How to change the layout of your pivot tables

This Pivot Table lesson shows you how to use the Pivot Table Field Layout to quickly change the layout of your pivot table. This allows you to try different pivot table layouts so you can be sure your data is being grouped, aggegated and displayed in the most useful way possible. It also allow you to generate multiple reports from the same underlying data without having to create multiple pivot tables.

A quick recap on creating a pivot table

In our first lesson, How to create a Pivot Table, we took a data table of sales data for three months and created a basic pivot table that showed daily sales over that time broken down by salesperson and by product sold. The resulting pivot table looked like this:

The Field list and Field layout for this pivot table looked like this:

This lesson will focus on how you can change your pivot table layout using the Field List and the Field Layout panes.

Changing the Pivot Table Field Layout - a simple example

The Pivot Table Field Layout is the key to understanding how you can change the layout of your pivot table.

The great thing about pivot tables is that you can easily move fields around in the field layout to see whether a certain layout tells the story about your data that you want it to. Excel changes your layout almost instantly, so you can try different scenarios very quickly and easily. That said, there may be a delay in updating the layout of your pivot table if you have a lot of data or a complex layout. You can use the Defer Layout Update option to stop excel rebuilding the pivot table layout every time you make a change to the field layout.

• To change your layout, the quickest way is simply to drag and drop fields between (or within) the different areas.
• A simple example might be to show the rows by Salesperson and then Date, rather than the current layout of Date then Salesperson.
• To do this, simply click on the Date label and drag it below the Salesperson label:
  
• The pivot table changes to look like this:
  
• As you can see, we now have Abdul's sales first, with a row for each day on which he sold at least one product (dates on which he didn't sell anything aren't shown). If you were to scroll down the pivot table, you would see each sales person listed in turn, with their sales by day as they are for Abdul.
• This isn't necessarily the most useful way to show this data, so you might choose to collapse the date field to show only the names of the sales people and their overall sales. Simply click the button next to each sales person to make the spreadsheet look like this:
  
• Of course, another way to get a similar result to that above would be simply to remove the date field from the Field Layout area, to get this:
  
• Another option would have been to aggregate the Date data and show it grouped by week, rather than showing every day. The example below shows the result of doing that. Note that this isn't done by changing anything in the field layout - you do it by operating directly on the pivot table itself. You can learn more about grouping data by date in this lesson (opens in a new window):
  

Pivot Table Layouts - some more examples

Let's look at some different scenarios for displaying our report data. You can download the spreadsheet used in these examples from the Worked Examples box in the right column so you can reproduce what you see here.

Example 1 - Show sales by salesperson then by item

• Change the pivot table field layout to look like this:
  
• The pivot table changes to look like this:
  ​

Example 2 - show sales by item and then by date, with salespeople listed in columns

• Change the pivot table field layout to look like this:
  
• The pivot table changes to look like this:
  

Summary

As you'll see from these examples, and from experimenting with your own pivot tables, a lot of the power of Pivot Tables comes from the ability they give you to quickly and easily change the layout of your report table. We covered some faily simple examples here. In our next lesson, we'll look at the only element of the Pivot Table Field Layout we haven't looked at yet - the Pivot Table Report Filter.