digest
Short Summary
Main recommendation: To build a PivotTable from more than 1 million rows in Excel 2013 without Power Pivot, import the data through Get External Data, send it to the Data Model, and then build the PivotTable from that external data source (as long as you don't need to group by dates). The video's example uses an 84 MB Access database with about 1.3 million records, which cannot be loaded as a normal worksheet table. The method works, but it does not support grouping by dates.
Key Points
- The Data Model is built into every Excel 2013 workbook. It uses a columnar database that handles large data volumes and compresses it, shrinking the file.
- The imported data does not appear on the worksheet. You work with it only through PivotTables and slicers.
- The Access source file was 84 MB, while the resulting Excel file was about 22 MB, which is still large but much smaller.
- Date grouping is not available for PivotTables built this way, so you cannot right-click a date field to group it.
- The method works for any large data source, such as a text file, not only Access.
Details Worth Noting
- Concrete instructions: Go to Data > Get External Data, choose the source (here, From Access), navigate to the file, double-click it, select the correct table, and click OK. In the Import dialog, choose to add the data to the Data Model. Then press Alt, N, V (Insert PivotTable) and select "Use an external data source." Click Choose Connection, open the Tables tab, select the table listed under Tables in Workbook Data Model, and click Open. Place the PivotTable on an existing sheet (the example uses A7) and click OK.
- Pivot fields from the Data Model show a dark line on top, which confirms the PivotTable is using the Data Model.
- To format a numeric field such as Sales, right-click it, choose Number Formatting, and select a format such as Currency with no decimals.
- To show full field names in the layout, go to PivotTable Design > Report Layout and choose Outline or Tabular. The video uses Outline.
- To add a slicer, go to PivotTable Analyze > Insert Slicer and select a field such as City, then click OK.
- The video does not state a row count limit for the Data Model or mention any other version requirements beyond Excel 2013.
Bottom Line
This is a practical workaround for analyzing very large datasets in Excel 2013 without Power Pivot, as long as you don't need to view the raw table or group by dates.
