How to Hide and Unhide Columns and Rows

By Bob Umlas, Excel MVP

Here we’ll be exploring the various ways to hide and unhide rows and columns in Excel. The bulk of the discussion will address columns, but it applies to rows as well (unless otherwise noted).

How to Hide Columns

You can change a column width by dragging the column separator (the little line between columns) left or right:

hide-1

In the following figure, the column separator between D and E is clicked (notice the cursor shape), the mouse is held down, and dragged to the right:

hide-2

If you drag it to the left enough it will narrow or hide the column (when the width becomes 0 it’s hidden):

hide-3      hide-4

When you let go of the mouse there’s a double-line indicating that column D is missing (this shape varies with the version of Excel used):

hide-5

But when headings are not shown (from the View tab you can turn them off), there’s no indication a column is missing. Here, it looks like cell D3 is selected, but in fact it’s E3 because column D is hidden:

hide-6

Another way to hide the column is select any cell in the column, say D1, then on the Home tab, in the Cells group, there’s a Format dropdown which has access to the Column Width:

hide-7

When you issue the command, you get a dialog with the current width, like this:

hide-8

You can type in any number from 0 to 255, however when you type 0 the column is hidden.

You can also hide a column via Home/Format/Hide & Unhide/Hide Columns.

There’s also a shortcut to hide a column. Whichever column is selected will become hidden when you press ctrl/0 (a row will hide when you press ctrl/9).

All of the above applies to more than one column as well. If you select A1:E1 and use any of the methods, all 5 columns will hide.

How to Unhide Columns

Okay, how about unhide a cell? Again, there are several ways. It’s not as easy to select a hidden column, however! So you can type the cell address into the name box to get the cursor into the column. Or, you can press the F5 key (Go To) and type in the cell address. Here, D1 was entered into the name box:

hide-9

The cell D1 became selected. You can unhide this with the column width command we saw above, change the 0 which will be there to 8.43, or any number, etc. This is not literally an unhidden cell– it’s resetting the column width to a number. When you unhide, it will restore the width to what it was before it was hidden! You can unhide it by Home/Format/Hide & Unhide/Unhide Columns:

hide-10

Ctrl/shift/0 (ctrl/shift/9 to unhide rows) will do the job as well. You can also select the columns before and after the hidden column and unhide them (only the hidden one will be affected). Here, C thru E is selected:

hide-11

And finally, you can carefully position the cursor a little to the right of the D-column separator so it looks something like this:

hide-12

then drag to the right which will reveal column D!

Lastly, suppose you have text like this:

hide-13

And you want to widen column C so it all fits. There are a few ways to do this. You can drag the column separator between C and D to the right and visually adjust it. You can double-click the column separator which does a “best-fit” for the longest cell in that column. Or you can select a few cells (vertically or horizontally) or the entire column, and use Home/Format/AutoFit Column Width:

hide-14

Enjoy!

[trx_infobox style=”regular” closeable=”no” icon=”icon-desktop”]Learn more about IIL’s Microsoft Excel training >> [/trx_infobox]

Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.


Excel's JUSTIFY Command

Excel’s JUSTIFY Command

By Bob Umlas, Excel MVP

Have you heard of the Justify command? Let’s take a look at it here.

Suppose you have the data below:

justify-1

and you’d like to make it look like this:

justify-2

You may not know that Excel has this ability already built-in! It’s called the Justify command, found on the right side of the Home tab:

justify-3

What this feature does is reflow text. To use it, you have to make a specific selection first. In the above example, the selection had to be A1:C3 or longer (A1:C4, A1:C100, etc.):

justify-4

From this selection, issuing the Justify command would reflow the text.

You could have selected just columns A and B and reissued the same command for this result:

justify-5

