What Are “Pivot Tables?”
Unfortunately, the name alone doesn’t really reveal how useful pivot tables are! So, what are pivot tables, and why are they useful? A pivot table in Excel is a separate instance of a table that you’ve already set up. The idea behind pivot tables is that you can select how the data is displayed without touching the “main table.” You can sort, tally up totals, and check highest sums in this separate instance without having to mess around with the actual data. This makes for a very useful tool for analyzing data on the fly.
Setting Up a Pivot Table
For this example, let’s take a look at some data from a pizza delivery service. This small chain has three deliverers: Bob, Gregory, and Sally. The table records each time they make a delivery, how much the delivery was, and how many items were in each delivery. It also lists which one of the three nearby towns the delivery was made to: Alderfield, Basthead, and Carringtown.
This is a very simple table, yet answering questions such as “Which deliverer delivered the most items?” and “Which town paid the most money?” are a little tricky to answer just by looking at it. Instead of counting on fingers or editing the table, we can use a pivot table here to find out information. To make a pivot table in Excel, we first highlight the entire table, and then we go to “Insert” and click on “Pivot Table.”
A window will appear. Essentially, all this is asking us for is a data range (which we already set) and where we’d like the table. For the sake of this example, we’ll make the table in a new sheet to separate it from the main table.
Using the Pivot Table
Basic Table
Now we have our table ready to go in a new sheet, but it’s not doing anything particularly exciting!
However, if we click on the table itself, some options will appear. This is where we can customize and experiment with the data fields so that we can better analyze the data we have. First of all, let’s give this pivot table a very easy question: “Who delivered the most items?” To answer this, we need only two pieces of data: the deliverer’s name and the amount of items they delivered. So, in the pivot table side bar we’ll select both “Deliverer Name” and “Items Delivered.”
If we look at the table again, we can see that it has neatly sorted itself into a table based on our input. We can see from it that Sandy had the roughest time getting her deliveries done!
Advanced Table
You may have noticed that at the bottom of the pivot table the options are four boxes: filters, columns, rows, and values. This is where we can customize where each field appears in the table. For now let’s look at each box bar the “filters” have created. When we clicked the buttons above, Excel put “Deliverer Name” in the rows box and “Items Delivered” in the values box. This then created the table we saw above.
We can manually customize where each value appears by clicking and dragging them to the corresponding box. For instance, let’s answer the question “How much did each town collectively pay for their deliveries, and who got the highest pay?” We need three pieces of data for this: the names, the towns, and the cost. We can set up this table by activating the names field and dragging it to the rows box, activating the town field and dragging it to the columns box, then activating the payment field and dragging it to the values box.
When we look at the table, we see the following.
From this we can easily see that the big spender was Basthead, and the big earner was Sandy. We can also see we made $334 total from all the deliveries.
Advanced Table with Filters
Now let’s try applying a filter. Filters give us a way of selecting which data the table presents, based on the variable we set in the filter category. Let’s set it up so we have a town-by-town breakdown of the data. We put the names in the rows, payment and items delivered into values, and towns into the filter box.
Now the table allows us to filter the data by each town, so we can see the finer details on how each town is doing.
Tailor-Made Tables
When you want to ask questions about your data, you don’t need to manually sift through it all! Pivot tables make analyzing your spreadsheets easier and can be customized on the fly to get important information from your data. Do you think pivot tables will help your productivity? Let us know below!
