Excel Power Pivot window

This article walks you around the Excel Power Pivot window and gives an overview of how it differs from the Excel window. The full Power Pivot add in was added to all Excel versions in 2019. Power Pivot caused great confusion amongst Excel users. Many users didn’t even understand what it did. The name suggested that it might be some sort of new pivot table with extra features. To understand what it really is you need to first understand OLAP.

If you have no understanding of OLAP I recommend that you begin with my Excel Power Pivot 2-minute overview article first. That puts the whole subject of OLAP into context and will make the lesson below make a lot more sense.

The article below is an unedited lesson from one of the 68 short focused lessons in my Excel Expert Skills book/e-book that seeks to empower any Excel user with a complete mastery of OLAP skills.

Leave me a comment if you find the article useful.

Last Updated: September 20, 2020

note

You can paste data directly into a new data model table

To do this:

1. Select and copy the range of data that you want to import into a new Power Pivot table.

2. Open the Power Pivot window.

3. Click: Home->Clipboard->
Paste (from within the Power Pivot window).

4. Provide a name for the new table when prompted.

note

If data in the workbook changes the data model will not automatically update

The data you are looking at is an independent copy of the data residing in the Excel Product and Category tables.

If the data in the Product and Category tables changes, you will not see any change in the data model (unless you refresh the model from the original source data).

important

Do not import or transform data using Power Pivot

The Power Pivot application is a tool to construct and refine data models.

Power Pivot is not a good tool for importing data into the data model.

You should always import data into the data model using Get & Transform (in the way you did for this model in: Lesson 12‑3: Add tables to a data model).

Using Get & Transform provides powerful data transformation features that enable you to edit and refine your data in the future.

The best-practice rule:

Do not use Power Pivot to import or transform data.

… is included in: Appendix A: Power Pivot Rules.

Lesson 12-4: Understand the Power Pivot window

1. Open the sample file Stock List-2 in Excel.

2. Open the Excel Power Pivot window.

Click: Power Pivot->Data Model->Manage.

The Power Pivot window opens.

This is your first view of the Power Pivot application.

The differences between the Excel Power Pivot window and Excel

The Power Pivot window seems very similar to Excel but you are really looking at a completely different application that has been engineered to “look and feel” like Excel. This is intended to make you feel instantly at home with Power Pivot.

You can see that there is a data grid that is similar to Excel with rows and columns. You can also see tabs showing that there are two tables in the above data model (very similar to two worksheets in a workbook).

Just like Excel, you can:

Rename and delete columns.
Change column widths.
Insert new columns.
Click and drag columns to new locations.
Rename and delete sheet tabs.
Click and drag sheet tabs to re-arrange them.
Use AutoSum to aggregate all of the values in a column.
This creates a calculated measure. You’ll learn how to use calculated measures later in: Lesson 13 3: Create a DAX calculated measure.
Format cells.
Sort columns.

Unlike Excel you can't:

Type a value into a cell or change any of the information you see displayed (although you can refresh the data from the original data source).
Have more than one table in a sheet. A Power Pivot sheet contains a single table rather than a worksheet.
Refer to a cell using A1 notation (A1 notation means cell references such as B12 or A1:C14). Power Pivot does not recognize letters to identify columns. Columns must be referred to by name.
Assign different data types within a column. You cannot, for example, have text in some cells and numbers in other cells in the same column. You learned about data types in: Lesson 11‑6: Understand data types.
Have more than one formula in a column. When a formula is added to a Power Pivot column it always applies to every cell in that column. You’ll learn more about calculated columns later, in: Lesson 13‑2: Add a DAX calculated column.

Some things Power Pivot can do that Excel cannot

Create relationships between tables.
Assign a larger range of data types to columns.
Use DAX functions (you’ll learn about DAX later, in: Session Thirteen: An introduction to DAX).
Create calculated measures. You’ll learn more about measures later, in: Lesson 13 3: Create a DAX calculated measure.
Work with Big Data. An Excel worksheet can contain a maximum of just over a million rows. A Power Pivot table can contain a maximum of just over a thousand million rows.
Produce extremely fast (often perceived as instant) results even when analyzing data sets containing many millions of rows. Power Pivot uses Microsoft’s xVelocity in-memory analysis engine. This engine can scan billions of data rows per second and can produce reports in a tiny fraction of the time needed by Excel.