The PivotTables feature within Microsoft Excel 2013 can be a powerful ally in analyzing large amounts of data. However, to reduce the possibility of errors, first make sure to prepare Excel data for PivotTables analysis by following these 8 data clean up tasks.

Failed-proof tips to get Excel 2013 data in shape for PivotTables analysis

You already know that Excel is a popular program for analyzing data, and PivotTables is one of the many powerful tools you use. This enables you to quickly analyze large amounts of data and present it in a meaningful way.

While any large set of data can be transformed into a pivot table, it is important to prepare your Excel data for PivotTables analysis in advance. Otherwise, you may run into errors or inaccuracies that misrepresent your data. Follow these instructions to ensure your pivot table.

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

Have a look at an example of good source data for a Pivot Table in excel.

This is,  what makes it a good source data design

• The first row contains headers that describe the data in columns.

• Each column represents a unique data range. For example, column C contains only product data and column D and month data.

• Each line is a record that represents a frequency of a transaction or sale.

• Data headers are unique and are not replicated anywhere in the data set. For example, if you have sales numbers for four quarters in a year, you should not name them all as sales. Instead, give these columns 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 will automatically make these unique by adding a suffix (eg Sales, Sales 2, Sales 3). However, this would be a great way to prepare and use a pivot table.

Preparing-Source-Data-For-Pivot-Table-No-Duplication-in-Headers

Common pitfalls to avoid when preparing source data

The source data must not contain any blank columns. This is an easy place. If you have an empty column in the source data, you will not be able to create a pivot table. This will show an error as shown below.

Preparing-Source-Data-For-Pivot-Table-Error

• Source data must not contain spaces / rows. When you can successfully create a pivot table despite empty cells or rows, there are many side effects that can bite you later in the day.
  • For example, suppose you have an empty cell in the Sales column. If you create a pivot table using this data and place the sales area in the column area, it shows you the COUNT and not the SUM. Because Excel interprets the entire column to be text data (due to only one blank cell).

Preparing-Source-Data-For-Pivot-Table-Count-instead-of-Sum

• Apply relevant formats to cells in source data. For example, if you have dates (which are stored in Excel as serial numbers in the backend), implement one of the acceptable date formats. This will help you create a pivot table and use dates as criteria to summarize, group, and sort data.
  • If you have a few 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, choose the date field and see what happens. This will automatically put it in the value field. This is because your axis table does not know that these are dates. It interprets these as numbers.
• Identifying any column totals, row totals, aways, etc. as a part of the source data. Once you have an axle table, you can easily get these later.
• Always create an Excel table and then use it as the source of the pivot table. This is more part of a good practice, not a loss. Your pivot table will work fine with a source data that works well as an Excel table. The advantage with Excel tables is that it can accommodate extended data. If you add more rows to the data set, you may need to adjust the source data repeatedly. You can simply refresh the pivot table and it will automatically account for new rows added to the source data.

Examples of Bad Source Data Designs

Let’s have a look at some bad examples of source data designs.

Example 1 – Bad Source Data Design 

Preparing-Source-Data-For-Pivot-Table-Bad-Data-1.1a

This is a common way of retaining data because it is easy to follow and understand. There are two problems with this data arrangement:

• You assumed that the whole picture would be found. For example, you can see the sale 2924300 for Mid West in Quarter 1. But whether it is a single sale, or a number of sales. If you have each record available in a separate row, you can do a better analysis.
• If you go ahead and create a pivot table using it (which you can do), you will get different fields for different quarters. As shown below:

Example 2 – Bad Source Data Design 

Preparing-Source-Data-For-Pivot-Table-Bad-Data-2

This can be well received by data representation management and audience of PowerPoint presentations, but is not suitable for creating pivot tables.

Again, this is a type of summary that you can easily create using a pivot table. So even if you ultimately want such a look for your data, retain the source data in pivot ready format and create this view using the pivot table.

Example 3 – Bad Source Data Design 

Preparing-Source-Data-For-Pivot-Table-Bad-Data-3

This is again an output that can be easily obtained using a pivot table. But it cannot be used to create a pivot table.

The data set contains empty cells and quarters are spread as column headers. Also, this field is specified at the top, while it should be part of every record.

Converting Pivot Table Ready Source Data to a Bad Format Data

Occasionally, you may find a dataset that is unsuitable to use as the source data for a pivot table. In such a case, you may have no choice but to convert the data into a pivot friendly data format.

Here’s an example of bad data design:

Preparing-Source-Data-For-Pivot-Table-Bad-Data-1.1a

Now you can use Excel functions or pivot queries to convert this data into a format that can be used as the source data for pivot tables.

