Getting More From Your Pivot Tables

In A Few More PivotTable Improvements in Excel 2010, Diego Oppenheimer, Program Manager on the Excel team at Microsoft, discusses new pivot table features being implemented for the next release of Excel.

One of the improvement mentioned is the ability to repeat down labels facilitating the use of formulas with the pivot table as a source. If you don’t want to wait for the next Excel version, here’s a trick to get to the same result.

Before getting to the repeat label part, we need to make sure the pivot table is set properly, more precisely the row fields. We’ll have to adjust the field settings for each row field except for the last one on the right. In the pictured example we have three row fields (Cie, Category and Product) therefore the following steps will be done twice in this case.

To adjust the settings of a field, you simply need to select one of its elements, right-click and then select “Field Settings”.

• In the “Subtotals & Filters” tab, remove the sub-totals
• In the “Layout & Print” tab. You settings should as in the next picture

Note: In Excel 97-2003, you remove the subtotals in the field settings dialog and then click on the “Layout…” button from there.

Finally, simply add some helper columns next to your pivot table. Take a look at the following picture for the setup.

Helper column with repeated labels

Other thoughts

Beware: Your pivot table might stretch up or down so you want to make sure that you have enough formulas.

If you don’t like the look of your pivot table, duplicate it and hide the less esthetic section or even the whole sheet.

You can download a file with the pivot table before and after here: Getting More From Your Pivot Tables