2. Creating a Basic Pivot Table

When you create a pivot table report, you’re the photographer, taking a snapshot of your data. By taking time to make sure your data looks its best, you can ensure that your pivot table report is effective in accomplishing the task at hand.

One of the benefits of working in a spreadsheet is that you have the flexibility of laying out your data to suit your needs. Indeed, the layout you choose depends heavily on the task at hand. However, many of the data layouts used for presentations are not appropriate when used as the source data for a pivot table report.

However, just because a pivot table report is created successfully does not mean that it’s effective. A host of things can go wrong as a result of bad data preparation—from inaccurate reporting to problems with grouping and sorting.

Let’s look at a few of the steps you can take to ensure that you end up with a viable pivot table report.

Figure 2.1 shows an example of data structured properly for a pivot table. There are headings for each column. Even though the values in D2:D6 are all the same model, the model number appears in each cell. Month data is organized down the page instead of across the columns.

Tabular layouts are database centric, meaning you would most commonly find these types of layouts in databases. These layouts are designed to store and maintain large amounts of data in a well-structured, scalable format.

Also in Figure 2.2, the author was very creative with the model information. The data in rows 2 through 6 applies to Model 2500P, so the author entered this value once in A2 and then applied a fancy vertical format combined with Merge Cells to create an interesting look for the report. Again, although this is a cool format, it is not useful for pivot table reporting.

Also, the worksheet in Figure 2.2 is missing column headings. You can guess that column A is Model, column B is Month, and column C is Sales, but for Excel to create a pivot table, this information must be included in the first row of the data.

The problem is that the headings spread across the top of the table pull double duty as column labels and actual data values. In a pivot table, this format would force you to manage and maintain six fields, each representing a different month.

Delete all empty rows within your data source. Empty rows may cause you to inadvertently leave out a large portion of your data range, making your pivot table report incomplete.

Fill in as many blank cells in your data source as possible. Although filling in cells is not required to create a workable pivot table, blank cells are generally errors waiting to happen. So a good practice is to represent missing values with some logical missing value code wherever possible.

Make certain that any fields to be used in calculations are explicitly formatted as a number, currency, or any other format appropriate for use in mathematical functions. Fields containing dates should also be formatted as any one of the available date formats.

The first row of your data source is made up of field labels or headings that describe the information in each column.

Each column in your data source represents a unique category of data.

Each row in your data source represents individual items in each column.

None of the column names in your data source double as data items that will be used as filters or query criteria (that is, names of months, dates, years, names of locations, or names of employees).

To ensure that the pivot table captures the range of your data source by default, click any single cell in your data source. Next, select the Insert tab and find the Tables group. In the Tables group, select PivotTable and then choose PivotTable from the drop-down list. Figure 2.6 demonstrates how to start a pivot table.

Choosing these options activates the Create PivotTable dialog, shown in Figure 2.7.

As you can see in Figure 2.7, the Create PivotTable dialog asks you only two fundamental questions: Where’s the data that you want to analyze? and Where do you want to put the pivot table? Here’s how you handle these two sections of the dialog:

Choose the Data That You Want to Analyze—In this section, you tell Excel where your data set is. You can specify a data set that is located within your workbook, or you can tell Excel to look for an external data set. As you can see in Figure 2.7, Excel is smart enough to read your data set and fill in the range for you. However, you always should take note of the range Excel selects to ensure that you are capturing all your data.

Choose Where You Want the PivotTable Report to Be Placed—In this section, you tell Excel where you want your pivot table to be placed. This is set to New Worksheet by default, meaning that your pivot table will be placed in a new worksheet within the current workbook. You will rarely change this setting because there are relatively few times you’ll need your pivot table to be placed in a specific location.

After you have answered the two questions in the Create PivotTable dialog, simply click the OK button. At this point, Excel adds a new worksheet that contains an empty pivot table report. Next to that is the PivotTable Fields list, shown in Figure 2.8. This pane helps you build your pivot table.

Filters—Adding a field to the Filters area enables you to filter on its unique data items. In previous versions of Excel, this area was known as the Report Filters area.

Columns—Adding a field into the Columns area displays the unique values from that field across the top of the pivot table.

Rows—Adding a field into the Rows area displays the unique values from that field down the left side of the pivot table.

Values—Adding a field into the Values area includes that field in the values area of your pivot table, allowing you to perform a specified calculation using the values in the field.

Before you start dropping fields into the various areas, answer two questions: “What am I measuring?” and “How do I want to see it?” The answer to the first question tells you which fields in your data source you need to work with, and the answer to the second question tells you where to place the fields.

Say that in this case, you want to measure the dollar sales by region. This automatically tells you that you need to work with the Sale Amount field and the Region field. How do you want to see it? You want regions to go down the left side of the report and the sales amount to be calculated next to each region.

To achieve this effect, you need to add the Region field to the Rows area and add the Sale Amount field to the Values area.

Find the Region field in the PivotTable Fields list, and place a check in the check box next to it. As you can see in Figure 2.9, not only is the field automatically added to the Rows area, but your pivot table is updated to show the unique region names.

Now that you have regions in your pivot table, it’s time to add in the dollar sales. To do that, simply find the Sale Amount field and place a check next to it. As Figure 2.10 illustrates, the Sale Amount field is automatically added to the Values area, and your pivot table report now shows the total dollar sales for each region.

At this point, you have already created your first pivot table report!

The report is instantly restructured, as shown in Figure 2.13.