Let us see how these two methods work.

Method 1: Using Excel Formulas

Let us see how to use Excel functions to convert this data into pivot table ready format.

Create a unique column header for all categories in the original dataset. In this example, it would be Area, Quarter and Sales.
In the cell below the region header, use the following formula:

=INDEX($A$2:$A$5,ROUNDUP(ROWS($A$2:A2)/COUNTA($B$1:$E$1),0))

• Drag the formula down and it will repeat all fields.

Preparing-Source-Data-For-Pivot-Table-convert-Data-using-Formula-2a-1

• In the cell below the quarter header, use the following formula:

=INDEX($B$1:$E$1,ROUNDUP(MOD(ROWS($A$2:A2),COUNTA($B$1:$E$1)+0.1),0))

• Drag the formula down and it will repeat all the quarters.

Preparing-Source-Data-For-Pivot-Table-convert-Data-using-Formula-x

• In the header below the sale, use the following

=INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(H2,$B$1:$E$1,0))

• • Drag it down to get all values. This formula uses area and quarter data as a lookup value and returns the sales price from the original dataset.

Preparing-Source-Data-For-Pivot-Table-convert-Data-using-Formula-y

You can use this resulting data as the source data for Pivot Table.

Download Example File Here

Method 2 – Using Power Query

Power Query has a feature that can easily transform such data into a pivot ready data format.

If using Excel 2016 is considered, the Power Query features will be available in the Data tab in the Get & Transform group. If you are considering using Excel 2013 or earlier versions, you can use it as an add-in.

Again, you have the data formatted as shown below:

Preparing-Source-Data-For-Pivot-Table-Bad-Data

Here are the steps to convert source data to pivot table ready format:

• Step 1: Convert data into Excel table. Select the dataset and go to Insert -> Tables -> Table.

Preparing-Source-Data-For-Pivot-Table-insert-Table

• Step 2: In the Insert Table dialog box, make sure the correct range is selected and click OK. This will convert tabular data into Excel tables.

Preparing-Source-Data-For-Pivot-Table-create-Table-dialog-box

• Step 3: In Excel 2016, go to Data –> Get & Transform –> From Table.
• If you are using the Power Query addin in the first version, go from Power Query -> External Data -> Table.

Preparing-Source-Data-For-Pivot-Table-From-Table-Get-Transform

• Step 4: In the Query Editor, select the columns you want to unapprove. In this case, these are for four quarters. To select all columns, hold down the Shift key and then select the first column and then the last column.
• Step 5: Within the Query Editor, go to Transform -> Any Column -> Unpred Column. This will convert the data of the column to a format compatible with the pivot table.
• Step 6: Gives the generic name to the power query column. Change these names to the ones you want. In this case, change the attribute to quarter and value to sales.
• Step 7: In the Query Editor, go to File -> Close and Load. This will close the Power Query Editor dialog box and create a separate worksheet containing the data without columns.

Now that you know how to prepare source data for a pivot table, you are ready to excel in the world of pivot tables.

8 Steps to Prepare Excel Data for Pivot Tables

1. Give each column a unique title in your dataset. For example, specify “first name” and “last name” instead of using “name” for both.
2. Specify the range for each column, such as currency or date. To do this, highlight the entire column by clicking on the letter above the column. Then assign the appropriate range using the drop-down menu in the Number group on the Home tab. You can format your data by right-clicking the column and choosing the Form cell, then selecting the Number tab. You will be able to select your category and specify how you want the data.
3. Give each column a unique title in your dataset. For example, specify “first name” and “last name” instead of using “name” for both.
4. Specify the range for each column, such as currency or date. To do this, highlight the entire column by clicking on the letter above the column. Then assign the appropriate range using the drop-down menu in the Number group on the Home tab. You can format your data by right-clicking the column and choosing the Form cell, then selecting the Number tab. You will be able to select your category and specify what data you want to display.
5. Remove duplicate data. Repeated data will give incorrect results. Although some data will be replicated (for example, products in sales reports), you do not want duplicate entries for a single event.
6. Remove all filters from the data. Filters can be created within the pivot table. You can adjust the filter using the Sort and Filter command in the Edit group on the Home tab.
7. Ungroup any grouped cells. Wait until you have created your axis table for group data. Remove the cells grouped with the Ungroup command in the Outline group on the Data tab.
8. Your last step before creating your axis table should be to format your data as a table. Highlight all your data, and then choose Format as a table in the Styles group on the Home tab.

The key to getting reliable information with the Excel PivotTables tool is to properly clear and format your data. Follow the steps above to prepare Excel data for PivotTables and ensure that your results are accurate and error-free.