I often work with very large spreadsheet, often more than 30 MB in size. I am an Office user, not an Office expert. A few months ago one of my teammates taught me how to use pivot tables to summarize and present the large data set in a way better than just using filters. The standard way of creating a pivot table is quite easy, but I was surprised to find that it nearly doubled the size of my files — when it worked Often I got the error, “Excel cannot complete this task with available resources”. I found a lot of not very helpful information, until I saw a post from someone saying he put the pivot table in a second spreadsheet, and used Microsoft Query to connect them. This got me to thinking — why not try it within the spreadsheet itself? And it works! Here are the steps:
The technique below requires that you have Microsoft Query installed. It is an optional component which you may have to add — it is under Office Tools. Note that this also works with Pivot Charts. The steps to create the Pivot Table are:
- Add a or select a new sheet
- Select Data/Get External Data/From Other Sources
- Choose “From Microsoft Query”
- From the database tab, select Excel, then press OK
- Select the file you are working on.
- Go to options, and check System Tables
- Select page with data. Expand to select what want. You can select all
- Click Next through filter and sort
- Click Finish “Return Data to Microsoft Excel”
- At Import Data, choose Pivot Table (or Pivot Chart) Report.
You will see the data being queried as the pivot table is generated. Unlike a standard Pivot Table, you will not have a second copy of the data as part of the document. The new size is only slightly larger than the original document.