Monthly Archives: February 2013

Excel – Data Consolidation

Let’s say you have asked people to fill out a simple time sheet. Everyone fills out their sheet and sends it back. You now have to total up all the hours that everyone worked for each day of the week or whatever time period you have asked them to complete.

ScreenHunter_53 Jan. 30 09.54

The problem you face is that not everyone works on every day of the week and they may have worked on different projects. So how to add everything up and allocate it to the correct day and project?

You have 2 possible strategies you could take;

  1. Consolidation
  2. Pivot tables

In this example, because the data is limited and simple I am going to go for DATA CONSOLIDATION.

If you have managed to bring all the worksheets into a single workbook then it’s worth naming each range as it makes referencing the data a bit simpler. However, it’s not a problem if you can’t as you can easily pick out ranges from separate worksheets and workbooks.

Click in a blank cell below or to the right of any existing tables or on a blank sheet. This is important otherwise you could end up overwriting a data table by accident when you create the consolidated table.

Go to the DATA tab and click on;

ScreenHunter_53 Jan. 30 10.23

This will open the CONSOLIDATE window;

Data Consolidation window

Data Consolidation window

At the top you have the option to choose from a list of functions. These are all the usual functions; SUM, AVERAGE, COUNT, MAX, MIN etc. Select the appropriate function for what you are trying to achieve. In this case I want to add up all the hours people have completed so I’d go for SUM.

Then you need to select all the tables that people have filled out for you. Make sure you include all row and column headings.

Click on the range selection button…..

ScreenHunter_54 Jan. 30 10.27

…and then select the ranges. After selecting a range of cells, click on ADD to add it to you references list. If you forget to do this (and it’s easily done….I’ve done it many times myself) your consolidation will not happen. So whenever you are consolidating repeat to yourself “Select…..add, select….add”.

Keep going until all the table ranges have been added.

ScreenHunter_54 Jan. 30 10.32

Now for the important bit. At the bottom of the CONSOLIDATION window there are two little tick boxes.

ScreenHunter_54 Jan. 30 10.44

You need to tick at least one of these so that the end result shows you some labels associated with the data you have just consolidated. In this example I want to see both the days and the project numbers against the number of total hours, so here I would tick both boxes. You may well find that in most cases you will do so this anyway.

Click on OK and there you have your consolidated data!

Consolidated data from my three tables

Consolidated data from my three tables

MS Project – Working with Multiple Projects

Working with one project can be difficult enough, but you may well find yourself managing or having to keep an eye on multiple projects which of course makes it several more times difficult to manage and control.

Unlike Primavera, which is set up so you can manage a wide portfolio of projects from the same screen, Microsoft is essentially a one project view (I can already hear gasps of shock from the more advanced users at that last comment) but MS Project does have the ability to display multiple projects in a single project view.

For this, I am just going to use 2 projects to keep it simple but this could just as easily apply to 10 projects or more. I am also not using SHARED RESOURCES to keep it simple, but you do have the option to use shared resources from a common pool to use across multiple projects.

ScreenHunter_105 Feb. 04 12.02

Rather than have to go into each project individually I want to see them both at the same time and on the same screen. For that have a blank project open and go to the PROJECT tab and click on SUBPROJECT. If you are using 2003 or 2007 go to the INSERT menu, and select PROJECT.

ScreenHunter_106 Feb. 04 13.27

Select all the projects you want to view together, using SHIFT + left click to select a whole group of projects or use CTRL + left click to select non-contiguous projects, then click on INSERT.

ScreenHunter_106 Feb. 04 15.14

Note the LINK TO PROJECT tick box. By leaving this option ticked, the imported projects remain linked to the original ‘stand-alone’ projects. This means that any changes you make in the new multi-project view will feed back to the original project so you don’t have to duplicate any work you do updating both the multi-project and the single projects. If you untick this option, then any changes you make to the multi-view project will not affect the original projects.

You should now see this:

ScreenHunter_106 Feb. 04 15.12

Notice that there is a PROJECT icon in the left hand margin to show that these are imported projects rather than project tasks.

ScreenHunter_106 Feb. 04 16.04

By clicking on the expansion button you can see the entire Gantt chart for each of your projects. If you are used to reviewing your project through the STATISTICS button on the PROJECT INFORMATION window, then you will encounter a slight problem. If you check your STATISITCS now you will get total duration, work, cost etc. for ALL the projects in the current view. In order to view the STATISITCS for each project individually, you have to treat each project as a TASK.

Click on one of the project names, go to the TASK tab, and click on INFORMATION in the PROPERTIES group. You will see the multi-tab task information window as though looking at a standard task, but you will now notice that there is a PROJECT INFORMATION button. Go to the ADVANCED tab and there you can see whether the project is linked, read only etc. and you can use this window to change the options whenever you want.

ScreenHunter_106 Feb. 05 09.38

To view the project STATISITICS of the selected project, click on PROJECT INFORMATION, then in the next window click on the STATISITCS button.

So now you can view and edit (if you want to) all the projects you are keeping an eye on in one simple view!

Note that if you do edit the consolidated multi-project view you will be prompted about saving changes to the original project.

ScreenHunter_106 Feb. 05 09.43

Decide for yourself what changes you want to save back to the original project and click on the appropriate button. If changes are made to the original stand-alone project then these changes come through automatically to the multi-project view.

Good luck managing your many projects!

Excel – Clearing Workbook & Worksheet Passwords