Site icon Professor Excel

Pivotable: How to Prepare Data for Creating Pivot Tables in Excel

pivot, pivotable, excel

Pivot Tables are one of the most helpful features in Excel. With Pivot Tables, you can easily evaluate data. Per drag-and-drop you arrange analysis layouts. Within seconds, you’ll see your results – without using any formulas. Usually the first obstacle comes up, when you try to create a Pivot Table. There are some rules to regard in order to create Pivot Tables and your data needs a certain structure. In this article we explore how to make your data ‘pivotable’.

In this article: 

Steps for creating Pivot Tables

Insert a Pivot Table with 3 steps: Select the data, click on ‘PivotTable’ on the ‘Insert’ ribbon and follow the steps in the new window.

In our previous article you’ve already learned about the purpose of Pivot Tables. We’ve also talked about the steps for setting up a Pivot Table in detail. To insert a Pivot Table in Excel, please follow these steps:

  1. Select the input data.
  2. Click on ‘Pivot Table’ on the ‘Insert’ ribbon.
  3. Follow the steps on the screen. After confirming with OK you can drag-and-drop the fields for arranging the Pivot Table.

Please refer to the previous article for more help on the steps. For now, we want to concentrate on the requirements of your data and how to make your data ‘pivotable’.

Requirements for Pivot Tables

Example: Change the original table structure into a ‘pivotable’ table

The data for your Pivot Tables must meet the following requirements:

  1. The most important criteria: Each column must have a title. The title is always the top row of your data. Blanks/ empty cells as column headings are not allowed.
  2. In earlier versions of Excel, each column heading could only appear once. Therefore, each column heading had to be unique. Newer version add a number in the end if a title is used several times. In order to avoid confusion, we recommend using unique column titles for each column.
  3. Your data should have a ‘database’ structure: Each column should have one criteria or value. If you use the ‘table format’ in Excel already, you shouldn’t have any problems.

Please take a look at the image on the right. The upper table is not formatted well for creating a Pivot Table. But the lower one is ‘pivotable’. It has 4 columns and each column has a single purpose or data item.

Example: Make an existing table ‘pivotable’ so that you can create a Pivot Table from it

Original data: This table we will transform into a structure which we can create a Pivot Table from.

Let’s assume you have the table above and you want to create a Pivot Table with it. Therefore, you have to change the structure.

Each row contains the values of one product. The columns C to F show the revenue per year and the column G to J the corresponding costs.

That means, you got 4 different types of data: The product name/ number, the type (revenue, costs), the year and the values.

Column structure in order to create a Pivot Table in Excel.

In order to create a Pivot Table, you need a structure as shown on the right hand side. Each column contains one data type. As you have 4 different data fields (product, type (revenue or cost), year and the values) you need 4 columns, one for each data type.

So let’s start filling the columns. This requires some manual steps as well as some formulas.

First step: Create the ‘Product’ column
Step 2: Copy down ‘Revenue’ and ‘Cost’ in the second column.
Step 3: Add the year in the third column. Add +1 for increasing the number of the year.
Step 4: Get the correct value for each combination of ‘Product’, ‘Type’ and ‘Year’ with a formula.

Method 2 – Add-in: Because the formulas are quite complicated, we’ve integrated a function in our Excel add-in ‘Professor Excel Tools’. It copies all values into one column underneath each other. Follow these steps:

Step 4: Alternatively use the Excel add-in ‘Professor Excel Tools’ for easily getting the values from the original table.
  1. Select the source data.
  2. Press copy on the ‘Professor Excel’ ribbon.
  3. Select the cell, in which you want to start pasting the data (in your case E11).
  4. Click on ‘Paste to Single Column’ on the ‘Professor Excel’ ribbon.
  5. Select your desired option. Our preferred pasting option is usually ‘Link to source’ because any changes of the original values will be regarded. Confirm by clicking on start.

This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

That’s it. Now you can insert a Pivot Table. The final result should look similar to the image on the right hand side.

The result: We started with the original data, transformed it into the structure on the left hand side and eventually inserted a Pivot Table.

Exit mobile version