Excel Tip: How to combine multiple sets of data for a pivot table - a practical solution

I have written many posts about the benefits of pivot tables and how to prepare data for use in them, but there is one scenario that I haven't covered which I come up against every now and again with clients. What if we have multiple sets of similar data that we wish to report across using a pivot table?

An example might be where we are comparing sales reports at a group level and are provided with a list of sales invoices from each of three departments or subsidiaries. To complicate matters further, the three departments use different invoicing systems and therefore the lists are in different formats.

Excel has a facility to consolidate ranges in a pivot table but this is for a very specific scenario and I, personally, have yet to find a practical use for this. It certainly does not help us here.

The good news though, is that I have developed a way of approaching this problem that works every time, and is pretty straight-forward.

To include them all in one pivot table, we will need them all in one list with one set of headings. And we want to do this in such a way that the work is already done for next month.

To start with we import, or paste, the three lists into their own individual tabs within our workbook.

We then set up a fourth tab and enter the column headings that we want to use for our consolidated table. These might be Date, Invoice Number, Salesperson, Customer, Amount. We also add an extra column for Department.

We want to allocate a section of the consolidated list to each of these departments.

Before we do that, we need to look at the typical size of each of these lists. Say that each is typically 2,000 rows - we might want to allocate 10,000 rows to each department to allow plenty of slack.

We now populate the first row of the consolidated sheet as follows.

In cell A2 (the first cell in the Date column), we enter a formula to pull the date from the first row of department 1. In cell B2 (the first cell in the Invoice Number column), we enter a formula to pull the Invoice Number from the first row of department 1, etc.

So, if department 1's data is held on a sheet called Department1 and the date is in column C and the Invoice Number is in column F, then on the consolidated sheet, cell A2 contains

=Department1!C2

and cell B2 contains

=Department1!F2

when this has been done for each of the columns we then enter the name of the department in the department column and copy the whole row down all the way to row 10,000.

Then in row 10,001 we do the same for the first row of department 2 (obviously referring to the correct columns for department 2 as these may be different. We then copy this down to row 20,000 and repeat again for department 3 on row 20,001.

We can then use these 30,000 rows as the source range the pivot table, allowing us to report across the departments, and by department thanks to the department column we added.

Next month we just have to import or paste the new data over the old data for each the three departments and the consolidated list will automatically update.

If you don't know how to create the pivot table itself, take a look at my earlier post, or you can purchase my Introduction to Pivot Tables video course here. If you're quick and do this by 31st January 2013, you can get it for half price.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".