Power Pivot in Excel: Import and Analyse Millions of Rows

What is Power Pivot?

Power Pivot is a feature in Excel for Windows that stores data in the Data Model instead of on a worksheet. The Data Model compresses the data, so a single workbook can hold millions of rows. In the video, a file with 5.4 million records is analysed in Excel, far beyond the worksheet limit of 1,048,576 rows.

The Data Model is the same engine used by Power BI. Everything you learn here, from relationships to DAX measures, works the same way in Power BI.

When should you use it?

• Your data has more than about a million rows.
• The data is in several tables, such as Sales, Products and Stores, and you want to analyse them together without VLOOKUP.
• Your workbook has become large and slow.
• You need calculations that PivotTables cannot do on their own, such as year-on-year growth.

Step 1: Enable Power Pivot

1. Go to File › Options › Add-ins.
2. At the bottom, choose COM Add-ins in the Manage box and click Go.
3. Tick Microsoft Power Pivot for Excel and click OK.

A Power Pivot tab appears on the ribbon. Click Power Pivot › Manage to open the Power Pivot window.

Step 2: Import data into the Data Model

For a large text or CSV file, import it directly instead of opening it in Excel, which would cut it off at the row limit.

1. In the Power Pivot window, go to Home › Get External Data › From Other Sources.
2. Choose Text File and click Next.
3. Browse to the file, set the column separator, and tick Use first row as column headers.
4. Untick any columns you don’t need, then click Finish.

Power Pivot loads every row. The row count shows at the bottom of the window.

Step 3: Fix data types

Text files often import dates as text. Select the date column and, on the Home tab, set Data Type to Date and choose a format. Do the same for numbers stored as text. Correct data types are needed for date grouping and calculations, and they also help compression.

Step 4: Create a PivotTable from the Data Model

1. In the Power Pivot window, click Home › PivotTable, or in Excel go to Insert › PivotTable › From Data Model.
2. Choose a new worksheet.
3. Drag fields from the Data Model into Rows, Columns and Values, just like a normal PivotTable.

The PivotTable summarises all the millions of rows, and it stays fast because the calculation happens in the Data Model.

Step 5: Write your first measure

A measure is a calculation written in DAX. It works in any PivotTable built on the Data Model. Go to Power Pivot › Measures › New Measure, or type it in the calculation area under a table in the Power Pivot window:

Total Sales := SUM ( Sales[Amount] )
Orders := COUNTROWS ( Sales )
Average Order Value := DIVIDE ( [Total Sales], [Orders] )

Measures are better than adding calculated columns to your data: they don’t increase the file size, and they recalculate for every filter in the PivotTable.

Step 6: Connect tables with relationships

If you import more than one table, for example Sales and Products, open Home › Diagram View in the Power Pivot window and drag ProductID from one table to the other. You can then use fields from both tables in one PivotTable without a single lookup formula. This is covered in detail in the next part of the series.

FAQ