(The resulting selection, A1:A4, happens after the command is completed. The entire columns A:B were selected prior to using Justify.

It aligns with the column widths. Notice the difference with different column widths here (column B is wider, column C is pretty narrow), where A:C was selected:

justify-6

OK, here’s another trick: if you select A1:D3 and issue the justify command, you get this:

justify-7

It reflows text from 3 lines into 2 lines! If you had selected A1:G3, you’d see this:

justify-8

Pretty cool.

Let’s look at a new example. Say you have the data in column A, below, and you want to make it look like cell C1:

justify-9

If you select A1:J7, for example (remember, there’s nothing yet in C1) and issue the command, you’d see this:

justify-10

So here’s a trick to overcome that. In cell C1 we’ll enter a formula to both change A1 to text and insert a comma. That formula is =A1&”, “, and we fill down to C7:

justify-11

We have the makings of a solution, but we don’t want that ending comma in cell C7, so we change that formula to =A7&”” which appends an empty string but it’s now text:

justify-12

We’re not yet ready to use the Justify command because, if you read the message above, it also can’t contain formulas! So we have to copy C1:C7, then paste only the values back into the same place. Ctrl/C can copy, and we can use the Paste Special command to paste just the values:

justify-13justify-14

 

 

 

 

 

 

 

 

 

So this:

justify-15

becomes this:

justify-16
And we’re complete!

[trx_infobox style=”regular” closeable=”no” icon=”icon-desktop”]Learn more about IIL’s Microsoft Excel training >> [/trx_infobox]

Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.

 

 

 


20 Custom Filters for Schedule Analysis [Microsoft Project]

20 Custom Filters for Schedule Analysis [Microsoft Project]

By Aaron J. Parham, PMP, CSM, MS Project Orange Belt 2013
Associate, Booz Allen Hamilton

Interested in submitting a guest blog? Here’s how.

Here is a list of 20 custom filters and criteria for creating them. You can use these filters to analyze schedules for completeness, find common scheduling errors, and create custom reports.

Sequencing Logic

1. Tasks without Predecessors

This checks for and displays any detailed tasks that do not have a predecessor(s).

Best Practice: Every task and milestone in a schedule (with exception to the first task and summary-level tasks) should be connected to at least one predecessor.

Tasks -Pred

2. Tasks without Successors

This searches for and displays any detailed tasks that do not have a successor(s).

Best Practice: Every task and milestone in a schedule (with exception to the last task and summary-level tasks) should be connected to at least one successor.

Tasks -Succ

3. Summary Tasks with Dependencies

This looks for and displays any summary-level tasks that have a predecessor and/or successor.

Best Practice: Predecessor(s) and successor(s) should be limited to detailed tasks and milestones only.

Summary +Depend.PNG

4. Out of Sequence Logic

This checks for and displays any detailed tasks that have a reported actual start that is before the baseline start. For this filter, I use a custom flag field with the following formula:

[trx_infobox style=”regular” closeable=”no” icon=”inherit”]IIf([Actual Start]<[Baseline Start],Yes,No)[/trx_infobox]

OOS Logic

5. Manually Scheduled Tasks

This inspects for any tasks that have a manually scheduled task mode.

Manual.PNG

Constraint Logic

6. Tasks with Hard Start Constraints

This seeks for and displays any tasks that have a SNET, SNLT, or MSO constraint.

Start Constraint

7. Tasks with Hard Finish Constraints

This looks for and displays any tasks that have a FNET, FNLT, or MFO constraint.

Finish Constraint.PNG

8. Tasks with Constraints other than ASAP

This checks for and displays any tasks that have a constraint type other than ASAP.

Constraint ASAP.PNG

Time Logic

9. Tasks with Lead/Lag Time

This finds and displays any detailed tasks that have lead or lag time applied to the duration.

Lead or Lag.PNG

10. Tasks with Undefined Duration

This will display any detailed tasks that have a duration of “1 day?”

Undefined Duration.PNG

11. Unassigned Task Calendar

This will show any detailed tasks that do not have a Task Calendar assigned.

No Calendar.PNG

12. Tasks Overlapping FY

This will find any detailed tasks that start or finish after the end of the fiscal year.

Overlapping FY.PNG

13. Critical Duration Tasks

This will display all the detailed tasks where the duration of the task is greater than XX% of the total duration of the project.

Best Practice: Any task(s) that comprises a significant percentage of the overall duration, as defined by the project, should be considered for further decomposition into smaller tasks.

Total Duration.PNG

Critical Path Logic

14. Single Point of Failure on Critical Path

This will illustrate all the detailed tasks on the critical path that have only one assigned resource.

Best Practice: 80/20 rule…If ~20% of the resources are responsible for ~80% of the critical tasks, it is beneficial to identify alternate resources and/or assign more resources.

SPOF on CP.PNG

15. Milestones on Critical Path

This seeks and displays any milestones that are on the critical path.

MS on CP.PNG

Resource Logic

16. Tasks without Resources

This hunts for and shows all the detailed tasks that have no assigned resource(s).

without Resources

17. Summary Tasks with Resources

This checks for and displays any summary-level tasks that have assigned resource(s).

Best Practice: Resource allocation should be limited to detailed tasks only.

Summary Resources

Baseline Logic

18. Tasks Not Baselined

This will display any detailed tasks that have not been baselined. For this filter, I use a custom flag field with the following formula:

[trx_infobox style=”regular” closeable=”no” icon=”inherit”]IIf([Baseline Start]<50000,Yes,No)[/trx_infobox]

Not Baselined.PNG

19. Missing Baseline Info

This will display any detailed tasks that are missing Baseline Start, Baseline Finish, Baseline Duration, Baseline Cost, or Baseline Work information.

Missing Baseline.PNG

Cost

20. Critical Cost Tasks

This will display any detailed tasks where the cost of the task is greater than XX% of the total cost of the project.

Critical Cost.PNG

Applying these filters to your schedules will help to ensure that the schedule is built adhering to best practices. If you are in a PMO or Project Audit role, these filters will be very useful to ensure all projects across your portfolio are scheduled correctly and consistently.

[trx_infobox style=”regular” closeable=”no” icon=”icon-desktop”]Learn more about IIL’s Microsoft Project training at www.iil.com.[/trx_infobox]

Aaron Parham is a Project Management Professional and management consultant for various Government clients, including the Army, Navy, and Marine Corps in Maryland and South Carolina. He has a degree in Business Management from McNeese State University in Lake Charles, Louisiana and served 4 years as a Communications Officer in the Marine Corps, deploying twice to Afghanistan in support of Operation Enduring Freedom.

Aaron earned his Project Management Professional (PMP) certification from the Project Management Institute (PMI), Certified Scrum Master (CSM) certification from Scrum Alliance, and Microsoft Project Orange Belt 2013 certification from the International Institute for Learning (IIL).


Demystifying Excel VLOOKUP

By Bob Umlas, Excel MVP

Using VLOOKUP in Excel seems to be a mystery to many. But you do it all the time in real life—you look up a word in a dictionary and scan a little to the right to read the definition. Well, it could be the same thing in Excel:

excel-vlookup-1

If you wanted to use Excel to look up a word in this dictionary, the syntax would look something like this:

excel-vlookup-2

The syntax is =VLOOKUP(what,where,returning which column,approximate match?)

“What” you’re looking up is cell G1, or Banana.

“Where” you’re looking it up is in the dictionary which is A1 thru B5 (A1:B5)

“Returning which column” is from the second column, or 2 (not “B”, because Excel’s syntax requires a number, not a letter).

“Approximate Match” is FALSE because we want an exact match.

Let’s say I looked up “Grape” in this dictionary:

excel-vlookup-3

The #N/A indicates it’s “Not Available”. Therefore “Grape” is not in this dictionary.

You may be wondering, “Why would anyone want an approximate match?” Well, suppose you’re looking up your salary in a tax table, and suppose you make $75,000. Here’s the fictitious table:

excel-vlookup-4

Your $75,000 isn’t in the table. It would be wrong here to get a #N/A error! You do owe taxes! You owe 12% of the first $60,000, and 12.75% of anything over that. So we’d use the TRUE instead of FALSE in the VLOOKUP formula (Since it’s assumed TRUE if omitted, we can just omit it):

excel-vlookup-5

The .12 is the 12% found next to the $60,000 item (cell B4) because we didn’t ask for an exact match. (When you do ask for the approximate match, the lookup column must be in sequence). Note that Excel does not also pick up the formatting, so the 12% is shown as .12. The value is the same, of course. Also note that the range we’re looking at is A2:C7 – we don’t include the headings in row 1.

In the following figure, we see that the 12% is for the first $60,000, so we need to “find” that $60,000:

excel-vlookup-6

Then these 2 values in column F need to be multiplied together to get the base tax amount:

excel-vlookup-7

The remaining part is to find the amount over the $60,000, then the tax on that overage. This is seen here:

excel-vlookup-8

This is a little trickier as we’re looking at a few calculations simultaneously. The difference is E1 minus F2; the $75,000 less the $60,000. This is places in parentheses so we can multiply that difference by the tax found in the 3rd column, the VLOOKUP(E1,A2:C7,3). This gives the result of $1912.50.

The total tax can now be found by adding the overage to the base:

excel-vlookup-9

For you adventurous folks, here’s the same answer all done in one cell:

excel-vlookup-10

A few final notes:

If the table started in column J, the column reference would still be 1, 2, or 3, not 10, 11, and 12, (the column number for J, K, and L) because the formula is looking for the relative column in the range. The above formula would be:

excel-vlookup-11

Learn more about IIL’s Microsoft Excel training >> 

Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.

 


Key Drivers to Scheduling Success with Microsoft® Project 2013

By Keith Wilson, B.Comm., PMP, MCT, MCTS
Project Server Black Belt
Senior Trainer and Consultant, IIL 

Project success means delivering your goods, service or result by meeting the expectations with respect to the project constraints (Scope, Quality, Resources, Time and Cost). A recent study of technology projects found that 94% of projects had to go through a restart, which impacts schedule and budget significantly.

This post provides valuable insight for managing the Constraints through a process that builds on Microsoft Project’s schedule calculation engine. To understand this, we have broken it down into the six key drivers that impact the calculation of time (start and finish dates) in Microsoft Project. Understanding Microsoft Project’s calculation tools will provide you with a framework to develop and maintain plans effectively, give you the necessary tools for troubleshooting, and allow you to manage the impact of change and eliminate the majority of problems you may encounter when optimizing your plan.  The six key scheduling drivers include:

  1. Project Start Date
  2. Calendars
  3. Task Constraints & Deadlines
  4. Task Durations
  5. Task Dependencies
  6. Resource Assignments & Task Types

1. Project Start Date (or Finish Date)

As the first key driver in an effective project planning process, the Project Start Date is used in Microsoft Project as the initial reference and starting point for all new tasks to be defined and entered into its schedule calculation engine. When you start a new project, the default is to apply the current system date as the project start date. However, it is important to define this date as accurately and realistically as possible since it will serve as the initial reference point for other calculations. This seemingly simple step can have a significant downstream impact as the interdependencies of related tasks and resources start to multiply. You can change the project start date at any time to model “what if” scenarios such as delaying or moving up the start of a project. In fact easy “what if” modeling is not only a critical step in setting up project plans, but it is also a very powerful tool that can be used to effectively recalculate your entire plan if a critical variable should change such as resource availability.

To define your project start date, follow these steps:

  1. On the Project tab click Project Information
  2. In the Start date box, type or select the project start date.

msp-1

2. Applying Calendarsmsp-2

Another key factor in the calculation time is based on creating and applying calendars. Calendars can be applied at the project, task or resource level. The project calendar can be selected and applied in the Project Information box. You can also use project calendars to simulate “what if” scenarios. For example, if you were on a tight schedule and you wanted your staff to work on Saturdays you could set up a new base calendar and apply it to the project to see the impact this may have on the timeline.  To create a new calendar, use the following approach:

  1. On the Project Tab select Change Working Time

 

3.  Task Constraints & Deadlines

One of the most common mistakes made in scheduling with Microsoft Project is to enter the dates directly into the start or finish date fields. This will actually set-up a schedule based on task constraints and seriously limit your ability to use Microsoft Project as a dynamic scheduling tool. Instead of constraints to drive your schedule, you should focus on setting up a network of tasks with dependencies driving when a task should start in conjunction with these other tools. However, task constraints can come in handy when there is a hard coded date such as a tradeshow, regulated deadlines, contract constraints, and so on.

Microsoft Project defaults to “As Soon As Possible” for new tasks. For the most part you should try and have the majority of your tasks stay “As Soon As Possible” with a few exceptions for fixed date tasks such as training, executive briefing, and site visits. This way when it comes time to make changes and do “what if” scenarios, you can let the tool recalculate each task start and finish dates automatically. If your tasks are all constrained, you will have to reenter the new dates manually for tasks that are impacted by any changes. Task constraints may also have a significant impact on your critical path calculations limiting your ability to manage the project life cycle. However, constraints are very useful tools but should be used wisely and with caution. The following table describes how each constraint behaves:

Constraint Description
As Late As Possible (ALAP) Schedules the task as late as it can occur in the schedule without delaying subsequent tasks. Do not enter a constraint date with this constraint.
As Soon As Possible (ASAP) Schedules the task to begin as early as it can occur. This is the default constraint for tasks. Do not enter a constraint date with this constraint.
Finish No Earlier Than (FNET) Schedules the task to finish on or after the constraint date. Use it to ensure that a task does not finish before a certain date.
Finish No Later Than (FNLT) Schedules the task to finish on or before the constraint date. Use it to ensure that a task does not finish after a certain date.
Must Finish On (MFO) Schedules the task to finish on the constraint date. Sets the early, scheduled, and late finish dates to the date you type, and anchors the task in the schedule.
Must Start On (MSO) Schedules the task to start on the constraint date. Sets the early, scheduled, and late start dates to the date you type, and anchors the task in the schedule.
Start No Earlier Than (SNET) Schedules the task to start on or after the constraint date. Use it to ensure that a task does not start before a specified date.
Start No Later Than (SNLT) Schedules the task to start on or before the constraint date. Use it to ensure that a task does not start after a specified date.

 

You can also apply task deadlines to help manage project or task finish dates. Select a deadline date in the Task information box on the advanced tab, and Microsoft Project will not constrain the task and anchor it in place but rather recalculate the task and compare it to the deadline. Depending on which constraint you select, it may override other calculation tools such as project calendars and task dependencies. The “Must Start On” and “Must Finish On” constraints are the two strongest and will anchor the task in place, even on a non-working day.  The figure below illustrates the use of a deadline represented by the green arrow which allows the milestone to continue to move dynamically yet calculate the positive or negative slack to help determine how far off schedule you may be.

msp-3

4. Duration

Estimating task durations will obviously have an impact on the calculation of start and finish dates. It is important to remember that task durations can be entered directly into the duration field on any table or dialogue box that has the field displayed. Keep in mind that duration is based on working days as defined in the project calendar. For example, 1 day = 8 hours, 5 days = 1 week, 20 days = 1 month. If you wanted to enter 1 month in the duration field, and you type in 30 days, it would actually be calculated as 6 weeks.

Since this post is concerned with the key calculation drivers of schedules in Microsoft Project, it assumes that the work breakdown structure (WBS) is already established as this will in turn drive the creation of schedules. Therefore, prior to working on creating a schedule in Project you must have already entered the phases, tasks, and milestones into the task name column.

Use the new multi-level undo  msp-4 feature in Microsoft Project to give you peace of mind as you make changes to your schedule. You can always undo and redo what you have entered. This allows for more efficient planning while conducting “what-if” scenarios in a more meaningful way without having to save back-ups or multiple versions of your plan.

5. Dependencies

One of the most critical steps in scheduling is to establish a network for your tasks by setting task dependencies. This step in scheduling will make the difference between a plan that can be used as a dynamic scheduling tool and a plan that can be used as a presentation tool upfront and then put aside during implementation. Once the dependencies are set, and the network is established you can easily identify the critical path and understand the driving factors to the project end date. You can also make changes to one task and immediately see the impact it will have on the rest of the plan like a ripple effect. Taking advantage of key features in Microsoft Project such as the multi-level undo, show change highlighting, and task inspector makes setting up your network more efficient while helping to ensure the accuracy of your schedule.

The challenge project managers have is to ensure that all tasks are in the network. A best practice to follow is that every task should have a predecessor unless it is driven by the start date of the project and every task should have a successor unless it is the last task or milestone in the project. Consider including a project complete milestone in your plan to link other tasks or milestones.

msp-5

Select a predecessor task from the drop down list. The default type is Finish to Start (FS). Enter lag (in days or %) to create overlapping tasks or gaps between tasks.

You can also type the dependency directly into the predecessor column, making note that this column is driven by the ID number located in the far left side of the table. A quick and easy way to set dependencies is to use the link tool by highlighting a series of tasks and the network will be set-up automatically in a sequential (finish to start) manner. You can even multi-select tasks not in sequence by holding down the control key and subsequently clicking on the link tool from the toolbar.

6. Resource Assignments

One major contributor to the calculation of start and finish dates is the manner in which you assign resources to tasks and what corresponding task type settings have been set. The first thing to understand is that there are three variables being calculated each time a resource is assigned to a task. These variables are tied together in a formula based on Work = Duration * Units. If you change one variable, then one of the other two variables will be recalculated depending on what task type has been applied to that task.

Selecting the task type will be driven based on the scheduling technique you want applied to that task or project. There are two main approaches to scheduling: A duration based approach and an effort based approach. Duration based scheduling is a method generally applied to tasks that are driven by dates and deadlines which will not be impacted by the amount of resources assigned. A good example of this would be a tradeshow.  No matter how many resources you assign to the tradeshow, the duration will always be the same.

Duration based scheduling is equivalent to setting the task type to Fixed Duration either as the default and/or for each task. To set a Fixed Duration task as the default follow these steps:

  1. On the Tools menu, click Options.
  2. On the Schedule tab select the Default task type drop down list, click Fixed Duration.

For duration driven tasks, you may want to consider turning the effort driven option off (not checked). If you leave it checked, work will remain constant within the fixed duration time period (as per the duration estimate) and be redistributed each time there is a resource change based on the original work calculation.  It is better to set the default task type before adding tasks, otherwise you will have to rework the plan and change each task type setting after they have been entered. Changing the default task type after will only affect new tasks, not existing ones. You can also set the task type on a task by task basis either in the split screen view or in the Task Information box under the advanced tab.

Effort based (driven by work) tasks are those in which the duration will be recalculated based on the amount of resources assigned. The more resources assigned to a task, the less time will be calculated in the duration field. The opposite is also true. As you assign fewer resources, duration will increase. The so-called “stuffing envelopes” approach can apply to a variety of tasks. To set the task default to recalculate duration based on resource assignment, follow the same approach for Fixed Duration as previously explained but instead, select Fixed Work (by default the Effort driven will be checked for each task) or select Fixed Units with Effort driven checked.

Assigning resources in Microsoft Project has never been easier. First you can build your resource pool in the resource sheet and subsequently assign them to tasks in the Gantt chart. To build your resource pool, click on the Resource Sheet View and enter the different types of resources whether they are named, generic, equipment, material or, new to Microsoft Project, cost resources.

msp-6

Once the resources have been created in the resource sheet, you can easily assign them to tasks using the Assign Resources tool assign-resources-tool from the toolbar or the split screen view or the drop down list from the Resource Name column.

msp-7

Split Screen View: Pay attention to the units and work details. Depending on task type and number of resources assigned your schedule will be recalculated automatically.

If you use the drop down list or the Assign Resource box your assignment defaults to 100% or 1 unit.

If you do use the Assign Resource dialog box, you can multi select tasks with the control key and assign multiple resources to multiple tasks at one time. This makes it very efficient to make resource assignments.

Conclusion

Using the right tool for the job will increase your ability to achieve success on projects and be a more effective Project Manager. Understanding the key drivers to scheduling in Microsoft Project will go a long way to ensuring your success when not only setting up your schedule but also managing the impact of change.  Keeping in mind there will always be competing trade-offs between scope, time and cost (resources) on any project, the more you learn about managing these factors the more productive and success you will achieve.

[trx_infobox style=”regular” closeable=”no” icon=”icon-desktop”]Learn more about IIL’s Microsoft Project and Project Server training at www.iil.com.[/trx_infobox]

Keith-1Keith Wilson is a Microsoft Project and Project Management Senior Consultant/Trainer for International Institute for Learning, Inc. His background includes over 25 years of successful management and consulting experience, with a focus that includes project management, training, and business planning. Well known for his public speaking skills and enthusiasm, he has been a welcomed facilitator at numerous Fortune 500 corporations, Universities and Associations worldwide.


Excel Basics: Separating First and Last Names in One Cell

By Bob Umlas, Excel MVP

Here in my first blog post, I’m going to show you how to separate first and last names in one cell using Excel. Imagine you have this worksheet, where the name is in column A:

excel-1

 

 

 

 

 

 

 

 

 

 

 

 

Your job is to separate the first and last names so you wind up with this:

excel-2

 

 

 

 

 

 

 

 

 

 

 

 

Then you can delete column A.

The 3 ways I’m going to explain here are:

  1. Formulas
  2. Text-to-Columns
  3. Flash Fill (For Excel 2013 or later)

The formula way is the most difficult, but also perhaps most instructive. To do this, I’ll describe the use of some built-in functions, namely FIND, LEFT, and MID.

Let’s also assume that we know the format is always last name, comma, and first name. Let’s look at cell A2 in detail. If we know the position of the comma, we can determine the location of the first name (the last name always begins the cell).

The FIND function does this job. The syntax (rule) is =FIND(what you’re looking for, in what string): If we put this formula in cell B2: =FIND(“,”,A2), we’ll see 6:

excel-3

 

 

 

This is because the comma is in the 6th position of “Jones, Bob”.

Since the comma is always followed by a space, we then know that the first name is 2 characters after the comma. The MID function takes characters from the “middle” of a string of characters, and the syntax is =MID(cell,start,length). So if in cell C2 we put =MID(A2,B2+2,255), we’ll see this:

excel-4

 

 

 

Notice that we added 2 to the value in B2. The 255 is arbitrary, but certainly large enough for the largest first name, and also the maximum number allowed in the MID function!

We can combine these last 2 cells’ solution into 1. Instead of using B2, we can put what’s in B2 (the FIND function) and substitute it:

excel-5

 

 

 

So we are no longer using cell B2! We can delete column B:

excel-6

 

 

 

OK, now we need the last name, and put it in cell C2. We know that we want all the characters to the left of the comma. That’s =FIND(“,”,A2)-1. Note the “-1”. We don’t want to include the comma. This is where we need to use the LEFT function. It takes this syntax: =LEFT(cell,length). Well, we know the length is that FIND function with the -1:

excel-7

 

 

 

Practically done! Select cells B2:C2, and double-click the fill handle (that’s the cursor when it sits on top of the little square at the bottom right of the selection):

excel-8

 

 

It will copy that formula down to the bottom:

excel-9

 

 

 

 

 

 

 

 

 

 

 

 

 

 

OK, we’re ready for the 2nd technique, Text-to-Columns.

First you select the names in A (from A2:A24), then invoke the Text-to-columns command found on the Data tab:

excel-10

 

 

That will bring up a dialog box from which you can choose delimited or fixed width:

excel-11

 

 

 

 

 

 

 

 

 

 

 

In this case, it’s delimited (by a comma). So click the Next button to see step 2 of 3:

excel-12

 

 

 

 

 

 

 

 

 

 

You have to click the Comma checkbox, as it isn’t checked initially. Now click Finish. (You could click Next, but there’s nothing on step 3 that applies to this situation. Text-to-columns can be used in many other situations as well.

When you click Finish, you’ll see this:

excel-13

 

 

 

 

 

 

 

 

 

 

 

 

You have the names split, but you have last then first, not first then last. You can select B2:B24, click Cut (or Ctrl/x), then click on A2 (which contains “Jones”), then use Right-mouse click and select Insert Cut Cells:

excel-14

 

 

 

 

 

 

and then you’re done.

The third technique is only if you have Excel 2013 or later. It’s called Flash Fill. You start by typing the first result (Bob) in cell B2, and continue typing the 2nd result in C2. As you do, you’ll see Excel suggest the rest in a light gray color, because it recognizes the pattern:

excel-15

 

 

 

 

 

 

 

 

 

All you need do is press enter, and you’re done.

Then go to cell C2 and type the first 2 last names in C2 and D2:

excel-16

 

 

 

 

 

All there is to do is delete column A!

The only disadvantage to the latter 2 techniques is that they’re not dynamic. That is, if you change A2 to Williams, Jared, the data in cells B2 and C2 won’t change. But the formula-based version will change.

Learn more about IIL’s Microsoft Excel training >> 

Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.


Can Microsoft® Project Be Used for Agile Projects?

By Keith Wilson, B.Comm., PMP, MCT, MCTS  |  Senior Trainer and Consultant, IIL 

Can Microsoft Project be used for Agile projects? Yes! Customizable fields and custom groups can be used effectively to create an Agile template. Custom fields allow you to add project-specific information, which can be used to filter or group tasks to provide different views into the Agile schedule. It is possible to create a schedule for planning, communicating, and tracking Agile projects, or with an Agile or a hybrid method.

Key characteristics of the Agile development method are Iterative, Incremental, Embraces change and Delivers a deployable product early for a quick ROI.

img1

Agile teams deliver complete and functioning code within short iterations. During the iterations, all of the necessary work to take features from an idea to a working product is completed without artificial dependencies that prevent work from being done in parallel. The end result is that portions of the product are delivered on a regular and frequent basis. This gives stakeholders a much better idea of the state of the project, because they can see and use the end result as it becomes available.

The strengths of Agile development:

  • Delivers minimum usable subset
  • Delivers on time and to cost
  • Iterative development for evolving solution
  • Flexible processes
  • Collaboration of whole team

So what project-specific data must be added to create an Agile schedule?

An Agile project starts with a backlog, which is a list of features to be implemented, and a number of iterations or sprints to implement those features, which are represented as tasks in the schedule.

In addition, each feature will have a priority and a size estimate represented in story points and will be mapped to a sprint. Story points (which can be numbers – e.g., 1, 2, 5, 8) are relative values based on the size or difficulty of a user story relative to other stories. When you estimate using story points, you estimate the “bigness” of a user story compared to other stories. Each feature and sprint will also require a status (e.g., not started, in progress, or done).

To create an Agile project schedule we need to know:

The duration of each sprint, the release date if there is a planned release including several sprints, the priority of each feature in the backlog, the feature complexity or story point value, user need or priority and state. In addition we will need to know the resources and the percentage of their time available per sprint and each resource loaded labor rate.

We will need to create custom fields for Agile-specific information including:

User Need                   Text Field

Lookup Table: Low, Medium, or High

State                             Text Field

Lookup Table: Not Started, In Progress, or Done

Story Points               Number Field (Estimate of Story Points)

Set Calculation for task and group summary rows to Rollup: Sum

Sprint                            Text Field:

Lookup Table: Backlog and Sprint 1 through Sprint n

It will also be necessary to create two Groups:

Sprint                  Group by Sprint.

The Sprint group will list all features in each Sprint; story point totals roll up for each sprint. Features not assigned to a sprint will be grouped under Backlog.
Burndown         Group by State, then by Sprint.

The Burndown group will provide story point summary by categories: Done, In Progress, and Not Started.
Story point totals roll up.

The Agile schedule will also require two summary tasks: Sprints and Features. Under Sprints, enter the expected number of sprints, including the name and duration of the sprint, and link them in sequential or finish to start order. Then list the features under Features summary task. Make each of the features a milestone, by indicating a zero duration. By default the features will all be in Backlog. To assign a feature to a specific sprint, select the sprint number in the Sprint dropdown. Also, when you assign a feature to a sprint, set a finish-to- start predecessor to that sprint so the expected finish date of each feature will be known. When tracking the project, if one or more features is not completed in a specific sprint, it is easy to just update the sprint number and the predecessor to move those features to another sprint.

Build a resource pool with real or generic resources. Be sure to include a rate per resource and other know attributes such as Calendar, Max Units, Group, and Rate. Once you have the resource sheet complete, assign the resources to each sprint and indicate the percentage of time that they will be working on the Sprint. Since there is a rate per resource, Microsoft Project will multiply the duration by the percentage allocation time for each resource and then by the resource rate. It will then summarize each resource cost per sprint to provide a total cost per sprint, which in turn can be rolled up to show the total cost of the project.

The following is an example of a schedule with sprints that have a duration of 15 and a dozen features and is using the custom Burndown group, grouped by state and sprint. It also displays the total number of story points for each of these states:

img2

Custom fields are a powerful feature of Microsoft Project, and when used with custom groups, we can actually use Microsoft Project for an Agile project without knowing which features each sprint will be addressing. Given the duration of each sprint, the resources, resource rate, and percentage assigned it will be possible to calculate the cost per sprint; and if the sprints are rolled into one release, the total cost and duration for that release or project.

Leave a comment below if you’re interested in receiving a downloadable template.

[trx_infobox style=”regular” closeable=”no” icon=”inherit”]Keith-1

About the Author

Keith Wilson is a Microsoft Project and Project Management Senior Consultant/Trainer for International Institute for Learning, Inc. His background includes over 25 years of successful management and consulting experience, with a focus that includes project management, training, and business planning. Well known for his public speaking skills and enthusiasm, he has been a welcomed facilitator at numerous Fortune 500 corporations, Universities and Associations worldwide.[/trx_infobox]