By Filtered Excel tipsThe ultimate crash course in Excel – part 33 Sep 2018 Please enable cookies in your browser to watch the videos.Note: we are currently experiencing difficulties with the videos in this article, but are working to resolve it ASAP. Thanks for your patience.Excel tips: Ultimate crash course seriesPart 1 – Copy, cut & paste in ExcelPart 2 – Conditional formatting in ExcelPart 3 – Pivot Tables in ExcelPart 4 – Mathematical Formulas in ExcelPart 5 – Excel formatting guide for a great looking spreadsheetPart 6 – Essential Excel formulas like vlookupPart 7 – Filtering your data in ExcelA 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:OrderIDOrderDateCompanyNameCountrySalespersonProductNameExtendedPrice ExtendedPrice represents the total sales value of each invoice line – the number of items sold multiplied by the 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.Let’s take a look at how we create and build a Pivot Table using our raw data fields.Filtering by top 10 Now right-click any ProductName and from Filter, choose Top10: 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 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 descending order, and then click the dropdown arrow to choose Sum of ExtendedPrice before clicking OK:The Top 10 sellers are now sorted in descending order by value:Drill down 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.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 PivotTables We’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 PivotTableWe’re just scratching the surface of what’s possible with a pivot table. Next, why not bring your data to life using a PivotChart, Slicer, or Timeline?In the next article, part 4 in the series, we’ll dive into using mathematical formulas in Excel, like SUM, absolute references, powers and more.AAT students and professional members can access a wide range of Excel resources and training. Filtered provides algorithmically personalised Excel courses written by subject matter expert.