Want to learn about Excel pivot tables? Start here. By David Carter

Pivot tables come free with Microsoft Excel and are a must for anyone who wants to analyse their data. AccountingWEB has been spreading the word for a long time now, and we published our first self-teach tutorial on pivot tables as far back as 1999. There's now a full suite of these tutorials and they have been accessed over 100,000 times.

Why tutorials?
There are dozens of books out there which will explain to you how to create a pivot table. But my own experience is that no matter how many times someone explains something, you will never really understand a piece of software until you've sat down at the keyboard and gone through a worked example yourself.

This is what the tutorials are for. Each comes with sample data and step by step instructions telling you which buttons to press. And each - whether it's budgeting, sales analysis, costing, or whatever - is a real application developed by a user and myself when we were installing an accounts/ERP package.

Working your way through a real case such as the sales analysis tutorial will help you visualise how you might analyse sales in your own company, and how to calculate key numbers such as margin and margin percentage.

Where to start?
How much time do you have? Each tutorial illustrates a dozen or more pivot table features in one continuous session. Realistically, it will take you an hour or 90 minutes. You will need to concentrate, so close the curtains and take the phone off the hook.

If this is too much to digest at one go, or you simply don't have the time to spare, we also have the Five-minute tips series. This uses worked examples, but for just one feature at a time.

Click below to choose the learning schedule you want:

  • 1. Getting started - If you are not an accountant
  • 2. Getting started - If you are an accountant

    1. GETTING STARTED - IF YOU ARE NOT AN ACCOUNTANT

    1. If you want just to get a flavour of what pivot tables are about, start with Five minute pivot table tips and try the first tip.

    2. If you seriously want to learn but don't have an hour or more to spare, work your way through the complete series of Five minute pivot table tips whenever you have a moment. They cover most areas of the Sales Analysis tutorial, plus several others such as charting and the all-important vlookup function.

    The Five minute tips series takes a very simple database of just 27 sales invoice records and shows how to analyse them to show sales by product and by customer, margins, margin percentage, etc. You can work through them in any order, although you should do tips 1 to 3 first to get yourself started.

    3. If you have an hour or 90 minutes to spare for a complete tutorial, spend it working your way through the Sales Analysis with Pivot Tables tutorial. This is similar to the 5-minute tips but uses a much larger (and therefore more realistic) data set of 4200 records.

    In this exercise you import into Excel several thousand sales invoice item records taken from an accounting/ERP package. Then use pivot tables to analyse sales and gross profit any way you want - sales by customer by month, sales by product, sales by sales executive, gross profit by sales exec, rank your top selling products by value, etc etc.

    2. GETTING STARTED - IF YOU ARE AN ACCOUNTANT

    1. If you want just to get a flavour of what pivot tables are about, start with Five minute pivot table tips and try the first tip.

    2. If you seriously want to learn but don't have an hour or more to spare, work your way through the complete series of Five minute pivot table tips whenever you have a moment. They cover most areas of the Sales Analysis tutorial, plus several others such as charting and the all-important vlookup function.

    The five-minute tips series takes a very simple database of just 27 sales invoice records and shows how to analyse them to show sales by product and by customer, margins, margin percentage, etc. You can work through them in any order, although you should do tips 1 to 3 first to get yourself started.

    3. If you have an hour to spare to work through a complete tutorial the simplest one to start with is Budgeting with Pivot Tables

    Set up monthly budgets for several departments within a company, then use a pivot table to calculate the overall company total budget. "Drill down" on the monthly figures to check doubtful balances. Learn how to revise individual departmental budgets, then use the Refresh Data command to recalculate the overall company totals.

    4.(a) After this you can choose. Either you can complete the budgeting exercise by going to Budgeting with Pivot Tables, part 2.

    This follows on from the first Budgeting tutorial. Add actuals to the budgets each month as they occur, then use pivot tables to produce monthly profitability reports comparing Actual versus Budget, and Variance.

    4.(b) Or, if you want to export balances from an accounts package and create financial reports, you can skip Budgeting Part 2 and go to Turn a Trial Balance into a P&L with a pivot table

    You have exported a Trial Balance report out of your accounts package and into Excel. In this tutorial, you are going to apply pivot tables and Excel's Vlookup function to the Trial Balance data in order to create a Profit and Loss account for each department of your company.

    5. In the next tutorial you export transactions from an accounts package and create financial reports. Use this where the accounts packages doesn't hold balances. For example, every package will hold nominal account balances for the company as a whole, but very few will hold them for each department. In this case you will have to export the raw transactions and calculate the departmental balances yourself. Create financial reports from exported transactions

    Export 2,000 transactions from an accounting package and use pivot tables to check for any errors - eg transactions analysed to the wrong nominal account, why Debtors Control doesn't agree with the sum of customer balances, etc. After proving that the data has been correctly analysed, use a pivot table to generate the Trial Balance, Profit and Loss report and Balance Sheet.

  • You may like these other stories...

    No field likes its buzzwords more than technology, and one of today's leading terms is "the cloud." But it's not just a matter of knowing what's fashionable. Accounting professionals who know how to use...
    There is a growing trend of accountants moving away from traditional compliance work to more advisory work. Client demand is there, but it is up to the accountants to capitalize on that. What should accountants' roles be...
     Event Date: April 24, 2014 In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel. David will introduce the Macro Recorder, which transforms actions...

    Upcoming CPE Webinars

    Apr 17
    In this exciting presentation Excel expert David H. Ringstrom, CPA shares tricks that you can use with pivot tables every day. Remember, either you work Excel, or it works you!
    Apr 22
    Is everyone at your organization meeting your client service expectations? Let client service expert, Kristen Rampe, CPA help you establish a reputation of top-tier service in every facet of your firm during this one hour webinar.
    Apr 24
    In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
    Apr 25
    This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.