Creating a basic pivot table

Contents

In this chapter, you will:

  • Format your source data before creating a pivot table

  • Learn how to create a basic pivot table

  • Understand the Recommended PivotTable and the Analyze Data features

  • Use slicers to filter your report

  • Keep up with changes in the data source

  • Share the pivot cache

  • Save time with PivotTable tools

When you have a family portrait taken, the photographer takes time to make sure that the lighting is right, the poses are natural, and everyone smiles their best smile. This preparation ensures that the resulting photo is effective in its purpose.

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.

tip.jpg TIP

As you read the following pages, which discuss preparing your data, keep in mind that pivot tables have only one hard rule pertaining to the data source: The data source must have column headings, which are labels in the first row of the data that describe the information in each column. Without column headings, you cannot create 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.

Format your source data before creating a pivot table

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

Ensuring that data is in a Tabular layout

A perfect layout for the source data in a pivot table is a Tabular layout. In Tabular layout, there are no blank rows or columns. Every column has a heading. Every field has a value in every row in most cases. Columns do not contain repeating groups of data.

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.

FIGURE 2.1

FIGURE 2.1 This data is structured properly for use as a pivot table source.

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.

tip.jpg TIP

You might work for a manager who demands that the column labels be split into two rows. For example, they might want the heading Gross Margin to be split, with Gross in row 1 and Margin in row 2. Because pivot tables require a unique heading one row high, your manager’s preference can be problematic. To overcome this problem, start typing your heading; for example, type Gross. Before leaving the cell, press Alt+Enter and then type Margin. The result is a single cell that contains two lines of data.

Avoiding storing data in section headings

Examine the data in Figure 2-2. This spreadsheet shows a report of sales by month and a model for the North region of a company. Because the data in rows 2 through 24 pertains to the North region, the author of the worksheet entered the title North as a single cell in C1. This approach is effective for displaying the data, but it’s not effective for a pivot table data source.

FIGURE 2.2

FIGURE 2.2 Region and model data are not formatted properly in this data set.

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.

In addition, 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. However, for Excel to create a pivot table, this information must be included in the first row of the data.

Avoiding repeating groups as columns

The format shown in Figure 2-3 is common. A time dimension is presented across several columns. Although it is possible to create a pivot table from this data, this format is not ideal.

FIGURE 2.3

FIGURE 2.3 This matrix format is common but not effective for pivot tables. The Month field is spread across several columns of the report.

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.

Eliminating gaps and blank cells in the data source

Delete all empty columns within your data source. An empty column in the middle of your data source causes your pivot table to fail on creation because the blank column, in most cases, does not have a column name.

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. A good practice is to represent missing values with some logical missing value code wherever possible.

note.jpg NOTE

Although eliminating gaps and blank cells might seem like a step backward for those of you who are trying to create a nicely formatted report, it pays off in the end. When you are able to create a pivot table, there will be plenty of opportunities to apply some pleasant formatting.

note.jpg NOTE

In Chapter 3, “Customizing a pivot table,” you’ll discover how to apply style formatting to your pivot tables.

Applying appropriate type formatting to fields

Formatting fields appropriately helps you avoid a whole host of possible issues, from inaccurate reporting to problems with grouping and sorting.

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.

Summary of good data source design

The attributes of an effective tabular design are as follows:

  • 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).

CASE STUDY: CLEANING UP DATA FOR PIVOT TABLE ANALYSIS

The worksheet shown in Figure 2-4 is a great-looking report. However, it cannot be effectively used as a data source for a pivot table. Can you identify the problems with this data set?

FIGURE 2.4

FIGURE 2.4 Someone spent a lot of time formatting this report to look good, but what problems prevent it from being used as a data source for a pivot table?

These are the three problems with the data set and the fixes needed to get the data set pivot table ready:

  • There are blank rows and columns in the data. Column C should be deleted. The blank rows between sectors (such as rows 4, 11, and 15) also should be deleted.

  • Blank cells present the data in an outline format. The person reading this worksheet would probably assume that cells A6:A10 fall into the Consultants sector. These blank cells need to be filled in with the values from above.

  • The worksheet presents the data for each month in several columns (one column per month). Columns D through O need to be reformatted as two columns. Place the month name in one column and the units for that month in the next column.

