The pivot table is a powerful tool in Microsoft Excel, although it can seem a bit overwhelming at first. It’s used to organize and understand big sets of data. You might have heard that pivot tables are complicated, but don’t worry. The good news is, that learning to create a pivot table in Excel is much easier than you might think.
We’ll guide you through making a pivot table and demonstrate how easy it is. But before we do that, let’s take a step back and make sure you know exactly what a pivot table is and why it’s useful.
What Is A Pivot Table?
A pivot table is a summary of your information or data that’s put into a chart. This chart helps you talk about trends and explore your data. Pivot tables are super handy if you have a lot of stuff in rows and columns, and you want to add up those numbers and compare them easily.
Pivot tables take all those numbers on your screen that might look confusing and turn them into something that makes sense. It helps you put your data into different groups, so you can figure out stuff more easily.
The “pivot” part of the pivot table’s name comes from the idea that you can spin the data around (like turning a door on its hinges) to look at it in a new way. Just to be clear, making a pivot table doesn’t change your data, like adding or taking away stuff. It’s more like rearranging the data to show you helpful things.
What Are Pivot Tables Used For?
If you’re still a bit confused about what pivot tables do, don’t worry. This is one of those things that’s easier to understand once you see it working.
Pivot tables are like helpers that make big data summaries easy. They’re great when you have lots of stuff to keep track of, like numbers in rows and columns. They help you understand and show off the numbers.
Let’s look at some examples of how they can also help:
Comparing Sales of Different Products.
Imagine you’re looking at sales data for three products. You want to know which one makes the most money. You could add up the sales for each product by hand, but that’s a lot of work. With a pivot table, you can do it super-fast.
Showing Product Sales as Parts of the Whole.
If you have sales numbers for different products, you might want to see how each product’s sales compare to all the sales. Pivot tables can show you the percentage of each product’s sales compared to everything.
Putting Together Similar Data
Sometimes you have the same info in different places. Pivot tables can help you add them up, so you don’t have to do it manually.
Counting Employees in Departments
If you have a list of employees and their departments, a pivot table can count how many people are in each department.
Filling Empty Spaces
Not all cells in your data have information. Pivot tables can fill those empty spots with default values, making it easier to understand what’s going on.
Pivot tables are like data wizards that make your life easier. They can help with all these things and more. Just think of them as tools that turn messy data into neat and useful info.
How To Create A Pivot Table
Now that you have a better idea of what pivot tables can do, let’s dive into how you can create one.
Step 1: Set Up Your Data
Start by putting your data into an Excel table. This means organizing your information into rows and columns. Use the first row or first column to describe what your data represents. For instance, if you’re tracking blog post-performance, you might have columns for “Top Pages,” “Clicks,” and “Impressions.”
Step 2: Sort Your Data
After you’ve entered your data, it’s a good idea to sort it. This makes it easier to work with when creating your pivot table. To sort, click on the “Data” tab at the top and then click on the “Sort” icon. You can choose which column to sort by and whether to sort in ascending or descending order.
Step 3: Highlight Cells and Create Pivot Table
Now, select the cells you want to include in your pivot table. Go to the “Insert” tab and click on the “PivotTable” icon. Alternatively, you can click anywhere in your data and then choose “PivotTable.” A box will appear where you can decide if you want the pivot table on a new sheet or in the existing one.
Step 4: Arrange Rows
Once your pivot table is set up, drag and drop a field (a column name) into the “Row Labels” area. This will determine how your data will be grouped. For example, if you’re organizing by post titles, drag the “Top Pages” field into this area.
Step 5: Add Values
Next, drag a field into the “Values” area. This is where your data will be summarized. For instance, if you want to see the sum of views for each post, drag the “Views” field into the “Values” area.
Step 6: Customize Calculations
By default, the pivot table will show sums. But you can change this to show averages, maximums, or minimums, depending on what you want. Click on the small “i” icon (on Mac) or the upside-down triangle (on PC) next to a value in the “Values” area. Select your preferred calculation method, and the pivot table will update accordingly.
Once you’re satisfied with how your data is organized and summarized, save your work. You can now use your pivot table to analyze your data in a more meaningful way. Make sure to remember that the appearance of your pivot table might vary depending on the version of Excel you’re using, but the basic steps remain the same.
How To Delete a Pivot Table
- If you don’t need a specific summary report anymore, you can remove it in a few different ways.
- If your report is on a separate sheet, you can just delete that sheet.
- If your report is on the same sheet as other stuff, you can select the whole Pivot Table by clicking and holding your mouse, and then pressing the Delete key on your keyboard.
- You can also click anywhere in the Pivot Table you want to get rid of, go to the “Analyse” tab (or “Options” tab in older Excel versions), find the “Actions” section, click the small arrow under the “Select” button, pick “Entire PivotTable,” and then hit the Delete key.
- Deleting an Excel Pivot Table
Just remember, if there’s a chart connected to your table, deleting the Pivot Table will turn it into a regular chart that you can’t pivot or update anymore.
CHECK THESE OUT:
