PivotTables in Excel – The Basics
PivotTable or pivot table. You may have heard of them…
In our opinion, they are one of the MOST powerful things you can learn in Excel. The great news is, they are pretty easy to get the hang of.
Before PivotTables were introduced into Excel, you had to perform a mind-boggling number of complex and often nested functions to achieve the simple summaries a pivot table kicks out. That’s part of the reason that the drag and drop data analysis that a PivotTable unlocks is so brilliant. It’s also no exaggeration to say that getting to grips with how the PivotTable function works, will change your professional capability and value.
Are they REALLY that good? Yes, keep reading.
In this article, we’ll show you the basics of how to get started.
***WARNING – WHEN USING PIVOTTABLE YOU MUST***
- Organise and clean your data. Any typos or any numbers that are displayed as text will likely mess up your PivotTable summaries and calculations. If you are getting errors, it means you probably didn’t clean your data properly.
- Give each column row a heading. Pivot tables work using the column headings so make sure each column has one.
- Use a table. You can run PivotTables from any dataset but it’s advisable to use a table. That way if you update the data in that table (add to it, perhaps), then you just need to refresh the PivotTable to see the update. If you’re not sure how to create a table, just click into your dataset and hit Ctl – T.
The best way to understand what PivotTables are all about is to create one and experiment with it. The following example creates a PivotTable showing the total sales values of the top 10 best-selling products in an Excel table containing just over 2,000 invoices.
Here is the original table of sales invoices—the screen is split to show the total rows of data. The Table Name box in the TableTools, Design contextual tab shows the table is named ‘SalesInvoices’:
It is possible to create a PivotTable based upon any range of cells, but the advantage of using an Excel table is that the PivotTable will adjust more automatically to include any new rows and columns of data added to the range.
The table contains seven columns:
ExtendedPrice represents the total sales value of each invoice line – the number of items sold multiplied by the unit price of each item. A single invoice can have several lines, one for each different product ordered, so you will see the same OrderID for several lines.
Just glancing at 2,156 lines won’t easily tell you which products are selling best, which is where a PivotTable comes in.
So we have our table of data and we want to pull some useful information and summaries from it. Enter the PivotTable…
Right, so you know how to create your pivot table. Now let’s start playing around with it.
Filtering by Top 10
One of the many useful things you can do in a pivot table is apply a filter to just look at certain parts of your data. In the image below we’ve right-clicked on ProductName and from Filter, we’ve chosen Top 10:
From the Top 10 Filter dialog box you can choose whether to display items from the top of the list or the bottom; how many items to display—and whether that figure should just be a number, a percentage of the total, or the number of items that add up to a minimum value—and which field to base the top or bottom calculation on:
In this case, you can just leave all the default values set to show the top 10 products by invoice total, so click OK. The Top 10 list displays:
The list would probably be clearer if sorted in descending order of values to give a kind of league table. To do this, again right-click any ProductName, then from Sort, choose More Sort Options.
In the Sort dialog box, choose to use descending order, and then click the drop-down arrow to choose Sum of ExtendedPrice before clicking OK:
The Top 10 sellers are now sorted in descending order by value:
If you want to look at the detailed records that add up to any of the totals shown in the PivotTable, double-click the total and Excel will insert a new sheet listing all the records that sub-total to the value in the PivotTable.
Just to finish it off, right-click any of the numbers and choose Number Format to select a more appropriate format:
So, that was how you get started with the PivotTable function. We performed a basic summary but a pivot table can do SO MUCH MORE than that. In this video we look at all four fields that the PivotTable function uses:
That’s all we have for now. We hope you found that helpful. The videos and examples in this article were taken from the Excel with Business Excel course.