Course sections

Introduction to Microsoft Excel, Lecture 8

Concept of Pivot Table

Pivot Table in Excel

A pivot table is a summary of your data, packaged in a chart that lets you report on and explore trends based on your information. Pivot tables are particularly useful if you have long rows or columns that hold values you need to track the sums of and easily compare to one another.

More specifically, it lets you group your data together in different ways so you can draw helpful conclusions more easily. 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 reveal useful information from it.

How to Use Pivot Tables

Here are seven hypothetical scenarios where you’d want to use a pivot table.

  1. Compare 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. 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.
  1. Show product sales as percentages of total sales. 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 table would automatically give you three totals at the bottom of each column. But what if you wanted to find the percentage these product sales contributed of all company sales, rather than just those products’ sales totals? With a pivot table, you can configure each column to give you the column’s percentage of all three column totals, instead of just the column total.
  1. Combine 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.

  1. Get an employee head count for separate departments. 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.

If you have a list of employees in an Excel sheet, for instance, and 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 name and the number of employees that belong to those departments. The pivot table effectively eliminates your task of sorting the Excel sheet by department name and counting each row manually.

  1. Add default values to empty cells. Not every dataset you enter into Excel will populate every cell. If you’re waiting for new data to come in before entering it into Excel, you might have lots of empty cells that look confusing or need further explaining when showing this data to your manager. That’s where pivot tables come in.

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 tables of data, being able to tag these cells quickly is a useful feature when many people are reviewing the same sheet.

How to Create a Pivot Table

  1. Enter your data into a range of rows and columns.
  2. Sort your data by a specific attribute.
  3. Highlight your cells to create your pivot table.
  4. Drag and drop a field into the “Row Labels” area.
  5. Drag and drop a field into the “Values” area.
  6. Fine-tune your calculations.

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.

  1. Enter your data into a range of rows and columns. 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. For example, to create an Excel table of blog post performance data, you might have a column listing each “URL,” a column listing each URL’s “Post Title,” a column listing each post’s “Views to Date,” and so on. (We’ll be using that example in the steps that follow.)
  1. Sort your data by a specific attribute.

When you have all the data you want entered into your Excel sheet, you’ll want to sort this data in some way so it’s easier to manage once you turn it into a pivot table.

  1. Highlight your cells to create your pivot table.

Once you’ve entered data into your Excel worksheet, and sorted it to your liking, highlight the cells you’d like to summarize in a pivot table.

  1. 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.

  1. 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.

  1. 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.

 

WhatsApp chat