Using PowerPivot in Microsoft Excel 2013

For the complete course click here.

Starting PowerPivot

PowerPivot is an add-in that is included in Microsoft Excel Professional Plus 2013. It was also available in Excel 2010, but has seen many improvements to its functionality in the 2013 version. PowerPivot is not available in Excel 2007. PowerPivot essentially expands the abilities of the Excel data model to allow for robust data management and reporting for PivotTables and PivotCharts.

Before you can begin using PowerPivot, however, you must enable it within Excel. PowerPivot is an “Add-in” program that must be enabled within Excel before you can use it. To enable PowerPivot, click the “File” tab in the Ribbon to open the backstage view. Then click the “Options” category at the left side of the backstage view to open the “Excel Options” window. Within this window, click the “Add-Ins” category shown at the left side. At the bottom right side of this window, select “COM Add-ins” from the “Manage” drop-down and then click the “Go…” button to open the “COM Add-Ins” window. Check the checkbox for the “Microsoft Office PowerPivot for Excel” add-in, and then click the “OK” button to the right to enable the add-in.

At this point, you should see a new “PowerPivot” tab appear within the Ribbon. You can click this tab within the Ribbon to view the commands used to mange your PowerPivot data within Excel.

Managing the Data Model

You can click the “Manage” button within the “Data Model” button group on the “PowerPivot” tab within the Ribbon to open a separate, visual instance of the workbook’s data model that you can then manage in Excel. The Ribbon within the “PowerPivot for Excel” window has four tabs: “File,” “Home,” “Design,” and “Advanced.” Below that is the Formula Bar within the data model. Below the Formula Bar is the “Data View” of the tables within the data model. In a separate pane below the data area is the “Calculation Area.” Below the calculation area you can see the names of the various tables within the data model displayed as tabs. You can click these tabs to switch between the tables within the data model. A recordset navigator is displayed for the currently selected table at the bottom of the window. You can click the buttons within the navigator to move back and forth through the records within the selected table.

In addition to the “Data View” of the data model, you can also view the “Diagram View” of the data model by clicking the “Diagram View” button within the “View” button group on the “Home” tab of the Ribbon within the data model window. This view allows you to see the tables in the data model as diagrams that display the links, or relationships, between fields in the tables. You can also create relationships between tables by simply clicking and dragging a field from one table and then dropping it onto the related field within another table. You can also right-click on the relationship line shown between two fields, and then select the “Edit Relationship” command to open the “Edit Relationship” window, if needed. You could also select the “Delete” command from the pop-up menu to delete a selected relationship from the data model. Note that you will need to click the “Delete from Model” button in the confirmation dialog box that appears to delete the relationship, if desired. You can click the “Data View” button within the “View” button group in the Ribbon again to toggle the view back to “Data View” when you are finished.

Calculated Columns and Fields

You can create calculated columns and calculated fields within the tables shown in the PowerPivot data model to create values within a table which you can then add to PivotTables and PivotCharts. This is one of the primary reasons to use PowerPivot add-in versus the standard PivotTables within Excel. There are many different types of formulas that are available within the PowerPivot data model that allow you to calculate values of the existing columns within a table. Note that these formulas are not always exactly the same as the standard workbook formulas used within Excel. These formulas are called DAX formulas, and sometimes use a slightly different function and syntax to calculate values that normal Excel functions. However the syntaxes are very similar. Excel will assist you in creating the DAX formulas for calculated columns and fields so that you won’t need to worry about the syntax of the formulas that you create.

You create calculated columns within a table to create a column value that you can then summarize within the PivotChart or PivotTable in the “Values” section. For example, if you had both a “Quantity” and “Unit Price” field within an “Order Details” table, you could create a new calculated column that would display the result of the product of these two columns as an “Order Total” calculated column. You could then add this column to the “Values” section of a PivotChart or PivotTable to find the sum of order totals for a given grouping. Other uses for a calculated column would be to derive a new column that you can use in the “Row” or “Columns” sections within a PivotTable or PivotChart. For example, if you had the “Order Date” field within an “Orders” table, but wanted to group results based on the quarter of the year in which the order was placed, you could create a calculated column within the table that would display the quarter of the year for each associated “Order Date” value. You could then add this newly created column into the desired section within a PivotChart or PivotTable to be able to group by the values returned by this calculated column.

To create a calculated column in a table within the PowerPivot data model, first select the tab of the table within the data model window. Then click into the topmost cell within the “Add Column” column at the far right end of the table. You can then enter the formula that you want the column to calculate into the selected field.

Note that for formulas that you enter by hand, the formula is displayed within the Formula Bar. You start by entering the equal sign, followed by the field names enclosed in brackets, joined together by the standard mathematical operators. Note that you can also simply click on the field names of the fields within the table to add a field reference to the formula that you enter. Then simply click the checkmark button in the Formula Bar or press the “Enter” key on your keyboard to accept the formula.

Note that you can also create a formula that uses a function by clicking the “Insert Function” button within the “Calculations” button group on the “Design” tab in the Ribbon of the data model’s window to open the “Insert Function” dialog box. This dialog box shows the functions that you can insert. You can select a function within this listing to see the function and any additional arguments that the function requires shown at the bottom of the dialog box. Select a function to use for the formula from the listing shown and then click the “OK” button to insert it into the Formula Bar. You can then finish entering the additional arguments that the function requires into the Formula Bar. Then simply click the checkmark button in the Formula Bar or press the “Enter” key on your keyboard to accept the formula.

You can also create a calculated field, called a “measure” in PowerPivot for Excel 2010, to display information within the “Values” section of a PivotChart or PivotTable that is more complex than the usual functions allow. Normally, the values of a selected column are calculated using the “SUM” function or other aggregate function in the “Values” section of a PivotTable or PivotChart. This is what is called an “implicit” calculated field within a PivotChart or PivotTable. However, these types of fields within the normal PivotTables and PivotCharts are limited to performing one aggregate function, such as “SUM,” on the field that is placed into the “Values” section. Using PowerPivot, you can create an “explicit” calculated field that is derived from the values within table columns, which you can then add to the “Values” section within a PivotTable or PivotChart. For example, if you wanted to display a field within the “Values” section that was the sum of an “Order Totals” column within a table with a 10% increase to its value as a fiscal projection, you would need to create that field as an “explicit” calculated field within the data model window of PowerPivot. The formula would look something like =SUM([Order Totals])*1.10.

To create a calculated field within the PowerPivot data model window, you can click into the upper-left cell within the “Calculation Area” shown at the bottom of the “Data View” of the data model. Note that you can select any cell within the calculation area, if desired. Some people like to place the calculated fields at the bottom of the columns to which the aggregate calculated field refers. Once you have selected a cell in this area, you can then enter a formula for the calculated field and press the “Enter” key on your keyboard when finished. Note that if you want to rename the field, you can select the text within the Formula Bar that appears before the colon symbol and then type a new name for the field. Once again, click the checkmark button in the Formula Bar or press the “Enter” key on your keyboard to set the name of the field when finished. If you prefer, you can simply type the name to give to the calculated field into the formula as you enter it. Using the previous example, you could name the field “Projected Increase” by entering the following into the Formula Bar for the selected cell: Projected Increase:=SUM([Total Sales])*1.10. Also note that you can use the “Insert Function” button within the “Calculations” button group on the “Design” tab in the Ribbon to insert a function versus typing one manually, if needed. Once the calculated field has been created, it can then be used within the associated PivotTables and PivotCharts in the workbook.