vsbabu.org

Lean Pivot Tables

Pivot tables in Microsoft Excel are really great in summarizing data. These are usually used for charting also. Most of us just select the columns we need to pivot on while building a pivot table. This can cause an abnormally large file size, unnecessarily using up memory and painfully slow to transmit to others.

One easy solution is to just select only the range that has the data, as opposed to full columns, while choosing the input to pivot. What it means is that just select the rows and columns in the grid, but not using the header bar. One drawback with this approach is that when the data changes or more rows are added to it, you will need to redefine the input to the pivot table. Read on to see how to get around this problem using dynamic ranges.

You define a named range that counts the non-blank columns and non-blank rows. As and when more data is added to it, the range automatically resizes itself. We can do this using OFFSET and COUNTA functions.

The screenshot here illustrates a simple pivot table using the first four columns. The data we really need to use today is in range $A$1:$D$7. If you make the pivot table using the range $A:$D, the file size (Excel 2003) is an astonishing 529 KB! Use dynamic ranges as explained below and the size drops to 13 KB.

• Navigate to the menu : Insert - Name - Define
• In the Names in workbook: field, enter the name RawData
• In the Refers to:
field, enter the formula : =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
• When you create the pivot table wizard, give =RawData as the input
• Build your pivot table

I am not going to explain these functions here - the links above do a good job of that.