How to Make Use of Pivot Table in Excel to Improve Your Productivity

If you’re organizing a large amount of data, spreadsheets are a great way to keep track of it. They’re easy to edit, can be backed up, and have automated functions that put physical calculators to shame! Sometimes, however, you need more than just a table; you need your data to answer important questions you may have about it. This is where a pivot table in Excel come into play.

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.

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.

pivot-table-base

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

pivot-table-button

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.

Basic Table

Now we have our table ready to go in a new sheet, but it’s not doing anything particularly exciting!

pivot-table-empty

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

pivot-table-fields-first

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!

pivot-table-results-first

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.

pivot-table-categories

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.

pivot-table-fields-second

When we look at the table, we see the following.

pivot-table-results-second

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.

pivot-table-fields-third

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.

pivot-table-results-third

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!

Leave a Reply

Yeah! You've decided to leave a comment. That's fantastic! Check out our comment policy here. Let's have a personal and meaningful conversation.