24.5 Creating PivotTables within Power Pivot

Within the Power Pivot module in your Excel workbook, in the Home tab, click on PivotTable. After designating where you’d like to store the new PivotTable, you can build a PivotTable much like others we have made. The difference between other PivotTables and PivotTables built within Power Pivot is the data available within the table. In the PivotTable Fields dialog, we can see all of the tables available for our use. By clicking on the arrow next to each table, we can see the available fields within each table.

Build out a PivotTable by adding the Product Category and Product Sub Category from the Products table to the Rows and the Order Date from OrderFact to the Columns. The Values should be the revenue from the OrderFact table. Apply the currency number formatting to the Values and remove any decimals to make them easier to read. Notice that since our Order Date is a Date data type, Excel automatically groups the data for us and allows us to view the numbers by year, quarter, and month.

You can add charts for this data to make it easier to visualize trends in the data.

Recall that early in this lesson we discussed that some might scoff at using Excel to analyze data, thinking that it must be limited compared to other database tools. For many small- to medium-sized businesses, however, it is very appropriate and capable of supporting analysis.

Customizing PivotTables with Slicers

Building a simple PivotTable based on data imported from a database is one very quick way to present data in a digestible manner.

In some cases you may be building a PivotTable that other individuals will use, and you may want to make some additional data available to those individuals so they can perform their own analysis using the data you’ve imported and begun to organize and display. One simple way to do this is by adding slicers to your PivotTable. A slicer is a selection of buttons displayed near a PivotTable that allows you to filter the data shown in the table.

Add a slicer to your PivotTable by navigating to the Analyze tab and clicking Insert Slicer. Then, select the field(s) you’d like to allow users to filter by. By selecting All at the top left corner of the Slicer dialog box, you can choose fields from all imported data rather than just the fields already shown on the PivotTable. Then, arrange the slicers around your PivotTable.

Now, say you have a slicer that includes a lengthy list of options, such as a few dozen product categories from which the data was drawn. You can increase the visibility of those selections by adding columns to the slicer so that the options appear in a grid format rather than a list. To add columns to a slicer, right-click on the slicer and select Size & Properties. In the Format Slicer window, increase the number of columns to the desired number. Increase the size of the slicer by clicking the border of the slicer and dragging it to a more appropriate size to display the available options. You can also add date-specific slicers to filter date fields more efficiently.