A Better Microsoft Project: Background Color in D * U = W fields to Communicate Which Fields are Protected and Editable

This article is the third in this series, A Better Microsoft Project. You can find the first two articles here and here.

I believe the fields involved in assigning resources in MS Project have created the most problems and biggest challenges for users in previous decades. Regardless of whether my thousands of students had used MS Project just one month, a year, or even a decade, I could safely assume that simplifying the process of assigning resources was of interest to them! The issue with Microsoft Project is that there are too many fields that make Modeling Workloads too cumbersome:

  • Field Type with 3 values: Fixed Duration, Fixed Units, Fixed Work
  • Field Effort driven with 2 values: Yes, No
  • Field Duration in business days
  • Field Units
  • Field Work or effort in person days

Recommendation #3A: Rename the field Work in Project for Desktop to Effort like has been done in Project for the web.

These five fields create a grant total of 3*2*3=18 possible permutations of values, which creates too much complexity for the user.

How do you deal with an equation that has three variables: D * U = W? First you protect (or fix) one, then you change the second, and, finally, the third one will always be re-calculated by MS Project! You can protect a value by setting the task field Type to Fixed Duration, Fixed Units, or Fixed Work. This is what I’ve taught students in every one of my classes! People could remember this and it stopped them from fighting with Microsoft Project. An often-heard criticism was, “Microsoft Project is changing the values I just entered!”

Let’s simplify by taking an option away that does not make sense. Recommendation #3B is disallow changing the value that is fixed. Project for Desktop allows you to change the Duration on a Fixed Duration task, but let’s think about that. Why would you change a value after you tell the scheduling engine to keep it fixed (constant)? You could also change the Units on a Fixed Units task, and change the Work on a Fixed Work task. This caused many users to get confused and provided me with a lot of training work.

Let’s simplify it further and take an option away that adds little value. Recommendation #3C is to remove the field EffortDriven from Project for Desktop. I suggest this because this field increases the number of possibilities by a factor of two (from 9 to 18) adding much complexity with little, extra usability. The Type of task Fixed Work accomplishes most of what the field EffortDriven = Yes does. You can see this in the interface: Once Fixed Work is selected, Effort-Driven is always Yes. Good riddance of redundancy that makes the interface unnecessarily complex!

How many options are left? Removing the EffortDriven field removes 9 of the 18 options. Disallowing changing the fixed value removes another three options (from 9 down to 6 permutations). When you fix (protect) one value (one of three), you can only change one of the other two values and get 3*2=6 options for the user: There are still six options to learn, so the user interface needs to guide users well in this, but I believe it would be a big improvement.

I also recommend guiding the user by making these additional improvements:

  • Recommendation #3D: Create a new task-related field called Units. The Units field aggregates the units of all resources assigned to the activity. In the rare case that a Work Contour is applied in Project for Desktop to the assignment, the average can be calculated to add to the Units value. This new field, Units, should become editable, and, when clicked, display the same list that is displayed currently in the Project for the Desktop field, Resource Names, as can be seen in the following screenshot:

This list allows you to pick the (multiple) resource(s) to assign to the task. Perhaps a new assignment-related Units field can be added to this list, too.

  • Recommendation #3E: Display all three fields: Duration, Units, and Effort (Work) side‑by‑side by default in the Gantt Chart table (task-level).
  • Recommendation #3F: Guide the user with background colors to show what value is protected and which other two values they can change:

    -Use the color red to indicate which field is protected from recalculation (in traffic lights, red means stop, or you cannot go); in Project, it would mean: this value is protected, you cannot change this, and that a red field should not be editable.
    -Use the green background color to indicate which two fields can be edited. This would look like as in the following screenshot:

