The pivot table is one of Excel's most powerful -- and intimidating -- functions. Powerful, because it can help you summarize and make sense of large data sets. Intimidating, because you're not exactly an Excel expert, and pivot tables have always had a reputation for being complicated.
Download our complete guide to using Excel here for more step-by-step Excel tutorials.
The good news: Learning how to create a pivot table in Excel is much, much easier than you've likely been led to believe. But before we walk you through process of creating one, let's take a step back and make sure you understand exactly what a pivot table is, and why you might need to use one.
What Is a Pivot Table?
To quote Microsoft, a pivot table is a report that lets you, "summarize, analyze, explore, and present a summary" of your data. And it is particularly useful "when you have a long list of figures to sum, and aggregated data or subtotals would help you look at the data from different perspectives and compare figures of similar data."
In other words, a pivot table lets you extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it lets you group your data together in different ways so you can more easily make comparisons.
The "pivot" part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table in order to view it from a different perspective. To be clear, you're not adding to, subtracting from, or otherwise changing your data when you make a pivot. Instead, you're simply reorganizing the data so you can extract useful information from it.
Use Cases for Pivot Tables
If you are still feeling a bit confused about what a pivot table actually does, don't worry. This is one of those technologies that's much easier to understand once you've seen it in action. So, here are two hypothetical scenarios where you'd want to use a pivot table.
Scenario #1: Comparing Sales Totals of Different Products
Say you have a worksheet that contains monthly sales data for three different products -- product 1, product 2, and product 3 -- and you want to figure out which of the three has been bringing in the most bucks. You could, of course, look through the worksheet and manually add the corresponding sales figure to a running total every time product 1 appears. You could then do the same for product 2, and product 3, until you have totals for all of them. Piece of cake, right?
Now, imagine that monthly sales worksheet of yours has thousands and thousands of rows. Manually sorting through them all could take a lifetime. Using a pivot table, you can automatically aggregate all of the sales figures for product 1, product 2, and product 3 -- and calculate their respective sums -- in less than a minute.
Scenario #2: Combining Duplicate Data
In this scenario, you've just completed a blog redesign and had to update a bunch of URLs. Unfortunately, your blog reporting software didn't handle it very well, and ended up splitting the "view" metrics for single posts between two different URLs. So in your spreadsheet, you have two separate instances of each individual blog post. In order to get accurate data, you need to combine the view totals for each of these duplicates.
That's where the pivot table comes into play. Instead of having to manually search for and combine all the metrics from the duplicates, you can summarize your data (via pivot table) by blog post title, and voilà: the view metrics from those duplicate posts will be aggregated automatically.
How to Create Excel Pivot Tables
Now that you have a better sense of what pivot tables can be used for, let's get into the nitty-gritty of how to actually create one.
Step 1: Click a cell to create the table.
If you're using a Mac, click anywhere inside your data array, navigate to the Tables tab, and click New. (Alternatively, you can go to Insert > Table via the top navigation). Then, select the Summarize with PivotTable option from the Tables tab and click OK on the pop-up menu.
If you're using a PC, this process is a little bit simpler: Just click anywhere inside your data array and navigate to the Insert tab. Next, select the "PivotTable" option and click "OK" on the pop-up menu.
Step 2: Drag and drop a field into the "Row Labels" area.
After you've completed Step 1, Excel will create a blank pivot table for you. Your next step is to drag and drop a field -- labeled according to the names of the columns in your spreadsheet -- into the "Row Labels" area. This will determine what unique identifier -- blog post title, product name, and so on -- the pivot table will organize your data by.
For example, let's say you want to organize a bunch of blogging data by post title. To do that, you'd simply click and drag the “Title” field to the "Row Labels" area.
Note: Your pivot table may look different depending on which version of Excel you're working with. However, the general principles remain the same.
Step 3: Drag and drop a field into the "Values" area.
Once you've established what you're going to organize your data by, your next step is to add in some values by dragging a field into the "Values" area.
Sticking with the blogging data example, let's say you want to summarize blog post views by title. To do this, you'd simply drag the "Views" field into the Values area.
Step 4: Fine-tune your calculations.
The sum of a particular value will be calculated by default, but you can easily change this to something like average, maximum, or minimum depending on what you want to calculate.
On a Mac, you can do this by clicking on the small "i" next to a value in the "Values" area, selecting the option you want, and clicking "OK." Once you’ve made your selection, your pivot table will be updated accordingly.
If you're using a PC, you'll need to click on the small upside-down triangle next to your value and select "Value Field Settings" in order to access the menu.
Digging Deeper
You've now learned the basics of pivot table creation in Excel. But depending on what you need your pivot table for, you might not be done.
For example, you may notice that the data in your pivot table isn't sorted the way you'd like. If were the case, Excel's Sort function can help you out. Alternatively, you may need to incorporate data from another source into your reporting, in which case the VLOOKUP function could come in handy.
To take a deeper dive into the world of Excel and learn about its various functions, download our comprehensive guide, How to Use Excel.
Editor's Note: This post was originally published in December 2013 and has been updated for accuracy and comprehensiveness.
No comments:
Post a Comment