Friday, May 29, 2020

15 Excel Like Features in Microsoft Project – Part 3




In the part – 2 of this series, we discussed five excel like features in MS Project. They are:
  • Selection of a set of cells in a mini-table area.
  • Selection till the end of a row or column from a particular cell.
  • Selection of an entire table.
  • Increasing the height of multiple rows or all rows
  • Auto-fitting the content.

In this part, we will check the next five excel like features. These features are quite handy and makes you more productive with MS Project software.

This series: Part - 1

This series: Part - 2


Feature – 11: Undo or Redo the actions

Brief: You want to undo (or redo) as many actions as you want.


How to do: You want to undo a number of actions. By default, you can undo up-to 20 levels, which can be configured from Global options.

This feature is actually applicable for all the applications of the MS Office. However, the settings in MS Project will be different. 

Example: The commands that you used are shown in the “Undo” and “Redo” buttons in the Quick Access Toolbar (QAT).


You can now select these actions (one or more) to undo. Same can be done for redo.


As shown above, I’ve selected to undo 6 actions, depicted as “Undo 6 Actions” above.

The number of “undo levels” can be set in the global options by going to Backstage view -- Options -- Advanced -- Undo levels:. This is shown below.



Feature – 12: Auto-wrapping of text

Brief: You want to auto-wrap the text to fit within the cell, instead of increasing the width of a column.

How to do: To auto-wrap a long text so that it can fit into the cell, you can use the “Wrap-text” command.

Example: In the below “Task Sheet” view, I’ve a long task name “Design and develop the backend module”. When I entered this task, as shown below, the entire task name is not visible. I don’t want to increase the width of the column; rather want to fit the task name within.


To auto-wrap the text, use the “Wrap text” command in “Format” tab of Task Sheet tools.


As shown, the command informs you to “wrap extra-long text into multiple lines so you can see all of it.” As click on this command, the text will be wrapped and will be shown as below.

This command is by default enabled.

You can also enable or disable it, by selecting a cell’s column and right clicking. This is shown below.


Feature – 13: Hiding multiple columns

Brief: You want to hide multiple columns in a table in one go.

How to do: To hide multiple columns in any table in any view, you need to select the columns, right click and select the “Hide Column” command.

Example: In the “Resource Sheet” view below, you want to hide three columns “Material Label”, “Initials”, and “Group”. First, select the three columns. You can select a contiguous set of columns or a discontiguous set of columns by pressing the “Control” key. This we have seen earlier in “Feature – 4” and Feature – 5”. This under Part - 1 of this series.


Next, right click within the selected area and select “Hide Column” command.


This will hide all the columns selected, i.e., “Material Label”, “Initials”, and “Group”.

Note: This feature is not applicable for rows in MS Project.


Feature – 14: Drag and Drop of column and/or rows. 

Brief: You want to rearrange the columns or rows by dragging and dropping.

How to do: To rearrange the columns and/or rows by dragging and dropping them in the table side of any view, you can select the column or rows and move them anywhere you need.

Example: In the “Task Sheet” view below, you want to move the “Total Slack” before the “Free Slack” column. The table used here is “Table: Schedule”.


You can select the “Total Slack” column and ensure the cursor becomes a “four-headed arrow”. Next, keep the mouse pressed and drag your cursor to the left. This will result in a shape shown below.


Next, drop the mouse press and you have “Total Slack” coming after “Free Slack” as shown below.


For rows, you can do the similar thing, which is shown below.


Note: In rows, you can select and drag multiple rows to your needed position in the sheet. For the column case, you drag and drop only one!

Feature – 15: Auto-filter option for the fields in MS Project

Brief: You want to enable auto-filter for the columns in a table in any view.

How to do: To apply auto filter in any table-sheet in any view, use the “Display AutoFilter” command from View tab -- Data group -- Display AutoFilter.

Example: You want to apply the auto filter to the columns in a table. To do so, you can use the “Display AutoFilter” shown below.


After you have applied the filter, the “inverted triangle” pointer will be enabled and displayed. This is shown below. This is next to the column name of every column.


If you have applied a filter, then the “funnel” icon will also be shown. For example, you can see the funnel icon below where I’ve applied the filter type “work” for the resources. This is next to the column name, where filter has been applied.



