ExcelZone's Five Minute Pivot Table Tips - Index

In the worked examples you will analyze the invoices to show sales by product, sales by customer, gross margins, margin percentage etc. The series is therefore relevant to sales and marketing people as well as accountants.

You don't have to work through the series in any particular order. But if you have never used pivot tables before, please do tips 1-3 first as they will get you started.

Tip 1 - First step: remove those subtotals quickly [2]
Tip 2 - How to Group totals by month [3]
Tip 3 - Create Page worksheets automatically [4]
Tip 4 - Drill down on your data via Hide/Show Detail [5]
Tip 5 - How to lay data totals side-by-side [6]
Tip 6 - Use Formulas to create a calculated field [7]
Tip 7 - Tips on formatting a pivot table [8]
Tip 8 - How to Group dates manually [9]
Tip 9 - When to use Max instead of Sum [10]
Tip 10 - Tips on calculating monthly totals [11]
Tip 11 - Use Top 10 to show best sellers, or maybe not [12]
Tip 12 - Sort Rows into the order you want [13]
Tip 13 - Use Text to Columns to enhance your data [14]
Tip 14 - Use Vlookup to add extra fields [15]
Tip 15 - Working with Pivot Charts [16]
Tip 16 - Exploring PivotChart options [17]
Tip 17 - Use Subtotals to create detailed reports [18]
Tip 18 - Date problem: 'Cannot group that selection' [19]
Tip 19 - Correct faulty dates with Text to Columns [20]
Tip 20 - Change the Range name to A:K if you intend to add more records [21]
Tip 21 - Use AutoFormat, or save as an Excel 4 worksheet [22]
Tip 22 - Use Format Painter to tidy up drill-down worksheets [23]
Tip 23 - Create group totals via Formulas-Calculated Item [24]
Tip 24 - How to analyse your sales by week (1) [25]
Tip 25 - How to analyse your sales by week (2) [26]
Tip 26 - How to analyse sales by financial year [27]
Tip 27 - How to analyse sales between two dates [28]
Tip 28 - Use Get External Data to refresh your reports automatically [29]
Tip 29 - If you use Refresh, make sure you set Advanced-AutoSort [30]
Tip 30 - Use =MONTH and =YEAR functions to summarise dates [31]
Tip 31 - Use a VLOOKUP table to add dates [32]

Next step - pivot table self-teach tutorials
The sample database contains just 27 records and each of these tips should only take you 5 minutes to work through. If you want to learn more about pivot tables, there is a set of full-scale tutorials which show you how to use pivot tables for particular applications (budgeting, sales analysis, cost analysis, etc). These use much larger data sets and will take you an hour or so to work through. See Want to learn about Excel pivot tables? Start here [33]

Reprinted from our sister site, AccountingWEB.co.uk [34]