This feature is Microsoft’s way of getting you up and running with pivot tables by simply creating one for you. The idea is simple: Place your cursor in a tabular range of data and then click the Recommended PivotTables icon. Excel shows you a menu of pivot tables it thinks it can create for you, based on the data in your range (see Figure 2.17). When you find one that looks good to you, click it and then click OK to have Excel create it.

Another way to get to a recommended pivot table is to right-click anywhere in your data range and choose the Quick Analysis option. The context menu shown in Figure 2.18 activates, and you can select a recommended pivot table under the Tables section.

The reviews on this feature are mixed. On one hand, it does provide a quick and easy way to start a pivot table, especially for those of us who are not that experienced. On the other hand, Excel’s recommendations are rudimentary at best. You will often find that you need to rearrange, add, or manipulate fields in the created pivot table to suit your needs. Although Excel might get it right the first time, it’s unlikely that you will be able to leave the pivot table as is.

There is also a chance that Excel will simply not like the data range you pointed to. For example, the data in Figure 2.19 is a valid range, but Excel doesn’t like the repeating dates. So it pops up a message to indicate that there are too many duplicates to recommend a pivot table; however, it will gladly create a blank one.

All in all, the Recommended PivotTables feature can be a nice shortcut for getting a rudimentary pivot table started, but it’s not a replacement for knowing how to create and manipulate pivot tables on your own.

The Insert Slicers dialog, shown in Figure 2.21, opens. The idea is to select the dimensions you want to filter. In this example, the Region and Market slicers are selected.

After the slicers are created, you can simply click the filter values to filter your pivot table. As you can see in Figure 2.22, clicking Midwest in the Region slicer filters your pivot table, and also the Market slicer responds by highlighting the markets that belong to the Midwest region.

In Figure 2.23, the Multi-Select toggle was enabled and then Baltimore, California, Charlotte, and Chicago were selected. Note that Excel highlights the selected markets in the Market slicer and also highlights their associated regions in the Region slicer.

Another advantage you gain with slicers is that you can tie each slicer to more than one pivot table. In other words, any filter you apply to your slicer can be applied to multiple pivot tables.

To connect a slicer to more than one pivot table, simply right-click the slicer and select Report Connections. The Report Connections dialog, shown in Figure 2.24, opens. Place a check next to any pivot table that you want to filter using the current slicer.

At this point, any filter applied via the slicer is applied to all the connected pivot tables. Again, slicers have a unique advantage over Filter fields in that they can control the filter state of multiple pivot tables. Filter fields can control only the pivot table in which they live.

To create a Timeline slicer, place your cursor anywhere inside your pivot table, select the Insert tab on the ribbon, and then click the Timeline icon (see Figure 2.25).

The Insert Timelines dialog shown in Figure 2.26 opens, showing you all the available date fields in the chosen pivot table. Here, you select the date fields for which you want to create slicers.

After your Timeline slicer is created, you can filter the data in your pivot table by using this dynamic data-selection mechanism. As you can see in Figure 2.27, clicking the April slicer filters the data in the pivot table to show only April data.

Figure 2.28 demonstrates how you can expand the slicer range with the mouse to include a wider range of dates in your filtered numbers.

Want to quickly filter your pivot table by quarters? Well, you can easily do it with a Timeline slicer. Click the time period drop-down and select Quarters. As you can see in Figure 2.29, you also have the option of selecting Years or Days, if needed.

As time goes by, your data might change and grow with newly added rows and columns. However, the pivot cache that feeds your pivot table report is disconnected from your data source, so it cannot represent any of the changes you make to your date source until you take another snapshot.

The action of updating your pivot cache by taking another snapshot of your data source is called refreshing your data. There are two reasons you might have to refresh your pivot table report:

Changes have been made to your existing data source.

Your data source’s range has been expanded with the addition of rows or columns.

The following sections explain how to keep your pivot table synchronized with the changes in your data source.

To do this, click anywhere inside the pivot table and then select Analyze from the PivotTable Tools tab in the ribbon. From here, select Change Data Source. This selection triggers the dialog shown in Figure 2.31.

All you have to do here is update the range to include new rows and columns. After you have specified the appropriate range, click the OK button.

In legacy versions of Excel, when you created a pivot table using a data set that was already being used in another pivot table, Excel actually gave you the option of using the same pivot cache. However, Excel 2016 does not give you such an option.

Instead, each time you create a new pivot table in Excel 2016, Excel automatically shares the pivot cache. Most of the time, this is beneficial: You can link as many pivot tables as you want to the same pivot cache with a negligible increase in memory and file size.

On the flip side, when you group one pivot table by month and year, all of the pivot tables are grouped in a similar fashion. If you want one pivot table by month and another pivot table by week, you have to force a separate pivot cache. You can force Excel to create a separate pivot cache by taking the following steps:

1. Press Alt+D+P on your keyboard to launch the Pivot Table Wizard.

2. Click the Next button to get past the first screen of the wizard.

3. On the second screen, select the range for your pivot table and click the Next button.

4. Excel displays a wordy message saying that you can use less memory if you click Yes. Instead, click No.

5. On the next screen, click the Finish button.

At this point, you have a blank pivot table that pulls from its own pivot cache.

Excel 2016 offers some relief for this problem by providing a way to defer layout changes until you are ready to apply them. You can activate this option by clicking the relatively inconspicuous Defer Layout Update check box in the PivotTable Fields list, as shown in Figure 2.32.

Here’s how this feature works: With the Defer Layout Update check box selected, you prevent your pivot table from making real-time updates as you move your fields around without your pivot table. In Figure 2.32, notice that fields in the areas are not in the pivot table yet. The reason is that the Defer Layout Update check box is active. When you are ready to apply your changes, click the Update button on the lower-right corner of the PivotTable Fields list.