Software

Get the most out of your Excel PivotTables with these handy tips

Once you tap into the power and flexibility of PivotTables, you'll be able to summarize data in the most clear and meaningful ways. Here are several techniques that will help.

hero
Image: iStockphoto.com/serggn

Image: iStockphoto.com/serggn

Excel's PivotTable feature lets you summarize data into meaningful information without changing the data's structure. If you have lots of numbers to crunch, a PivotTable might be your most efficient route. The earlier article Make summarizing and reporting easy with Excel PivotTables explains how to count, sum, and create custom groups based on numeric fields, but there's still more to learn. After reading this article, you'll be able to do the following:

    • Format columns on the fly
    • Create custom groups based on text fields
    • Change the name of a field
    • Drill down to view details
    • Add a field twice

    I'll be working in Excel 2016 on a Windows 10 system, but you can use any .xlsx version to follow along. Each version has subtle differences, especially in default structure, so your results might not (exactly) match those for 2016. Differences should be insignificant.

    You can't generate a PivotTable in 365. However, once you've generated the PivotTable in Excel 2016 locally, you can save it to 365 for sharing and editing. Excel 2003 supports the PivotTable object, but there are no specific instructions for that version in this article beyond the following: To generate a PivotTable, choose PivotTable And PivotChart Report from the Data menu to launch a wizard that will walk you through the process.

    You can work with your own data or download the demonstration .xlsx or .xls file. For the sake of backward compatibility, the example PivotTable is based on a range, not a Table object. You can work with a Table if you prefer.

    An empty PivotTable

    We'll start by generating an empty PivotTable frame based on the data set shown in Figure A. If you're using the downloadable demo file, you can skip to the next section. Otherwise, you can create the table as follows:

    1. Select any cell in the data set, click the Insert tab, and then click PivotTable in the Tables group.
    2. Check the range and specify where you want the PivotTable.
    3. Click OK.

    Figure A

    Figure A

    We'll customize this PivotTable.

    We'll build on this empty frame to demonstrate several advanced PivotTable features.

    Format on the fly

    The PivotTable frame always starts out empty. Generate the table by dragging columns from the field list to the sections below: Filters, Columns, Rows, and Values. (The names of these sections vary from version to version.) In the next section, we're going to create a custom text group based on the Region values. With that in mind, let's add the appropriate fields to the field list and apply some formatting as we go:

    1. Drag Region to the Rows section.
    2. Drag Amount to the Values section. Choose Value Field Settings from the dropdown (Figure B) and choose Max (Figure C) but don't click OK.
    3. Click Number Format (circled in Figure C).
    4. Choose Currency, set the decimal places to 0 (Figure D), and click OK twice to see the formatted Region column shown in Figure E.

    Figure B

    Figure B

    Change the default aggregate.

    Figure C

    Figure C

    Select Max to return the maximum number for each group.

    Figure D

    Figure D

    Set a format on the fly.

    Figure E

    Figure E

    We set the currency format while selecting an aggregate.

    You can continue to add and remove fields by simply moving them to and from the lower section. We're working with only two fields to keep the example simple. You'll get a glimpse of this task in the "Add a field twice" section below.

    A custom text group

    Next, we'll create two custom groups: Northeast and Southeast and Northwest and Southwest:

    1. Select one of each item in the group, holding down the [Ctrl] key. In this case, select cell G6. Then, press and hold [Ctrl] while clicking G8 (Northeast and Southeast).
    2. Right-click either selected value and choose Group from the resulting submenu, shown in Figure F.
    3. Repeat step 2, selecting Northwest and Southwest. Figure G shows the results.

    Figure F

    Figure F

    Choose Group to create the first multi-value group.

    Figure G

    Figure G

    We have three groups instead of five.

    Renaming fields

    Excel does its best to name groups and summarizing columns, but you might want to change them. For instance, let's change the Group1 and Group2 names to East and West, respectively, and Max of Amount to Amount. You're going to be surprised at how easy this is!

    Simply select G7 and type East. Then, select G10 and type West, as shown in Figure H. You don't need to set anything—just provide the new name.

    Figure H

    Figure H

    Rename the custom groups.

    Now, select H4 and type Amount. This time there's a problem, as you can see in Figure I. There's a conflict because the underlying field is named Amount. The solution is easy; enter Top Regional Order. Another name works as well as what you'd planned.

    Figure I

    Figure I

    You can't rename this field.

    There's a simple trick that you can use if you must reuse the underlying field name: Add a space character before or after the name. These types of tricks are easy to forget and can be difficult to troubleshoot later, but this one seems harmless.

    Drill down

    You might want to see the details that make up a summarized value. You can easily do so by double-clicking it. For example, double-click H8 to view the records for the Northeast. Figure J shows the results. Excel displays the subset in a new sheet, which you can keep or delete. Each subset will be displayed in its own sheet. You can't drill down on the custom East or West group.

    Figure J

    Figure J

    You can quickly see the detail records for a summary value.

    Add a field twice

    The information you need will often lie in evaluating the same field in different ways. To illustrate how flexible the PivotTable can be, let's add a second Amount field to the table and format it to display the maximum value as a percentage of the grand total for each group as follows:

    1. Drag Amount to the Values section again.
    2. Choose Value Field Settings from the dropdown.
    3. Click the Show Values As tab.
    4. From the Show Values As dropdown, select % of Grand Total but don't click OK yet.
    5. Earlier, we replaced field names by typing over an existing name, which is convenient after the fact. However, you can rename fields when you add them by entering a name in the Custom Name control. As you can see in Figure K, I replaced the default Sum of Amount with Region Percentage.
    6. Now, click OK to see the table shown in Figure L.

    Figure K

    Figure K

    Choose a display and change the field's name.

    Figure L

    Figure L

    It took little effort to create this summarizing PivotTable.

    PivotTables turn data into useful information. They're flexible and can accommodate almost any summarizing scenario you might need.

    Send me your question about Office

    I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

    Also read...

    About

    Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

    Editor's Picks