Consolidation within pivot table

In the previous texts, you could learn about several ways to consolidate data, and here we will deal with consolidation using a pivot table. To be able to achieve this we will use the Pivot Table and Chart Wizard. This is a hidden option that you can’t find in any of the ribbons (you can add it yourself, if you wish, by adjusting the layout of the ribbon), and it makes it easier to create pivot tables and charts …

Let’s say we have four worksheets (Q1, Q2, Q3, Q4) within which there are tables with sales data by cities. How to consolidate this data using a pivot table?

The shortcut to launch the Pivot Table and Chart Wizard is a combination of the ALT + D + P keys. Once you run this wizard, a dialog box will open that guides you, in a several steps, through the process of creating a pivot table or chart. The wizard will first ask you: based on which source do you make the pivot table? This can be a range or a data table, an external source, multiple consolidation ranges, or an existing pivot table. Here we need to choose the third option. Then you choose whether to make a pivot table or a chart. The pivot chart is based on data from the pivot table so when you make a chart, you must also have an associated pivot table. This is something you probably know but I think it’s not bad idea to repeat it once more.

Clicking Next moves to the next screen. Here you have to choose between one of the two options: whether you want Excel to create one reporting page or you will create the pages yourself. Choose second option and continue…

Then a window will appear that allows you, one by one, to select the ranges of data which you want to consolidate. After, for four quarters, we enter four data ranges in the selection How many page fields do you want one field should be selected, and then in the Field One box, for each of the ranges, assign it a name (Q1, Q2, Q3, Q4).

In the last wizard window, you choose whether you want to create a pivot table in a new or existing worksheet. After this pivot table report is created, we can select the Outline Form, as well as cancel the totals for the columns. Now, we can filter the pivot table by choosing one, more or all quarters.