Microsoft Excel’s pivot table is one of its most powerful and intimidating functions. You can use pivot tables to summarize and make sense out of large datasets. They are also criticized for being complex.
It is easier than you think to learn how to create pivot tables in Excel.
What is a pivoting table?
A pivot table summarizes your data in a chart. This allows you to report and analyze trends using your information. The pivot table is particularly useful when you have a lot of rows or columns with values that you want to compare and track.
In other words, pivot tables give meaning to the seemingly endless number of numbers that appear on your screen. It allows you to group your data differently so that you can make more informed decisions.
The pivot part of the pivot table is derived from the fact that the data can be rotated (or swung) to see it from a new perspective.
When you pivot, you are not changing, adding, or subtracting data. You’re merely reorganizing your data to reveal more useful information.
Video Tutorial: Create Pivot Tables with Excel
It’s no secret that pivot tables are complex and intimidating, especially when you create them for the first time. This video tutorial will teach you how to create pivot tables in Excel in six simple steps.
You can quickly become an expert at creating pivot tables with Excel by immersing yourself in the material. Start with the Excel templates below.
What is the purpose of pivot tables?
Don’t worry if you are still a little confused by what pivot tables do. It’s one of those technologies you can understand much better once you see it in action.
Pivot tables are designed to provide a user-friendly way to summarize large quantities of data quickly. You can use them to display and analyze numerical data.
This information can be used to identify and answer questions that you may not have anticipated.
Here are five hypothetical situations where a pivotable could be useful.
Comparing sales totals for different products
Say you have a spreadsheet that has monthly sales figures for three products — products 1, 2, and 3. You’re trying to determine which product has generated the most sales.
You could manually add the sales figures to the running total each time product 1 is listed on the worksheet.
You can repeat the process for products 2 and 3 until you reach a total for each. It’s easy.
Imagine that your sales sheet has thousands and thousands of rows. It could take an eternity to sort through all the data manually.
You can calculate the sum of all sales for products 1, 2, and 3 in under a minute using pivot tables.
Displaying product sales as a percentage of total sales
When created, pivot tables show the totals for each column or row. You can also automatically generate other figures.
Imagine you have entered the quarterly sales figures for three different products in an Excel sheet. You then converted this data to a pivotable. The pivot table will automatically display three totals in each column after adding up the quarterly sales of each product.
What if you were interested in the percentage that these products contributed to total company sales rather than only their sales?
Instead of only displaying the total of each column, you can also configure the pivot table to show the percentage of the total of the three columns for each column.
Let’s say three products generated $200,000, and the first product was $45,000. You can edit the pivot table so that it says this product contributed 22,5% of company sales.
Click the cell that contains a total of sales and choose Show Values as > Grand Total.
Combining duplicate data
In this case, you have just finished a blog redesign. You had to update a lot of URLs. Unfortunately, the blog reporting software did not handle the change very well. It splits the “views” metrics of single posts across two URLs.
You now have two instances of the same blog post in your spreadsheet. You need to add the views for both duplicates in order to get accurate data.
You can summarise your data by the title of blog posts instead of manually searching for and combining all the metrics.
Voila! The view metrics for those duplicate posts are automatically aggregated.
How to get the employee headcount of separate departments
The pivot table is useful for calculating things you cannot easily find on a standard Excel table. One of these things is to count rows that have something in common.
Let’s say, for example, you have a spreadsheet with a list of employees. The departments of the employees are listed next to their names.
This data can be used to create a pivot chart that displays the name of each department and how many employees are in that department.
Automated functions in the pivot table eliminate the need to sort and count rows in Excel manually.
Add Default Values To Empty Cells
Excel does not always fill every cell with data. You might find that you have a lot of blank cells while you wait for new data.
This is where pivot tables can be useful.
It is easy to customize a pivotable so that empty cells are filled with default values, such as TBD or $0 (for “to determine”). When working with large data tables and multiple users, the ability to quickly tag cells is an important feature.
Click on PivotTable options and right-click the table to format empty cells automatically automatically.
Check the “Show empty cells” box and type in what you want to be displayed when the cell is blank.
How to create a pivot table
Let’s dive into the details of creating a pivot table now that you know what they are.
Toyin Odobo is a Data Analyst and explained creating a pivotable.
Excel provides a “Recommended Pivot Table function” that will help you analyze your data. You can choose from the recommended layouts and modify them if needed.
This tool has limitations, as it may not always suggest the best way to arrange your data.
As a data expert, I recommend that you keep in mind this and consider the option of creating a pivotable on your own.
Here are some steps to help you create your pivot table.
Excel pivot tables begin with a simple Excel table where you store all of your data. Enter your values in a series of columns and rows, as shown below, to create this table.
I’ve got a list with the names of people, along with their educational level and marital status. A pivot table allows me to find several different pieces of information. For example, I could see how many people have master’s degrees and are married.
You’ll need to set a goal at this stage. What information do you hope to gain by manipulating the data? What do you want to know? You can use this to design your pivot table.
Insert your pivot table. Insert your pivot table.
The easiest part of inserting a pivot table is to slide it into place. You’ll need to:
Note: If you are using an older version of Excel, the “PivotTables,” which is usually located under Tables and Data in the top navigation bar, may appear instead as “Insert.”
You will see a dialog box that confirms the data set you selected and gives you the option of importing data from an external resource (ignore it for the moment). You will be asked where you would like to place the pivot table. I suggest using a fresh worksheet.
You won’t need to edit any options unless, for example, you want to move your pivot table or change the table that is selected.
Click OK after you have double-checked all the information.
The result will be empty.
It’s here that it can get a bit confusing. I would stop at this point as a novice because I felt so lost. Next, we’ll edit the pivot table fields so that a rendered table appears.
Edit your pivot table fields.
Now that you have the “skeleton,” it’s time for it to be fleshed out. You will be able to edit the pivot table fields after clicking OK.
It can be not very clear to view this for the first time.
This page allows you to take any table field (for example, First name, Last name, Education, and Marriage Status) and convert it into one of the following four fields: