Power Pivot in Excel
The Excel Data Model: load data, relate tables, write measures, PivotTables and cube formulas.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Load data into the Excel Data Model and relate the tables.
- Write measures in DAX and use them in PivotTables.
- Use cube formulas and a DAX query table to check your numbers.
- Refresh and share a workbook built on the Data Model.
Power Pivot is the DAX engine inside Excel for Windows: it holds many tables with relationships in the Data Model, and PivotTables, PivotCharts and cube formulas read from it. It uses the same DAX as Power BI, with a smaller feature set. Availability: Microsoft 365 Apps for Windows and Excel 2016/2019/2021 Professional Plus editions; not in Excel for Mac or the web (Mac can use a model built on Windows but cannot create one). If the Power Pivot tab is missing: File > Options > Add-ins > Manage COM Add-ins > Go > tick Microsoft Power Pivot for Excel.
Getting data into the Data Model
- Power Query (recommended): Data > Get Data > From Database > From SQL Server Database, server
learnsql.cpetoday.com,1433, databasecontoso, Import mode, SQL Server login. In the Navigator tick several tables and choose Transform Data or Load To... > Only Create Connection and tick Add this data to the Data Model. Filter rows and remove columns in Power Query before loading: smaller models are faster. - From an Excel table: select the table, Insert > PivotTable, tick Add this data to the Data Model. Or Power Pivot > Add to Data Model. A table loaded this way is a Linked Table that refreshes when the sheet changes.
- From the Power Pivot window: Power Pivot > Manage opens the window; Home > Get External Data > From Database > From SQL Server.
- Prefer loading to the Data Model only (no worksheet copy) for large tables, and load dimension and fact tables separately rather than one wide joined table.
- Excel for Windows 64-bit is strongly advised for big models; a workbook with a Data Model is limited by memory and file size (the file is limited to about 2 GB, and 32-bit Excel runs out of memory far sooner).
Relationships and the model diagram
Power Pivot > Manage > Home > Diagram View: drag the key column of the fact table onto the key column of the dimension. Or Data > Relationships (Data > Relationships > New). Excel's relationships are one-to-many with a single filter direction (many-to-one fact to dimension); bidirectional and many-to-many cardinalities are limited compared with Power BI. Create relationships on integer keys, one active relationship between two tables.
Date table
Power Pivot > Design > Date Table > Mark as Date Table, choose the date column, which must contain unique, contiguous dates. Build the calendar in Power Query (see the patterns topic) or use Date Table > New to have Excel create one. The DAX time intelligence functions then work.
Writing measures (the right way in Excel)
- Power Pivot > Measures > New Measure. Fill in Table name (where the measure lives), Measure name, Formula (you can type DAX with IntelliSense), Category and Format (Currency, Percentage, Number ...). Click Check formula, OK. Edit later through Measures > Manage Measures.
- In the Power Pivot window you can also type a measure in the calculation area below a table:
Total Sales := SUM(Sales[SalesAmount])(the:=operator, without a table name qualifier, only here). - Measures appear in the PivotTable Fields list with an fx icon. Always use explicit measures; dragging a numeric column into Values creates an implicit measure ("Sum of SalesAmount") that you cannot reuse in other DAX.
- Calculated columns: in the Power Pivot window, click Add Column, type
=and the formula (=RELATED(Product[Brand])). Rename the column. Use sparingly: they add to workbook size. - KPIs: right click a measure in the PivotTable field list or Power Pivot > KPIs > New KPI: base measure, target (a measure or absolute value), and status thresholds.
- Hierarchies: in the Power Pivot diagram view, create a hierarchy (Category > Subcategory > Product) for drilling in PivotTables.
Features Power BI has that Power Pivot does not (or hides)
Verify against your Excel build, because Microsoft adds to Excel's Data Model over time; as a guide: - No New Table (calculated tables) command in Power Pivot. Workarounds: create the table in Power Query (the Calendar, Top N list), load a linked Excel table, or define a DAX query table (below). - No calculation groups, field parameters, or user-defined functions created in the Excel UI. Time intelligence variants are separate measures (YTD, PY, YoY %). - No row-level security in Excel's Data Model (security belongs to Power BI, Analysis Services or the file's sharing). - No composite models, DirectQuery or incremental refresh: it is import only. - Visuals are PivotTables and PivotCharts (plus slicers, timelines, conditional formatting, sparklines, and 3D Maps), not the Power BI visual library. - Many-to-many and bidirectional relationships are limited, so model as a star schema and use TREATAS or bridge tables in DAX. - Tools like DAX Studio connect to Excel's Data Model (open the workbook, then connect) to run DAX queries and measure performance, and Tabular Editor can edit the embedded model through its Excel connection with caution (back up first).
PivotTables on the Data Model
Insert > PivotTable > Use this workbook's Data Model. In the field list choose All, then drag dimension columns to Rows/Columns/Filters and measures to Values. Slicers (PivotTable Analyze > Insert Slicer) connect to every PivotTable that shares the Data Model (Report Connections). A timeline slicer needs a date column. Show Values As (% of Grand Total, Running Total, Difference From) is a PivotTable feature, not DAX; use DAX when you need the same result in any context. Format a measure once in the measure definition (Format), not in the PivotTable's Value Field Settings, so every PivotTable shows it identically.
Cube formulas (CUBEVALUE, CUBEMEMBER)
Convert a PivotTable to formulas (PivotTable Analyze > OLAP Tools > Convert to Formulas) to lay out a pixel-perfect report. Examples against the Data Model connection ThisWorkbookDataModel:
=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Sales]", "[Calendar].[Year].&[2017]", "[Channel].[Channel].&[Online]")
=CUBEMEMBER("ThisWorkbookDataModel", "[Channel].[Channel].&[Online]")
=CUBERANKEDMEMBER("ThisWorkbookDataModel", "[Product].[Product].[Product].Members", 1)
Member keys use the ampersand form [Table].[Column].&[Value], measures are [Measures].[Name]. Referencing cells (slicer selections via CUBESET) makes the report interactive.
Query the Data Model with DAX (a DAX query table)
Data > Existing Connections > Tables tab > pick any table in the Data Model > Open > choose Table. Then Table Tools > Design > Properties (or Data > Queries & Connections > right-click the connection > Properties > Definition): set Command Type to DAX and Command Text to a query such as:
EVALUATE
SUMMARIZECOLUMNS('Calendar'[Year], Product[Brand], "Sales", [Total Sales], "Margin %", [Gross Margin %])
ORDER BY 'Calendar'[Year], [Sales] DESC
The result lands in a worksheet table that refreshes with the model, a way to get a DAX-calculated table in Excel without New Table.
Refresh and sharing
Data > Refresh All refreshes Power Query queries and the Data Model. Save the connection credentials (Data > Queries & Connections > Properties > Definition). Sharing: send the workbook (the Data Model rides inside it) or publish to OneDrive/SharePoint; Excel on the web can open and refresh-view PivotTables built on a Data Model but cannot edit the model (check your tenant for current behavior). Large models: reduce columns and rows, avoid wide text columns, load only needed tables.
Typical first lesson in Excel (Contoso)
- Data > Get Data > From SQL Server, server
learnsql.cpetoday.com,1433, databasecontoso, tables FactSales, DimDate, DimProduct, DimProductSubcategory, DimProductCategory, DimStores, DimChannel; Transform Data, keep the columns you need, Close & Load To > connection only, Add to Data Model. - Power Pivot > Manage > Diagram View: relate FactSales to the dimensions.
- Mark the calendar table as date table.
- Add measures
Total Sales := SUM(FactSales[SalesAmount]),Total Cost,Gross Margin,Gross Margin %. - Insert > PivotTable from the Data Model: Category on rows, Year on columns, Total Sales in values.
- Add
Sales YTD = TOTALYTD([Total Sales], DimDate[Datekey])(use your date table's column) andSales PY. Name the Power Query steps (rename Source, Navigation) for clarity and keep keys as whole numbers.
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.