How to Structure Source Data for a Pivot Table & Unpivot

The first and most important step to creating a pivot table is getting your data in the correct table structure or format. This post explains how to organize your source data, and why it is important. Solutions for converting your data to the correct format are also provided.
Pivot Table Source Data Comparison

This post will show you the proper way to setup or organize your source data for a pivot table.

Pivot Table Overview

Whether you are using Excel or a Google Spreadsheet, pivot tables are a great tool for summarizing and analyzing large amounts of data.  They can be huge time savers for creating reports that present your data in a clear and simple format.  With the advent of PowerPivot, there is no doubt that pivot tables are the way of the future for Excel.

Before you can create a pivot table, you must have your data laid out in the right structure.  This is the most critical step, and also the most common mistake when learning how to create pivot tables.  Once you have your data organized correctly, you will become much more proficient at creating reports, analyzing data, and finding trends.

This article will explain:

  • The correct vs. incorrect structure for pivot table source data.
  • Why it is important to understand this.
  • How to convert your reports into the right structure using formulas
    (free sample workbook). 

Data Table Structure

The first step to creating a pivot table is setting up your data in the correct table structure or format.  This is the source data you will use when creating a pivot table.  Your source data should be setup in a table layout similar to the table in the image below.

Pivot Table Data Source Structure

The following is a list of components of a data table.  These terms will be used throughout the article.

  • Fields – Columns that define the values in the rows.
  • Column Header – Name that describes the data in the field.
  • Data Records – Rows in the table below the header that contain the data.
  • Record Set – One row of data that contains values for each field.

The data table contains a column for each field and rows for each data record.  The column fields are named with descriptive attributes that define the values in the record sets (rows).  For example, your sales table may contain the following columns: Company, Region, Product, Month, and Sales Amount.  These are the descriptive fields that define what values will be in each row of the table.  Each row will contain a sales record for a different combination of company, region, product, and month.   It’s also important to note that field names (column headers) must be unique throughout the table.

Wrong Data Structure

Incorrect Structure for Pivot Source Data

Sometimes you will receive a report that is structured like the image above, with some page header info, months across the top, and products or accounts listed down the first few columns on the left.  This data is in the WRONG structure for a pivot table.  The data is already in a summary format, which is what we want the pivot table to produce.  However, you may want to use this data as a pivot table source to do your own analysis and produce different slices of the report.

Why is it Wrong?

It is important to understand why the data structure is wrong for a few reasons.

First, it will help you request the data in the proper format.  When we receive data in a summary report format like the example above, we usually don't have control over how this report is produced.  But somebody in the finance or IT organization does have control.  They should be able to produce a report in the table structure you need for your pivot.  So understanding why you need it in the correct format will save you time having to manually convert the report.  Otherwise, you will basically have to reverse engineer the report to get it in the proper table structure.

Second, it will help you understand how pivot tables work to summarize, filter, sort, and slice your data.  The basic understanding will allow you to learn more advanced techniques of adding calculated fields and items.

The job of the pivot table is to summarize your source data table based on the criteria you specify in the filter fields (Report Filter, Column Labels, and Row Labels).  You can think of it as a very advanced way to arrange and filter your data.  The pivot table is an extremely powerful tool, but can only be used to its full potential if the source data is in the right structure.

Getting the Structure Right – Setting Up Your Source Data for a Pivot Table

Convert Values Range to One Column for Pivot Table Source

In the image above, the sales data table on the right contains all sales amounts in the [Sales $] column.  With this format you could easily sum the column to produce the Total Sales $ for all companies, regions, products, and months.  You could then start filtering the columns to see only the sales for one month and one region.  A pivot table works the same way, and basically filters your table based on criteria you specify in the filter fields. 

The basic rule of the data structure is that all values of the same type need to be in one column.

