One of Excel’s most valuable and intimidating functions is the pivot table. Useful because it facilitates the summarization and interpretation of massive data volumes. Intimidating since you aren’t an Excel expert, pivot tables have a history of getting a bad rap for being difficult to use. This blog will discuss how to make a pivot table step by step.
But first, we need to understand what a pivot table is in Excel.
What’s a Pivot Table?
The pivot table provides a data summary shown in a chart that can be used for reporting and trend analysis. When you have a lot of numbers in a long row or column and want to add them up and compare them, a pivot table is a great tool to employ.
Pivot tables help you understand the endless stream of numbers filling your screen. In addition, it allows you to organise your data to facilitate the discovery of functional patterns.
The name “pivot table” comes from the ability to change the orientation of the data shown in the table. When you create a pivot, you don’t add, remove, or otherwise alter your data in any way. The truth is that all you’re doing is rearranging the data to make it easier to find out what you’re looking for.
Uses of Pivot Table
If you still need to figure out what pivot tables are and how they work, don’t worry. Once you see this technology in action, you will better grasp how it works.
Pivot tables are designed to simplify summarising massive datasets for the benefit of users. They could help people understand, present, and analyse numerical data. They could also help people find and solve problems that might not have been expected.
You Must Watch: A Beginner’s Guide to using SPSS for Data Analysis
Some possible pivot table examples are discussed below
Finding similarities and differences in the sales of various items
Let’s pretend you have a spreadsheet tracking the monthly sales of three goods (1, 2, and 3), and you’re trying to determine which one has been the most profitable. You could, of course, manually count the number of times Product 1 shows up in the spreadsheet and add the sales numbers for each time to a running total. Repeat this process with Products 2 and 3 until you’ve tallied all the products. Simple, right?
Let’s say your monthly sales sheet includes hundreds of rows. It would take an eternity to go through them all manually. You can use a pivot table in under a minute to automatically add sales numbers for all the products.
Data merging to eliminate duplicates
In this case, you have just finished a blog makeover and updated several URLs. As a result of your blog reporting software’s poor handling, the “view” statistics for individual posts were broken up between two URLs. Thus, for each blog post, you have two entries on your spreadsheet. You need to add how often these duplicates have been seen to get an accurate picture of how things are going.
To do so, you may use a pivot table. You may save time by summarising your data (using a pivot table) according to the blog post’s title. At this point, the view metrics for those duplicates will be added together automatically, so you won’t have to find and add them one by one.
Counting the number of workers in various divisions
When you need to automatically calculate numbers that aren’t easy to find in a regular Excel table, pivot tables can help. Keeping track of the number of rows that share a characteristic is another pivot table example.
For example, when you have a list of workers on an Excel sheet and, next to their names, you have the departments to which they belong, you can make a pivot table that lists the terms of the departments and the total number of employees in each. Because of the pivot table, you no longer have to count each item in Excel by hand or put the spreadsheet in order by department name.
Steps to create a pivot table in Excel:
- Put your information into the designated rows and columns.
It begins with a regular Excel table, where all your data will be stored, and grows into a pivot table. To get this table, fill in the appropriate rows and columns with your data. Use the top row or column to sort your data from most significant to least. A typical Excel table for tracking blog post performance would include rows for the “Top Pages,” “Clicks,” “Perceptions,” and so on for each article and URL.
- Arrange your information according to a specified criterion.
Once you’ve gathered all the necessary information, add it to an Excel sheet and categorise it to make it easier to manage in a pivot table. To put your information in a particular order, click the Sort button on the Data tab of the main toolbar. You’ll see an option to put your data in any order you want to be based on any column in the box that comes up. Select the title under a column, such as “Views to Date,” and then decide whether you want to organise your postings from smallest to most significant or vice versa. If you’re going to reorganise the rows of the Excel sheet based on how many times each blog has been seen, you can do so by clicking OK in the bottom-right corner of the Sort box.
- Select the cells you want to use in your pivot table.
After putting the data into an Excel worksheet and sorting it the way you want, you can choose the cells you want to use to make a summary pivot table. Click the PivotTable icon from the Insert menu. Alternatively, you may right-click anywhere on the worksheet, choose “PivotTable,” and manually input the number of cells you want to have in the PivotTable.
It will bring up a dialogue box where you can choose the cell range for which the pivot table will be created, as well as whether the table will be opened in a separate worksheet or remain in the current one. Use the tabs at the very end of your Excel document to go to or from a newly opened sheet. After making a selection, confirm it by selecting OK.
You can also choose your cells and click the Recommended PivotTables button to the right of a PivotTable icon to create a pivot table with ideas for arranging each row and column.
- Put a column into the Row Labels box.
Assuming Step 3 is performed, Excel will automatically generate a new, empty pivot table. Once you’ve done that, go to the Row Labels box and place a field, naming it like the column headings in your spreadsheet. Choose the label (blog post headline, product name, etc.) that will be used to categorise your data in the pivot table.
In the case of blog posts, you could sort all of the data by title tag. Drag the “Top pages” box into the “Row Labels” section.
- Select a field and drop it into the “Values” box.
After deciding on a method for categorising your records, you can start adding numbers by dropping a field into the Values section.
Using our blog data as an example again, let’s say you want a list of views sorted by post title. To do this, move the “Views” field to the Values section.
- Make sure your numbers are correct.
By default, the calculation will be the sum of a set of values, but you can alter this to get an alternative result, such as the average, the maximum, or the minimum.
Select the value field settings from the drop-down menu that appears when you click the flipside triangle beside your value on a PC.
Save your data after you’ve arranged it as you want, then utilise it in any way you choose.
The steps mentioned above will help you understand how to make a pivot table. In conclusion, you’ve completed the first step toward mastering Excel’s pivot table functionality. With this knowledge, you’ll be able to use a pivot table to get the necessary information and locate the answers you need.
