digest
Short Summary
Main recommendation: Import large, non-normalized Excel data sets into Power Pivot to dramatically reduce file size, because its columnar database compresses the data. In the demonstration, a 500,000-row non-normalized workbook shrank from about 20 MB to about 17 MB after import, and a normalized version shrank from about 16,000 (KB) to about 1.8 MB.
Key Points
- Non-normalized data repeats values such as sales rep names, products, prices, and regions in every row, which inflates the file.
- Normalization replaces repeated text with numeric keys that point to smaller lookup tables; in Excel this is handled with VLOOKUP, while Power Pivot can use relationships instead.
- Power Pivot stores data in a columnar format and compresses it, which is why the file size drops even for non-normalized data.
- The columnar database also improves formula speed, according to the speaker.
Details Worth Noting
- Concrete instructions:
- Open the Power Pivot window with Alt+B+M (Manage Data Model) from Excel.
- In the Get External Data group, choose "From Other Sources," scroll down, and select the Excel file option.
- Check "Use first row as column headers," enter the file path with the Browse button, and select the workbook.
- Click Next, then click Preview and Filter to check for extra columns before clicking OK.
- Click Finish to load the data, then Close, and save the workbook with Ctrl+S.
- Compare the file size before and after import to confirm the reduction.
- The demonstration used a workbook of roughly 500,000 rows, which the speaker describes as "not truly big data" by database standards but large for Excel.
- The normalized comparison file used keys that referred to separate product, sales rep, and region tables; he notes these could be related via Power Pivot relationships, to be covered later.
- The speaker imported the normalized version before recording only to show its file size, so the comparison is illustrative rather than a controlled test.
Bottom Line
Importing data into Power Pivot can shrink a workbook substantially even without restructuring the data, though normalizing the data first produced the smallest file in this example.