This series: Part - 1

This series: Part -2




Articles on MS Project:

Wednesday, May 27, 2020

15 Excel Like Features in Microsoft Project – Part 2




In the part – 1 of this series, we discussed five excel like features in MS Project. They are:
  • Auto-filling of items
  • Insertion of multiple rows 
  • Selection of Entire Row or Entire Column
  • Selection of a contiguous set of rows or a contiguous set of columns
  • Selection of a discontiguous set of rows or a discontiguous set of columns


In this part, we will see five more excel like features. I believe they are quite useful and helpful making you more proficient and productive with MS Project software.

This series: Part - 1

This series: Part - 3


Feature – 6: Selection of a set of cells in a mini-table area

Brief: You want to select a portion of table, i.e., mini-table, in any table of a view.

How to do: You can select a set of cells in this way. 
  • Select the cell, press the “Shift” key and select the other cell. 
  • All cells in that mini-table area will be selected.

Example: I’ve selected the cell of “Human Resource 2” as shown below.


Next, I want to select till the cell value of Material Resource 4’s “Std. Rate”. To do that:
  • First, hold the “Shift” key down.
  • Select the “Std. rate” cell of “Material Resource 4”. 
All the cells in the mini-table area will be selected, which is shown below.


In Excel, you have exactly similar features for selection of area within the sheet.

Feature – 7: Selection till the end of a row or column from a particular cell

Brief: You want to have selection till the end of a row or till end of a column from a particular cell in a table in any view of MS Project. 

How to do: First, note that it’s different from the “Feature – 3”, where I had mentioned the selection of the entire row (from the 1st cell in the left) or the entire column (from the header).

Here, the requirement is to select till the end a column or row from a particular cell. In order to do that:
  • Select the cell, press the “Control + Shift + Right Arrow” keys in the keyboard. It will select till the end of the row.
  • Select the cell, press the “Control + Shift + Bottom Arrow” keys in the keyboard. It will select till the end of the column.
Example: In the below “Gantt Chart” view, I’m currently in the Duration cells for A2 task.


Next, when I press “Control + Shift + Bottom Arrow” keys, it will select till the end of this column, i.e., the duration column. This is shown below.


You might be wondering, what's the use of that? Imagine having 100s of tasks and you want to format till the bottom from the current column's cell. How would you do it?

Similarly, when I press “Control + Shift + Right Arrow” keys, it will select till the end of this row, i.e., the row for Task ID – 3, which is shown below.


Feature – 8: Selection of an entire table

Brief: You want to select all the cells in a table in one go. This can happen in any view. 

How to do: The entire table of any MS Project view can be selected, by left clicking on the top left corner.

Example: In the below “Task Sheet” view, I want to select the entire table. To do so, put your cursor over the top-left corner.


In fact, in the above screen, it is informing you that “Right-click” to select and change tables”. So, let select that highlight area (in Green as shown above), and right click. You will have all the cells in the table selected.


As you can see, the entire table is selected. The default one is the “Entry” table and it has been selected. Now you can switch to another table from here as well. 

For example, I can switch to the “Schedule” table and it will come as shown below.



Feature – 9: Increasing the height of multiple rows or all rows!

Brief: You want to increase the height of multiple rows in a table.

How to do: You can increase the height of more than one row or all rows in any table of any view by selecting the rows and adjusting the heights.

Example: In the below “Gantt Chart” view, I want to select three rows and increase their heights, i.e., all rows will have similar height. In order to do that, select 3 rows and put the cursor over between two rows with the mouse cursor being a “two-headed vertical arrow”.


Next, drag any row to your desired height. All these selected rows will now be of similar heights. This is shown below.


To increase the heights of all the rows, select all the rows. (Tip: Use “Feature – 8” explained just before)

Next drag any one row to the desired height. All rows will be of similar heights, which is shown below.



Note: This feature is not applicable for columns in MS Project.

Feature – 10: Auto-fitting the content

Brief: You want to auto-fit the content within the width of a column or height of a row, so that the text in the cell is not wrapped. 

