Contextures

Fix Pivot Table Source Data Layout

If a pivot table's source data is set up incorrectly, it will be difficult to build a flexible and efficient pivot table. A short video shows how to change the source data layout, and build a better pivot table. Written instructions list the steps. For a macro to unpivot the source data, go to the Unpivot Excel Data Macro page.

Poor Source Data Layout

In some workbooks, the data is set up like the example shown below, with a column for each month. In this table, the sales amounts are entered horizontally, over those 12 monthly columns.

This is a good format for a worksheet report, but the horizontal structure is a poor layout to use as source data for an Excel pivot table.

pivot table compact layout

If a pivot table is created from this source data, there are 12 value fields - one for each month. To see an annual total, a calculated field would be required, to sum all 12 months..

pivot table compact layout

Normalize the Pivot Table Source Data

Instead of a using horizontal structure, with a separate column for each month, the pivot table source data should have just one column for amounts, where all of the sales amounts are entered. In another column, enter the month name for each amount. This creates a vertical layout, and is part of normalizing the data, which will allow a flexible pivot table to be built.

In the screen shot below, the orignal data has been rearranged, into a normalized structure, with columns for Product, Month and Amount. The easy steps for changing the layout are described below. With this 3-column Excel table, it will be easy to create a flexible pivot table, and focus of specific items or months.

pivot table compact layout

Read more about Database Normalization on Wikipedia. There are other guidelines to follow too, when setting up the source data for an Excel pivot table. Find more information here: Data layout guidelines.

Quickly Rearrange Source Data

To quickly rearrange pivot table source data that is in a horizontal layout, such as the example above, use a pivot table trick to change the 13-column data into a normalized 3-column table, in a vertical structure. This video shows the steps for this easy trick, and also shows the steps for this technique, if there are two or more columns with labels. Written instructions are below the video

Follow these steps, to quickly rearrange the data into a normalized table:

  1. Select a cell in the 13-column table, and press Alt+D, and then press P, to open the PivotTable and PivotChart Wizard
  2. In Step 1, select Multiple Consolidation Ranges, and then click Next.
  3. In Step 2a, select I Will Create The Page Fields, and then click Next.
  4. In Step 2b, click in the Range box, and on the worksheet, select the entire table, including the headings, and then click Add.

    pivot table compact layout

  5. Leave the other settings at their defaults, and click Finish.
  6. A new sheet is added to the workbook, with a pivot table
  7. In the PivotTable Field List, remove the check marks from the Row and Column fields, so only the Grand Total for Value is left.

    pivot table compact layout

  8. Double-click the Grand Total cell, (cell A4 in the screen shot above), to create a new sheet, with the pivot table's data in 3 columns.
  9. Then, rename the heading cells as Product, Month, and Amount.

pivot table compact layout

Build a New Pivot Table

After the source data is normalized, in a 3-column table instead of 13 columns, the next step is to create a pivot table, based on the rearranged data.

To create a pivot table:

  1. Select any cell in the new 3-column table
  2. On the Excel Ribbon, click the Insert tab, and click Pivot Table
  3. The name of the new table should automatically appear in the Table/Range box
  4. Choose a location for the pivot table, either a new sheet, or an existing sheet
  5. Click OK, to create the pivot table
  6. Add check marks to all three fields, to add them to the pivot table layout.

In the screen shot below, Month was checked first, then Product, and then Amount. The pivot table shows the total amount for each month, and total sales for each product each month.

With normalized data source layout, the amounts are all in a single columns, and that makes this pivot table powerful and flexible.

pivot table compact layout

Download the Sample Workbook

To test the multiple consolidation ranges technique, you can download the Normalize Data for Excel Pivot Table sample workbook. The file is in xlsx format, and is zipped. There are no macros in the file.

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search Contextures

Search Contextures Sites

More Tutorials

Data layout guidelines

Unpivot Excel Data Macro

Pivot Table Layout VBA

FAQs - Pivot Tables

Pivot Table Intro

Summary Functions

Clear Old Items in Pivot Table

Search Contextures Sites

 

pivot power premium

 

 

30 Excel Functions in 30 Days

 

Excel Data Entry Popup List

 

 

 

excel chart tools

 

Excel UserForms for Data Entry

 

Last updated: June 14, 2017 11:29 PM