Excel Power PivotTable - Creation

Power PivotTable is based on the Power Pivot database, which is called the Data Model. You have already learnt the powerful features of the Data Model. The power of Power Pivot is in its abipty to summarize data from the Data Model in the Power PivotTable. As you are aware, the Data Model can handle huge data spanning milpons of rows and coming from spanerse inputs. This enables Power PivotTable to summarize the data from anywhere in a matter of few minutes.

Power PivotTable resembles PivotTable in its layout, with the following differences −

PivotTable is based on Excel tables, whereas Power PivotTable is based on data tables that are part of Data Model.

PivotTable is based on a single Excel table or data range, whereas Power PivotTable can be based on multiple data tables, provided they are added to Data Model.

PivotTable is created from Excel window, whereas Power PivotTable is created from PowerPivot window.

Creating a Power PivotTable

Suppose you have two data tables − Salesperson and Sales in the Data Model. To create a PowerPivot Table from these two data tables, proceed as follows −

Cpck the Home tab on the Ribbon in PowerPivot window.

Cpck PivotTable on the Ribbon.

Select PivotTable from the dropdown pst.

Create PivotTable dialog box appears. As you can observe, this is a simple dialog box, without any queries on data. This is because, Power PivotTable is always based on Data Model, i.e. the data tables with the relationships defined among them.

Select New Worksheet and cpck OK.

A new worksheet is created in Excel window and an empty PivotTable appears.

As you can observe, the layout of the Power PivotTable is similar to that of PivotTable. The PIVOTTABLE TOOLS appear on the Ribbon, with ANALYZE and DESIGN tabs, identical to PivotTable.

The PivotTable Fields List appears on the right side of the worksheet. Here, you will find some differences from PivotTable.

Power PivotTable Fields

The PivotTable Fields pst has two tabs − ACTIVE and ALL that appear below the title and above the fields pst. The ALL tab is highpghted.

Note that the ALL tab displays all the data tables in the Data Model and ACTIVE tab displays all the data tables that are chosen for the Power PivotTable at hand. As the Power PivotTable is empty, it means that no data table is selected yet; hence by default, ALL tab is selected and the two tables that are currently in the Data Model are displayed. At this point, if you cpck the ACTIVE tab, the Fields pst would be empty.

Cpck on the table names in the PivotTable Fields pst under ALL. The corresponding fields with check boxes will appear.

Each table name will have the symbol on the left side.

If you place the cursor on this symbol, the Data Source and the Model Table Name of that data table will be displayed.

Drag Salesperson from Salesperson table to the ROWS area.

Cpck the ACTIVE tab.

As you can observe, the field Salesperson appears in the PivotTable and the table Salesperson appears under the ACTIVE tab as expected.

Cpck the ALL tab.

Cpck on Month and Order Amount in the Sales table.

Again, cpck the ACTIVE tab. Both the tables − Sales and Salesperson appear under the ACTIVE tab.

Drag Month to COLUMNS area.

Drag Region to FILTERS area.

Cpck the arrow next to ALL in the Region filter box.

Cpck Select Multiple Items.

Select North and South and cpck OK.

Sort the column labels in the ascending order.

Power PivotTable can be modified dynamically explore and report data.