Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

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

by
20th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

If you want to learn about Excel pivot tables, you've come to the right place. AccountingWEB has built up a wealth of learning resources over the past six years. David Carter takes you through what's available.

[Note for users of Excel 2007. These tutorials relate to Excel 97, 2000 and 2003. I haven't updated them to Excel 2007 yet because it is so different from previous versions and I don't know it well enough].

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 160,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.

  • Replies (6)

    Please login or register to join the discussion.

    avatar
    By David Carter
    18th Feb 2009 20:19

    Formulas in Excel 2007
    Jean, thanks for the kind comment; I hope to be updating this tutorial to Excel 2007 soon.

    At the top of your screen there is a bar called Pivot Table Tools. Click on it. Then choose Formulas in the Tools section at the right (next to Pivot Chart). David

    Thanks (0)
    By buttercup books
    13th Feb 2009 23:34

    brilliant tutorial
    Tutorial - sales analysis with excel pivot tables
    although I frequently use excel I haven't used pivot tables before. I thought this tutorial was brilliant and I managed to translate most of it into excel 2007 - but I simply couldn't get to grips with paragraph 17 - using formulas - can anyone translate that into excel 2007 for me

    Thanks (0)
    avatar
    By AnonymousUser
    29th Mar 2006 12:42

    Useful - but misses the first step
    Having being confused as to how to create pivot tables in practice I discovered on another site that they work only when the data is in lists or tables, not in report format.

    This made your tutorials crystal clear from then on.

    Although I may be the only one confused in this way, I think specifically stating the requirement for having the data in the right format at the beginning of the tutorial would be useful.

    Thanks (0)
    avatar
    By bthadeshwar
    10th Feb 2010 12:30

    For 2007?

    Hi David,

    Just to check, do you have any time frame by which you would be able to help with Excel 2007?

    Thanks (0)
    avatar
    By David Carter
    24th Mar 2010 20:04

    Sales Analysis tutorial for Excel 2007

    Re the Excel 2007 version of this tutorial, I've moved into the video age, so there's now a video of this tutorial for Excel 2007 on Youtube.    Link here:

    http://www.youtube.com/watch?v=PrfrVxAixH8

    or just go into Youtube and search on MrDatamart     (I've got 4 or 5 up altogether including a couple on the new breed of Excel-based report writers which do all the things you can't do with pivot tables).

    Thanks (0)
    avatar
    By alberto63
    21st May 2012 18:43

    Thanks

    You were right: at the beginning, the best way to learn is just doing it, and this tutorials are excellent tools. Besides learning Pivot Tables one gets involved in logical way of accounting thinking. Pure and simple applied knowledge.

    Thanks (0)