The pivot table is one of Microsoft Excel’s most powerful \u2014 and intimidating \u2014 functions. Pivot tables can help you summarize and make sense of large data sets. However, they also have a reputation for being complicated.<\/p>\n<\/p>\n
The good news is that learning how to create a pivot table in Excel is much easier than you may believe.<\/p>\n
We\u2019re going to walk you through the process of creating a pivot table and show you just how simple it is. First, though, 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.<\/p>\n
What is a pivot table?<\/a> <\/p>\n <\/a><\/p>\n <\/a> <\/p>\n In other words, pivot tables extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it lets you group your data in different ways so you can draw helpful conclusions more easily.<\/p>\n The “pivot” part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table 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 reveal useful information.<\/p>\n<\/p>\n <\/a> <\/p>\n If you’re still feeling a bit confused about what pivot tables actually do, don’t worry. This is one of those technologies that are much easier to understand once you’ve seen it in action.<\/p>\n The purpose of pivot tables is to offer user-friendly ways to quickly summarize large amounts of data. They can be used to better understand, display, and analyze numerical data in detail.<\/p>\n With this information, you can help identify and answer unanticipated questions surrounding the data.<\/p>\n Here are seven hypothetical scenarios where a pivot table could be helpful.<\/p>\n Let\u2019s say you have a worksheet that contains monthly sales data for three different products \u2014 product 1, product 2, and product 3. You want to figure out which of the three has been generating the most revenue.<\/p>\n One way would be to look through the worksheet and manually add the corresponding sales figure to a running total every time product 1 appears. The same process can then be done for product 2, and product 3 until you have totals for all of them. Piece of cake, right?<\/p>\n Imagine, now, that your monthly sales worksheet has thousands upon thousands of rows. Manually sorting through each necessary piece of data could literally take a lifetime.<\/p>\n With pivot tables, you can automatically aggregate all of the sales figures for product 1, product 2, and product 3 \u2014 and calculate their respective sums \u2014 in less than a minute.<\/p>\n Image source<\/span><\/em><\/a><\/p>\n Pivot tables inherently show the totals of each row or column when created. That’s not the only figure you can automatically produce, however.<\/p>\n Let’s say you entered quarterly sales numbers for three separate products into an Excel sheet and turned this data into a pivot table. The pivot table automatically gives you three totals at the bottom of each column \u2014 having added up each product’s quarterly sales.<\/p>\n But what if you wanted to find the percentage<\/em> these product sales contributed to all company sales, rather than just those products’ sales totals?<\/p>\n With a pivot table, instead of just the column total, you can configure each column to give you the column’s percentage of all three column totals.<\/p>\n Let\u2019s say three products totaled $200,000 in sales. The first product made $45,000, you can edit a pivot table to instead say this product contributed 22.5% of all company sales.<\/p>\n To show product sales as percentages of total sales in a pivot table, simply right-click the cell carrying a sales total and select Show Values As<\/strong> > % of Grand Total<\/strong>.<\/p>\n Image source<\/span><\/em><\/a><\/p>\n In this scenario, you’ve just completed a blog redesign and had to update many URLs. Unfortunately, your blog reporting software didn’t handle the change well and split the “view” metrics for single posts between two different URLs.<\/p>\n In your spreadsheet, you now have two separate instances of each individual blog post. To get accurate data, you need to combine the view totals for each of these duplicates.<\/p>\n Image source<\/span><\/em><\/a><\/p>\n 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.<\/p>\n Voil\u00e0, the view metrics from those duplicate posts will be aggregated automatically.<\/p>\n Image source<\/span><\/em><\/a><\/p>\n Pivot tables are helpful for automatically calculating things that you can’t easily find in a basic Excel table. One of those things is counting rows that all have something in common.<\/p>\n For instance, let\u2019s say you have a list of employees in an Excel sheet. Next to the employees’ names are the respective departments they belong to. You can create a pivot table from this data that shows you each department’s name and the number of employees that belong to those departments.<\/p>\n The pivot table\u2019s automated functions effectively eliminate your task of sorting the Excel sheet by department name and counting each row manually.<\/p>\n Not every dataset you enter into Excel will populate every cell. If you’re waiting for new data to come in, you might have lots of empty cells that look confusing or need further explanation.<\/p>\n That’s where pivot tables come in.<\/p>\n Image source<\/span><\/em><\/a><\/p>\n \u00a0<\/p>\n You can easily customize a pivot table to fill empty cells with a default value, such as $0, or TBD (for “to be determined”). For large data tables, being able to tag these cells quickly is a valuable feature when many people are reviewing the same sheet.<\/p>\n To automatically format the empty cells of your pivot table, right-click your table and click PivotTable Options.<\/strong><\/p>\n In the window that appears, check the box labeled Empty Cells As and enter what you’d like displayed when a cell has no other value.<\/p>\n Image source<\/span><\/em><\/a><\/p>\n \u00a0<\/p>\n <\/a> <\/p>\n Enter your data into a range of rows and columns.<\/a> 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.<\/p>\n <\/a> <\/p>\n Every pivot table in Excel starts with a basic Excel table, where all your data is housed. To create this table, simply enter your values into a specific set of rows and columns. Use the topmost row or the topmost column to categorize your values by what they represent.<\/p>\n For example, to create an Excel table of blog post performance data, you might have:<\/p>\n A column listing each “Top Pages.” We’ll be using that example in the steps that follow.<\/p>\n<\/p>\n <\/a> <\/p>\n Once you\u2019ve entered all your data into your Excel sheet, you\u2019ll want to sort your data by attribute. This will make your information easier to manage once it becomes a pivot table.<\/p>\n To sort your data, click the Data<\/strong> tab in the top navigation bar and select the Sort<\/strong> icon underneath it. In the window that appears, you can sort your data by any column you want and in any order.<\/p>\n For example, to sort your Excel sheet by “Views to Date,” select this column title under Column<\/strong> and then select whether you want to order your posts from smallest to largest, or from largest to smallest.<\/p>\n Select OK<\/strong> on the bottom-right of the Sort window.<\/p>\n Now, you\u2019ve successfully reordered each row of your Excel sheet by the number of views each blog post has received.<\/p>\n<\/p>\n <\/a> <\/p>\n Once you’ve entered and sorted your data, highlight the cells you’d like to summarize in a pivot table. Click Insert<\/strong> along the top navigation, and select the PivotTable<\/strong> icon.<\/p>\n You can also click anywhere in your worksheet, select “PivotTable,” and manually enter the range of cells you’d like included in the PivotTable.<\/p>\n This opens an options box. Here you can select whether or not to launch this pivot table in a new worksheet or keep it in the existing worksheet, in addition to setting your cell range.<\/p>\n If you open a new sheet, you can navigate to and away from it at the bottom of your Excel workbook. Once you’ve chosen, click OK.<\/strong><\/p>\n Alternatively, you can highlight your cells, select Recommended PivotTables to the right of the PivotTable icon, and open a pivot table with pre-set suggestions for how to organize each row and column.<\/p>\n<\/p>\n Note: If using an earlier version of Excel, “PivotTables” may be under Tables or Data along the top navigation, rather than “Insert.” In Google Sheets, you can create pivot tables from the Data dropdown along the top navigation.<\/em><\/span><\/p>\n <\/a> <\/p>\n After you’ve completed Step 3, Excel will create a blank pivot table for you.<\/p>\n Your next step is to drag and drop a field \u2014 labeled according to the names of the columns in your spreadsheet \u2014 into the Row Labels<\/strong> area. This will determine what unique identifier the pivot table will organize your data by.<\/p>\n 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 \u201cTop pages\u201d field to the “Row Labels” area.<\/p>\n<\/p>\n Note: Your pivot table may look different depending on which version of Excel you’re working with. However, the general principles remain the same.<\/em><\/span><\/p>\n <\/a> <\/p>\n Once you’ve established how you’re going to organize your data, your next step is to add in some values by dragging a field into the Values<\/strong> area.<\/p>\n 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.<\/p>\n<\/p>\n <\/a> <\/p>\n 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.<\/p>\n On a Mac, you can do this by clicking on the small i<\/em><\/strong> next to a value in the “Values” area, selecting the option you want, and clicking “OK.” Once you\u2019ve made your selection, your pivot table will be updated accordingly.<\/p>\n 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 to access the menu.<\/p>\n<\/p>\n When you\u2019ve categorized your data to your liking, save your work and use it as you please.<\/p>\n <\/a> <\/p>\n From managing money to keeping tabs on your marketing effort, pivot tables can help you keep track of important data. The possibilities are endless!<\/p>\n See three pivot table examples below to keep you inspired.<\/p>\n Image source<\/em><\/a><\/span><\/p>\n If you\u2019re in HR, running a business, or leading a small team, managing employees\u2019 vacations is essential. This pivot allows you to seamlessly track this data.<\/p>\n All you need to do is import your employee\u2019s identification data along with the following data:<\/p>\n Sick time. From there, you can sort your pivot table by any of these categories.<\/p>\n Image source<\/span><\/em><\/a><\/p>\n Whether you\u2019re running a project or just managing your own money, pivot tables are an excellent tool for tracking spend.<\/p>\n The simplest budget just requires the following categories:<\/p>\n Date of transaction With this information, you can see your biggest expenses and brainstorm ways to save.<\/p>\n
What are pivot tables used for?<\/a>
How to Create a Pivot Table<\/a>
Pivot Table Examples<\/a><\/p>\nWhat are pivot tables used for?<\/h2>\n
1. Comparing Sales Totals of Different Products<\/h3>\n
2. Showing Product Sales as Percentages of Total Sales<\/h3>\n
3. Combining Duplicate Data<\/h3>\n
4. Getting an Employee Headcount for Separate Departments<\/h3>\n
5. Adding Default Values to Empty Cells<\/h3>\n
How to Create a Pivot Table<\/h2>\n
\n Sort your data by a specific attribute.<\/a>
\n Highlight your cells to create your pivot table.<\/a>
\n Drag and drop a field into the “Row Labels” area.<\/a>
\n Drag and drop a field into the “Values” area.<\/a>
\n Fine-tune your calculations.<\/a> <\/p>\nStep 1. Enter your data into a range of rows and columns.<\/h3>\n
\n A column listing each URL’s “Clicks.”
\n A column listing each post’s “Impressions.” <\/p>\nStep 2. Sort your data by a specific attribute.<\/h3>\n
Step 3. Highlight your cells to create your pivot table.<\/h3>\n
Step 4. Drag and drop a field into the “Row Labels” area.<\/h3>\n
Step 5. Drag and drop a field into the “Values” area.<\/h3>\n
Step 6. Fine-tune your calculations.<\/h3>\n
Pivot Table Examples<\/h2>\n
1. Creating a PTO Summary and Tracker<\/h3>\n
\n Hours of PTO.
\n Company holidays.
\n Overtime hours.
\n Employee\u2019s regular number of hours. <\/p>\n2. Building a Budget<\/h3>\n
\n Withdrawal\/Expenses
\n Deposit\/Income
\n Description
\n Any overarching categories (like paid ads or contractor fees) <\/p>\n3. Tracking Your Campaign Performance<\/h3>\n