Blog Archive

EXCEL: Aligning Data for Pivot Tables


What is a Pivot Table - How to Make it
How to cook a delicious dynamic chart
Excel: Creating A Thermometer Style Chart
Excel: Creating A Database Table From A Summary Table
Excel: Perform Two-Way Table Lookup
Excel: Calculate The Number Of Days In A Month
Having the data in the right format is a crucial step in creating a robust and error-free Pivot Table. If not done the right way, you can end up having a lot of issues with your pivot table.
In this tutorial, you'll learn how to prepare the source data for Pivot Table. The following topics are covered:
  • What is a good design for the Source Data for Pivot Table.
  • Common pitfalls to avoid while preparing the source data for Pivot Table.
  • Examples of Bad Source Data Designs.
  • [Case Study] Converting a badly formatted data into Pivot Table ready source data using Excel Formulas and Power Query.

Preparing Source Data For Pivot Table - Cover

What is a good design of the Source data for Pivot Table?

Let's have a look at an example of good source data for a Pivot Table.
Preparing Source Data For Pivot Table - Good Data
Here's what makes it a good source data design:
  • The first row contains headers that describe the data in the columns.Preparing Source Data For Pivot Table - Good Design Headers
  • Each column represents a unique data category. For example, Column C has product data only and column D and month data only.Preparing Source Data For Pivot Table - Good Design unique data category
  • Each row is a record that would represent one instance of the transaction or sale.Preparing Source Data For Pivot Table - one record
  • The Data headers are unique and are not repeated anywhere in the data set. For example, if you have Sales numbers for four quarters in a year, you should NOT name all of these as Sales. Instead, give these column headers unique names such as Sales Q1, Sales Q2, and so on…
    • If you don't have unique titles, you can still go ahead and create a Pivot Table and Excel would automatically make these unique by adding a suffix (such as Sales, Sales2, Sales3). However, that would be an awful way to prepare and use a Pivot Table.Preparing Source Data For Pivot Table - No Duplication in Headers

Common Pitfalls to Avoid While Preparing the Source Data

  • There shouldn't be any blank columns in the source data. This one is easy to spot. If you have a blank column in the source data, you wouldn't be able to create a Pivot Table. It will show an error as shown below.Preparing Source Data For Pivot Table - Error
  • There shouldn't be blanks cells/rows in the source data. While you can successfully create a Pivot Table despite having blank cells or rows, there are many side-effects that can come bite you later in the day.
    • For example, let's say you have a blank cell in the sales column. If you create a Pivot Table using this data and put the sales field in the columns area, it would show you the COUNT and not the SUM. That's because Excel interprets the entire column as having text data (just because of a single blank cell).Preparing Source Data For Pivot Table - Count instead of Sum
  • Apply relevant format to cells in the source data. For example, if you have dates (which are stored as serial numbers in the backend in Excel), apply one of the acceptable date formats. This would help you create the Pivot Table and use Date as one of the criteria to summarize, group, and sort the data.
    • If you have a couple of seconds, try this. Format the dates in your Pivot Table as numbers, and then create a Pivot table using this data. Now in the Pivot Table, select the date field and see what happens. It will automatically put it in the values area. That's because your Pivot Table doesn't know these are dates. It interprets these as numbers.Preparing Source Data For Pivot Table - Date as Number
  • Don't include any Column Totals, Rows Totals, Averages, etc., as a part of the source data. Once you have the Pivot Table, you can easily get these later.
  • Always create an Excel Table and then use it as the source for a Pivot Table. This is more of a good practice and not a pitfall. Your Pivot Table would work just fine with a source data that isn't an Excel Table as well. The benefit with Excel Table is that it can adjust the expanding data. If you add more rows to the data set, you don't need to adjust the source data again and again. You can simply refresh the Pivot Table and it would automatically account for the new rows added to the source data.