In the previous screenshot for task 2 Water system, the Duration cell is red and cannot be changed (Fixed Duration), the Units and Effort (Work) cells are green, and one of those can be changed. Let’s say, the user changes the Units value by adding another resource, the Effort (Work) field will be recalculated. Note that:

  • All cells are red for the project summary tasks; none of these should be changed manually since all are calculated cells. Calculated from their detail tasks indented below them.
  • The Duration and Effort cells for the milestones 1 Start and 4 Water & Septic Completed are red and protected; these are always zero for milestones regardless if you assign resources to milestones as assignments of responsibilities.
  • Recommendation #3G: Allow the user to right-click on any of the green cells to make them protected (red cells). Add an item to the right-click, pop-up menu called Protect the value in this cell, which, when clicked, moves the red background color to that cell and makes the other two cells green (editable).
  • Recommendation #3H: Disallow the user to change a protected cell and communicate this to the user with red background color in the cell, which will dis-allow users, for example, to change the Duration on a Fixed Duration task in Project. (See task 3 Water System in the previous screenshot) or the Work on a Fixed Work task.
  • Recommendation #3I: The permanent re-coloring of these fields might be too extreme a recommendation for Microsoft, so, as a compromise, I propose that the coloring only kicks in when the user clicks on one of the cells in the fields: Duration, Units, or Effort (Work). After all, when the user clicks on one of these cells, the intention is clear that the user wants to change the value in that field. At that exact point, the interface could guide the user in a visible way with a message in a screentip: Don’t change the value in this red cell; change one of the other two values (green).
  • Recommendation #3J: Add both assignment-level fields, Units and Effort (Work), in the Assign Resources dialog in Project for Desktop. Only the Units field is currently shown, but too few people realized that you could also enter an effort value (like ‘2d’) in this Units field (try this out if you have never done this!). This could be made explicit by adding both fields. Again, one of the two needs to be protected (red background color) and the other one editable (green background color), and the user should be able to switch the protection from one to the other (which the task‑level will inherit as well).
  • Recommendation #3K: Create a similar Assign Resources dialog in Project for the web.
  • Recommendation #3L: Make the cells for Duration, Units, and Effort for any summary task un-editable and communicate this to the user with a red background color. It is not recommended to assign resources to summary tasks (because this creates over-allocations too easily).
  • Recommendation #3M: Make the cells for Duration and Effort for any milestone un-editable and communicate this to the user with a red background color. Milestones typically have a zero duration, and as a result the Effort (Work) will always be zero on milestones as well even if the user assigns resources to milestones, after all: zero * number = zero.
  • Recommendation #3N: While creating new tasks, and the user enters a value in one of the columns, Duration, Units, or Effort, Project should protect this value automatically by setting the task Type accordingly and communicating this to the user with the red background color.

How would all these recommendations work together? Let’s look at some examples. The following project schedule is a Time Model with the Durations entered currently on all tasks and their task Type set automatically to Fixed Duration (all Duration fields have red background color):

We will change this Time Model into a Workload Model by loading resources and assigning them to all activities. The result is as follows:

Now we want to make a change to this Workload Model. Perhaps we are not happy with the fact that task 3 Septic System takes twice as long as task 2 and we ask the septic engineers to double their human resources working on the septic system while keeping the Effort the same. We expect that Microsoft Project will recalculate and half the Duration and make it as long as its concurrent task 2 Water System: We need to protect the Effort first. With my preceding recommendations in place, we’d right-click on the cell Effort for task 3 and select item Protect the value in this cell from the pop-up menu.

As a result, the background color for task 3 Septic System switched. Duration is now green (editable), and Effort of 960h is red and protected. Additionally, the field Type is automatically switched to Fixed Work for task 3 Septic System:

The Effort is now protected, meaning the work is not going away. It is just going to be spread over more resources. Now, we can add two more septic engineers on task 3 and the result is that the Effort of 960h is still the same, but the Duration has gone from 60d to 30d, as shown:

As you can see, making changes to assignments and producing accurate forecasts of workloads is much easier with this way of working: Since Microsoft Project has a formula with three variables, you should always protect one variable before changing a second variable and then have Microsoft Project automatically recalculate the third variable. Simple and reliable.

Do you agree that my recommended changes to the Project interface would make working with Project a lot easier than it is now and that it would lure more users to start modeling workloads in their project? Let me know in the comments below if you like this re-designed interface for assigning resources and changing assignments. I look forward to your feedback.

Written by Eric Uyttewaal
Eric is a thought leader on project, program, and portfolio management. He spends most of his time using software from Microsoft. He has authored seven well-known textbooks including ‘Forecasting Programs,’ 'Forecast Scheduling with Microsoft Project 2010/2013/Online,’ and ‘Dynamic Scheduling with Microsoft Project 2000/2002/2003.’ He founded ProjectPro, which specializes in Microsoft Project, Project Server and Project Online. Eric developed several Add-ins with his team that enhance the capabilities of Microsoft Project in creating better schedules (Forecast Scheduling App), managing cross-project dependencies (CrossLinksPro), identifying and documenting the Critical Path (PathsPro) and creating S-curve reports (CurvesPro). He was president of PMI-Ottawa in 1997. Eric has received awards from PMI in 2009, from MPUG in 2012, and from Microsoft from 2010 until 2017 (MVP).
Share This Post
Have your say!
  1. Wondering if this could be implemented in Project Server with Highlight filters…

    • @Mark, thank you for your support. Highlight filters would be a temporary workaround at most in my mind: The user would have to be aware of those highlight filters and apply them. What I intend with this article is to say that the way Microsoft Project interfaces with the users is changed in a dramatic way to better support assigning resources and building Workload Models.

Leave a Reply