Home > Pivot > Create > Source Data 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 pivot table. Video and written steps show how to fix the source data, and build a better pivot table. Also see how to fix the source data with Power Query (Get & Transform), or use a macro to unpivot the source data.

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.

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

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.

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.

NOTE: This technique uses the old Multiple Consolidation Ranges feature, which has a 256 character limit per cell. The macro temporarily combines cell text, which could result in text strings that are over that limit. Some text could be cut off.

Also see how to fix the source data with Power Query (Get & Transform), or use a macro to unpivot the source data.

Video Timeline

• 00:00 Excel Data Across Columns
• 00:26 Pivot Table Problem
• 01:12 Start Multiple Consolidation Steps
• 02:13 Unpivot the Pivot Table
• 02:46 Make Pivot Table from Unpivot Data
• 03:30 Data with Multiple Labels
• 05:35 Pivot Table from Revised Data

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.

   

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.

   

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.

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.

Get the Sample Workbook

To test the multiple consolidation ranges technique, you can get 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.

More Tutorials

Data layout guidelines

Unpivot Excel Data Macro

Unpivot, Power Query

Pivot Table Layout VBA

FAQs - Pivot Tables

Pivot Table Intro

Summary Functions

Clear Old Items in Pivot Table