Blog Archives

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