How to do: You can fit the content with the exact width of a column. You can do similar things for a row in any table.

Example: In the below “Task Sheet” view, I want to auto-fit the width of columns. First, I’ve increased the widths of two columns as shown below.


As you can see, the widths of columns “Task Name” and “Start” are increased in size. I want to make them fit into the exact size needed. For that: 
  • Put your mouse cursor at the end of the column name till it turns into a “two-headed horizontal arrow”. 
  • Then, do a double click.


This series: Part - 1

This series: Part - 3



Articles on MS Project:


Monday, May 25, 2020

15 Excel Like Features in Microsoft Project – Part 1


There are a number of features that MS Excel users can use in MS Project. If you have mastery over MS Excel features frequently used, you can use many such features in MS Project.


Important Caveat: At the outset, I want to make this distinction clear: MS Excel is NOT a replacement for MS Project. The MS Project software stands on its own as a project management scheduling, cost, and resource management tool. MS Excel software, on the other hand, is a data storage, validation, analysis, representation tool with a number of built-in formulas. This article only informs the excel like features in MS Project.

I’ve used MS Project 2019 while using the features. I believe almost all of these features will be available in the earlier editions of MS Project, i.e., MS Project 2016 and MS Project 2013. I am not sure of very old editions such as MS Project 2010 and MS Project 2007.

This series: Part - 2

This series: Part - 3

Feature – 1: Auto-filling of items

Brief: Instead of filling up repetitive task names or resource types etc., you want to auto-fill these items.

How to do: To have auto-fill, you have to simply select the item and as the arrow converts to a tiny black plus symbol without arrows, you can drag the items to fill.

Example: I’ve a task A1 and I need to have similar 5 tasks. Simply put your cursor over the right corner of task’s cell and ensure it converts to a small black plus symbol without arrows. Next, drag the arrow down.

After the arrow converts to a small “+” sign, select and drag it down.


After you have dragged down, you need to release the mouse pointer. 


Similarly, if you have similar durations, you can drag for the duration by putting the cursor over there. I selected the 1st cell for duration and did an autofill. It comes as shown below.



Feature – 2: Insertion of multiple rows (Not columns)

Brief: You want to add multiple rows in one go. 

How to do: Select the number of rows before which you want to do insertion. Next, right click and use command “Insert Resource” or “Insert Tasks”. Based on the number of rows selected, an equal number of rows of resources or tasks will be added. 

Example: In the below figure, I’ve a set of human, material and cost resources. Do note that I’ve used the autofill feature to populate the resource names as well as the resource types.


Let’s say, we now have three more human resources for the project – “Human Resource 6”, “Human Resource 7”, and “Human Resource 8”.  You can add it at the bottom of the sheet or you can add just below “Human Resource 5”. However, to add just below “Human Resource 5”, you need to insert multiple new rows. To do so, select “Material Resource 1” and the subsequent two rows – “Material Resource 2” and “Material Resource 3”. And then right click. 


Next, select “Insert Resource” command as shown above. As you have selected 3 rows of resources, 3 more rows will be inserted. Because you selected three rows and then used the “Insert Resource” command shown above, three new rows are added.


As shown above, you now have 3 more rows added. Next, you can enter the resource names. If you have selected four rows and then clicked insert resource command, then four rows will be added. Depending on your need, you can add as many new rows at any place in the sheet.

Note: This feature is not applicable for columns in MS Project.

Feature – 3: Selection of Entire Row or Entire Column

Brief: You want to select the entire row or entire column in a table of any view.

How to do: Like Excel, you can select an entire row or entire column with two keyboard shortcuts. 
  • “Shift + Spacebar” command – Selects the entire row.
  • “Control + Spacebar” command – Selects the entire column.
Example: Put your cursor anywhere in the view of the MS Project. Currently, my cursor is placed on task “A3”.

Next, press “Shift + Spacebar” to select the entire row of A3. This is shown below. The selected row has been highlighted.

On the other hand, if you, press “Control + Spacebar”, then the entire column of A3, which is shown below. The selected column containing A3 task has been highlighted.


Feature – 4: Selection of a contiguous set of rows or a contiguous set of columns

Brief: You want to select a contiguous set of rows (or columns) in a table in any view of MS Project.

