Related Entries

Quick Ref: SVN command line
Git with Dropbox
Cisco's new urbanism
Portable development links
Wish I had this 10 years ago

« Can you spot the difference?
» ADD and antipatterns

Lean Pivot Tables

Reduce the size of your Excel files that have pivot tables using dynamic ranges

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.

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