ExcelZone's Five Minute Pivot Table Tips - Index

ExcelZone's series of Five Minute tips aims to develop your skills at using Excel pivot tables. If you want to try out each tip for yourself, Excel expert David Carter takes you through a worked example on a small sample database of sales invoices.

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

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

Reprinted from our sister site, AccountingWEB.co.uk

You may like these other stories...

Cybersecurity is no longer the domain of an organization's IT staff. It's moved to the boardroom, and in a big way. Accountants and financial managers may have been thinking it's just the province of the tech...
You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...

Already a member? log in here.

Upcoming CPE Webinars

Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
Sep 30
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Oct 21
Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience's communication style.
Oct 23
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.