

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:
Contents
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:
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’.
Example: Change the original table structure into a ‘pivotable’ table
The data for your Pivot Tables must meet the following requirements:
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.
Original data: This table we will transform into a structure which we can create a Pivot Table from.
You got the table on the right hand side 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
Product: You only got 3 products, so it’s not too difficult preparing the first 3 rows. Just copy and paste the cell B5 to B7 from the original table.
But you need the rows several times: In total, you need each product for every year (2012, 2013, 2014, 2015) two times (revenue and costs). Because you got 3 products, you need 24 (2 types x 4 years x 3 products) rows plus the header row.
You got two options: repeat this step 8 times or think of a shortcut. In our small example manually repeating this copy-and-paste procedure is possible. But what if you got more than 3 years or more than 2 types of data?
So the easiest way is using this formula in cell B14: =B11 . This formula you can easily copy down and the 3 products will keep on repeating.
Step 2: Copy down ‘Revenue’ and ‘Cost’ in the second column.
Type: With the column ‘Type’ you classify if the value is revenue or cost. So you need 12 rows saying ‘Revenue’ and 12 rows saying ‘Costs.
Why 12 rows? You got 3 products, and each products needs 4 rows for the years 2012, 2013, 2014 and 2015. So that makes 12 rows (3 products x 4 years).
Probably the fastest way: Write “Revenue” in cell C11 and copy it down.
Do the same with “Costs”, starting in row 23 (as you start in row 11 + 11 rows of revenues) and copy it down to the last row as well.
Step 3: Add the year in the third column. Add +1 for increasing the number of the year.
Year: The first 3 rows should start with year 2012. The following 3 rows should say 2013 and so on. Therefore we write 2012 into the cells D11 to D13.
In D14 we link to D11 but add 1. Copy and paste this formula down until the end of the revenue block. Then repeat the same for the cost (or copy the cells above).
Step 4: Get the correct value for each combination of ‘Product’, ‘Type’ and ‘Year’ with a formula.
Method 1 – Formula: For each combination of product, type and year you need to get the correct value from the table above. Therefore you can use a two-dimensional lookup as shown on the picture on the right hand side.
The formula is basically a VLOOKUP, searching for the product (here: ‘Product 1’ in cell B11) in the column B (given by the range $B$4:$F$7).
Instead of using a fixed value for the return column, you search for the current year with the MATCH formula in the original cell range.
The last 0 indicates, that we are searching for the exact match.
The complete formula for revenues: =VLOOKUP(B11,$B$4:$F$7,MATCH(D11,$B$4:$F$4,0),FALSE)
Adapt the formula for the costs. The underlying structure is the same: =VLOOKUP(B23,$B$4:$J$7,MATCH(D23,$G$4:$J$4,0)+5,FALSE) The idea is to change the return range to the right hand side.
Copy these two formulas down for revenues and costs.
Step 4: Alternatively use the Excel add-in ‘Professor Excel Tools’ for easily getting the values from the original table.
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:
This function is included in our Excel Add-In 'Professor Excel Tools'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook 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.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example