How to do: Like Excel, you can select a contiguous set of rows or a contiguous set of columns with two keyboard commands.
  • Select a column + “Shift” key+ Select the other column – Selects a set of columns, from the first selected column.
  • Select a row + “Shift” key + Select the other row – Selects a set of rows, from the first selected row.
Example: Let’s say you have selected the “Task Mode” column in the below Gantt chart view. Next, you want to select all the columns upto “Finish”. For that, press the “Shift” key and simultaneously select the “Finish” column. With this, all the columns from “Task Mode” to “Finish” will be selected.

The below view shows that the “Task Mode” column is selected.

In the below view (after using the “Shift” key and simultaneously select the “Finish” column), you have all the columns selected.



Feature – 5: Selection of a discontiguous set of rows or a discontiguous set of columns

Brief: You want to select a discontiguous set of rows (or columns) in a table in any view of MS Project.

How to do: You can use two keyboard commands for this purpose. The commands are similar.
  • Select the row, hold down the “Control” key and select other rows: This selects a discontiguous set of rows.
  • Select the row, hold down the “Control” key and select other columns: This Selects a discontiguous set of rows columns.
Example: As shown below, I’ve selected a row, i.e., the first row, in "Resource Sheet" view.


Next, you need to hold down the “Control” key and select as many rows (discontiguous ones). All of them will be selected as shown below.




This series: Part - 2

This series: Part - 3



Articles on MS Project:

Saturday, May 16, 2020

Scenario Analysis for Management Practitioners with Microsoft Excel (2)




In the earlier part of this post, I had outlined how scenario analysis helps managers to present their cases for the project,under various situations or scenarios. 

During project execution, too, this analysis will be helpful to communicate with the stakeholders based on various situations or scenarios. 

In the earlier part, we saw how to create the scenarios with MS Excel with a quite powerful pack of "What-if analysis". In this part, we will know how to run the scenarios and check the summary of all scenarios in one sheet.

Run the Scenarios
Next, we will run the scenarios by using the “Show” command in the Scenario Manager screen. This command is highlighted in the previous image. 

Select the first scenario of “High Labor Costs” and click on the “Show” command. This, in turn, will update the values of “C5” and “C6” and also the values in “Total” columns. This is shown below and updated ones are highlighted in blue.



As shown above, the labor costs for Phase 1 and Phase 2 have gone up by $200,000 and the total cost for these phases also have gone up. 

Similarly, when you the second scenario of “Low Expenses”, it will come as shown below. 



As you can see, in this case the expenses and hence total costs for all the phases have gone down by $100,000. The updated cells are again highlighted in blue.

Reverting Back to the Original Scenario
Many times, it’s possible you would like to revert back to the original scenario. To do that simply click on the “Undo” command on the Quick Access Toolbar (QAT) of MS Excel tool.



Summary of Scenarios
Another powerful functionality given with MS Excel is the summary of the scenarios you have created. This way you can check for the impact of all the scenarios along with the original values.



The summary report can be generated by running the “Summary…” command in the Scenario Manager screen.  

The summary report informs on the current or original cases, the “High Labor Costs” scenario and its impact, the “Low Expenses” scenario and its impact. For example:
  • For Phase 1 and Phase 2, the labor costs were $500,000 and $515,000, respectively. If you apply the scenarios, it will become $700,000 and $715,000, respectively.
  • For Phase 1, Phase 2 and Phase 3, the expenses were $275,000, $200,000 and $290,000, respectively. If you apply the scenarios, it will become $175,000, $100,000 and $190,000, respectively.

Final Words
While working as a project manager, it’s likely that you will be asked to check upon various scenarios for your projects. In this case we saw the increase or decrease of cost related items for the project. This can also be for your project schedule, too. MS Excel provides this in-built tool which can help work with various scenarios. 

Scenario analysis is not only needed by project managers, it may also be required if you are working as a portfolio manager or program manager or finance manager. Also, as mentioned earlier, in operational work with inventory, utilities, site related costs, you can be asked to have various scenario analysis.

This series Part - 1: 
Scenario Analysis for Management Practitioners with Microsoft Excel (1)