This one rule should hopefully make it easier to quickly determine if your data is in the right structure.  If the data you are trying to analyze is spread out over multiple columns, then you will likely need to convert it before creating a pivot table.  In the “Original Report” above, the Sales $ are in multiple columns by month (Jan – Apr).  This one observation tells us that the data is in the wrong structure.

Converting the Data

We now know that we need to convert our original report into a table so that each value is in its own row (record set).  Each value will contain a field (column) for each attribute that defines the value (company, region, product, month).  This means that many of the field values will be repeated in the data table.

The following image shows where the values for each field are derived from in the original report.  This mapping should help you understand what is needed to convert the report into the correct structure.

Sources for Pivot Table Data Conversion Single

The image below shows another view of this conversion.  Each part of the report is color coded to make it easy to see how the data is translated to the table.  The Value Range (green) on the left side is basically stretched out into one column in the table on the right.  All the defining characteristics of the values must be entered in the fields (columns) to the left for each record set (row).

Convert Data Structure for Excel Pivot Table
Click to Enlarge


In the original report format, the page and column headers are used to describe multiple values (data points).  For example the column header for the month Jan is associated with all the rows below it for the different products.  When the data is converted to the proper format, each value will be placed in a separate row, and a month column will be created that contains all the months.   The row labels for products will repeat in a similar fashion.  
The page headers for company and region will repeat on every row of the data table because they are the same for every cell in the value range.

Solution #1 – Unpivot with Power Query

Power Query is a free add-in from Microsoft for Excel 2010 and 2013, and it makes this process really easy.  Power Query will transform your data into the correct format with the click a button.

The following screencast shows how to use the Unpivot Columns button in Power Query.

Unpivot with Power Query in Excel Screencast

I also have a video that explains the unpivot process in Power Query in more detail.

Video best viewed in full screen HD.

Checkout my article on How to Unpivot with Power Query for a full explanation.  I also have an article with a full Overview of Power Query that describes some of its best features.

As awesome as Power Query is, you might not be able to get it.  It is only available for the Professional Plus versions of Excel 2010 and 2013.  Checkout my Complete Guide to Installing Power Query to determine if your version of Excel is compatible.

Solution #2 – Convert the Data with Formulas

If you are unable to use Power Query, then you will need to reverse engineer the report to the correct format before using it in a pivot table.  This can be done with lots of copy/paste and transpose.  However, there is a faster way using formulas.

The image below shows a sample of how a report can be converted into the correct table structure using a few formulas.  A sample workbook that contains all the formulas is available for download below.

Convert Data Structure for Pivot Table with Formulas
Click to Enlarge

The model makes use of the INDEX function to reference the original report, and pull the data into the table.  The [Row Index] and [Column Index] are helper columns that contain formulas to return the correct row and column numbers used by the Index formulas in the data table.  The sample workbook contains two examples.

  • Example 1 is similar to the report format above, with page headers, column headers, and row labels.
  • Example 2 does not contain page headers, but does contain two different value types: sales and margin.  

The file contains cell comments with more detailed descriptions of the formulas.

Download

Please click the link below and the Excel file that contains the conversion model will be emailed to you immediately.  You can use this model as a template to quickly convert your report data into the proper structure for the source data of a pivot table.

You will also have the option to subscribe to my free email newsletter to stay updated with new articles and videos that will help you learn Excel.  After confirming your subscription you will be able to download my “10 Excel Pro Tips” eBook.  It's all free!

xls iconConvert Source Data for Pivot Table.xlsx (34.2 KB)

Next Steps

Now that you have a basic understanding of how your source data should look, the next step is to start creating pivot tables (and impress your boss). 🙂

If you haven't already seen it, checkout my free video training series on Pivot Tables & Dashboards.  This will help you get started creating pivot tables and show you what a powerful tool they can be.

Additional Resources

Please leave a comment below with any questions.

  • The example to convert the data has not the columns as a date format, which is why it can be converted. But what is if the data has date in the columns? Than you can not create a table any longer, which is required when converting data.

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >