Excel tips: Ultimate crash course series
- Part 1 – Copy, cut & paste in Excel
- Part 2 – Conditional formatting in Excel
- Part 3 – Pivot Tables in Excel
- Part 4 – Mathematical Formulas in Excel
- Part 5 – Excel formatting guide for a great looking spreadsheet
- Part 6 – Essential Excel formulas like vlookup
- Part 7 – Filtering your data in Excel
A pivot table is one of the quickest and most effective ways to drill down into large sets of data in Excel. Once you have mastered the basics of the PivotTable in Excel you’ll find a world of data analysis opened up to you. This lesson takes 15-20 minutes, but the knowledge you’ll gain could save you hours every week. (Worth noting: PivotTable™ is the trademarked term for Microsoft Excel’s pivot table function. We use both terms in this article.) 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. Below is the original table of sales invoices. The screen is split to show the total rows of data. The highlighted Table Name box in the top left-hand corner shows the table is named ‘SalesInvoices’:
It’s possible to create a PivotTable based upon any range of cells, but the advantage of using a table is that the PivotTable will adjust automatically to include any new rows and columns of data added to the range. The above table contains seven columns:
Filtering by top 10Now right-click any ProductName and from Filter, choose Top10:
Drill downIf 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. To finish off, right-click any of the numbers and choose Number Format to select a more appropriate format. In this case, you could format the numbers to show a currency:
Recommended PivotTablesWe’ve just seen how quickly we can set up a PivotTable from our source data, but there is an even quicker way using Recommended PivotTables. We can click in any cell in our source data and, from the Insert ribbon tab, Tables group, choose Recommended PivotTables. Excel presents a selection of PivotTables based on your set of data – you can choose the most suitable with a couple of clicks: In the following video, we are going to explore the four areas of the Pivot Table; Filters, Columns, Rows and Values.
The four different areas of a PivotTable
Filtered provides algorithmically personalised Excel courses written by subject matter expert.