Cleaning this data used to require some VBA code or a bunch of manual steps in Excel. The Get & Transform tools that debuted in Excel 2016 will make it very easy to clean this data. Follow these steps:

  1. Select the entire range of data. In the sample file, it would be A1:O33.

  2. Click in the Name box and type a one-word name, such as UglyData. Press Enter to name the range.

  3. On the Data tab, in the Get & Transform Data group, choose From Table/Range (see Figure 2-5).

FIGURE 2.5

FIGURE 2.5 Originally called Power Query but later rebranded as Get & Transform Data, these new tools that appeared on the Data tab in 2016 are amazing.

The Power Query Editor will open. Notice you have ribbon tabs for Home, Transform, Add Column, and View. Follow these steps in the Power Query Editor.

  1. The formerly blank column C now has a heading of Column 3. Click that heading and choose Remove Columns from the Home tab (see Figure 2-6).

FIGURE 2.6

FIGURE 2.6 The Power Query Editor offers tools that are often better than their Excel equivalents.

  1. Click the Customer heading. Choose Home | Remove Rows | Remove Blank Rows (see Figure 2-7).

FIGURE 2.7

FIGURE 2.7 Deleting blank rows is a built-in command in Power Query.

  1. Select the Sector column header. From the Transform tab, choose Fill | Down (see Figure 2-8). This amazing command will replace all the null cells with the value from above.

FIGURE 2.8

FIGURE 2.8 Fill Down replaces Home | Find & Select | Go To Special | Blanks | OK, and then entering =A2 and pressing Ctrl+Enter. It is far easier to remember one command instead of many obscure commands strung together.

  1. Select both the Sector and Customer headings.

  2. Open the Unpivot Columns dropdown on the Transform tab and choose Unpivot Other Columns. The result is shown in Figure 2-9. Pause for a moment to admire the sheer simplicity of steps 5 through 8. Those are three new tools that replace far more complicated tasks in Excel. Although the data could be returned to Excel at this point, there are a few simple clean-up steps left.

FIGURE 2.9

FIGURE 2.9 At this point, you could return the data to Excel for pivoting.

  1. Right-click the Value column. Choose Rename and type Revenue.

  2. Open the Filter dropdown for Revenue. Unselect 0 to remove all the zero values.

  3. Select the Attribute Column. On the Add Column tab, choose Column From Example. The first row in your data might read “Apr.” If this data applies to the year 2029, type a value of Apr 1, 2029 in the new column. Power Query fills in the remaining rows and offers a Merged heading. Click OK (see Figure 2-10).

FIGURE 2.10

FIGURE 2.10 Add Column From Example is similar to Flash Fill in Excel, but it actually creates a formula that can be reused.

  1. Right-click the heading for the new Merged column. Choose Rename and type Date as the heading name.

  2. With the Date column selected, click the Transform tab. Open the Date Type dropdown and choose Date. The text dates are converted to real dates.

  3. You no longer need the month abbreviations shown in the Attribute column. Choose the Attribute column and then Home | Remove Columns. Before you return to Excel, look at the right side of the Power Query window for the list of Applied Steps. This is the world’s greatest Undo stack. You can click any step and see what the data looked like at that point. If you made a mistake several steps ago, you can click that step and make a correction. If you want to be more impressed, select the View tab and choose Advanced Editor. All that code is a programming language called “M.” By doing steps 4 through 13, you successfully wrote a program that can be reused the next time you download similar data from the IT department.

  4. Select Home | Close | Load. Your original data stays on Sheet1, and a new Sheet2 is added to the workbook (see Figure 2-11). The cleaned data is narrow and tall. In general, narrow and tall data sets are better for pivoting.

FIGURE 2.11

FIGURE 2.11 Just 11 steps in Power Query quickly cleaned the ugly data.

Not only is Power Query fast, it makes it easy to redo the data cleansing. Go back to Sheet1 and change any number in the original data. Go to Sheet2. Expand the Queries & Connections panel so you can click the Refresh icon on the far right of the UglyData query. Power Query repeats all the steps and updates the result.

Save to your account

This chapter is from the book