"Any Report You Want" - David Carter on Excel Pivot Tablesby
Excel expert David Carter recently conducted an online workshop with AccountingWEB's sister site in the United Kingdom on using Pivot tables in Excel. The tutorial he provided has turned out to be THE most read feature of the year in AccountingWEB, and we thought we'd bring it to this side of the Atlantic so CPAs across North America can benefit from this tutorial as well. Below is a transcript of the workshop, held December 15, 1999.
John Stokdyk, AccountingWEB: Welcome everybody, today we're going to have a hands-on session all about Excel pivot tables with consultant David Carter, who has written a tutorial for AccountingWEB. It is available online at:
To get things started; David said that for management accountants, "pivot tables represent the most important development in IT since the original invention of the spreadsheet. That's some claim David, how do you justify it?
David Carter: Actually John, I did say "for the management accountant, or anyone who has to analyze large a mounts of data."
John Stokdyk: OK - I'm not being deliberately provocative, honest... But why are pivot tables so useful? Some people view Excel with deep suspicion in the profession.
David Carter: Basically, you can drop down 30 or 40 thousand records out of your accounting/ERP system and produce any report you want.
Bob Jeffries: David is right! We have used pivot tables to provide enhanced management info from accounting systems such as Pegasus Opera, Sage etc for several years. The power of pivot tables greatly simplifies the month-end analysis for most managers
John Stokdyk: The people I've mentioned who criticize Excel are often software vendors who offer budgeting, forecasting, analysis and reporting tools. What's the advantage of the DIY approach with pivot tables?
David Carter: Yes, budgeting is one application. I've an FD who uses pivots to do all his job budgets and overhead budgets. He went to a Comshare seminar a week or so ago and reckoned they were only doing what he is already doing with Excel. Obviously, these budget packages cost money. With Excel you've got it there already.
John Stokdyk: Bob mentioned month-end analysis and you've mentioned budgeting. What other uses have you found where accountants could apply pivot tables?
David Carter: Talking about specific applications for accountants. One customer of mine operated from multiple branches and had a chart of accounts a yard long. They had a multi-segment nominal account - the first 4 digits were the nominal a/c, the next 2 the branch number etc. What we did was to use the "Data - Text to columns" command to chop this field into two. We were then able to report on both the nominal a/c and the cost centers.
Bob Jeffries: Pivot tables are a reporting tool. They can be set to allow 'drill down' on data and can be used to enhance enquiry information for any user in a networked accounting system.
Martin Povey: Any good training aids about? The online Excel help is difficult to follow.
David Carter: Martin - hopefully the online tutorial will answer your question! The problem with pivot tables is that it's a bit of a chicken-and-egg situation. You have to know what data to put in to get the reports you want. But until you've got the data in, you can't see their potential. We are going to give you some self-teach demo examples to train yourself. Watch this space!!
Bob Jeffries: I agree the Excel help is not particularly good. The key to the ease and success of pivots is the access to the information. Does your source info have ODBC links to Excel or do you need to output the data thru a file transfer of some sort?
David Carter: On-line help is very difficult. And Microsoft doesn't really understand what they can do. But Microsoft books do have a self-teach series on Excel and the pivot table section isn't too bad.
Christopher Bales: Microsoft books tend to assume too much and skip over areas too frequently from my experience.
John Stokdyk: I've tried the demo and got on quite easily with it, David. It's a good approach. But what you make easy for the student is one of the weak points - the data is there ready tabulated (in the budgeting demo, I know you've got an example of exporting data up your sleeve.) Any advice on how people can go about getting at the data they already have in their accounting software?
David Carter: Yes, the examples in Microsoft are based on simple stock reports or whatever, not around transaction records. Talking about ODBC, I 'm non-techie and I just use the accounts package's report generator to output the data in comma delimited or text format. I suppose it might be better to come straight out of the database via ODBC, but things like date formats may need to be changed and you can do this in Excel's import wizard.
Martin Povey: Bob, all my data is output in CSV format, which I already analyze through Excel & Access, but I guess pivot tables would take me to the next stage.
John Stokdyk: How different would it be, say for a Sage user to format and export data to a pivot table compared to a Pegasus user? Bob, you said you already do this sort of thing with Pegasus. How do you construct your month-end reports?
David Carter: This is the hard bit. You need to talk to the bloke in your organization who can use the accounts package's report generator. Then design the report, but output to "Comma delimited". Tell him, if you wanted to analyze purchase invoices, for example, to output a report which included Date, Invoice no, Account number, amount, nominal code, nominal description. If he could get that going and you managed to import it into Excel, you would get some good results.
Bob Jeffries: Martin, that should be fine, if you open the data in an Excel spreadsheet, then put the cursor in any of the data cells, you should be able to go to tools, pivot tables, etc and follow the steps. The main function of the Pivot allows you to breakdown data by groups, so for example if your data is a list of sales with customer and product codes, put the customer a/c number fields in the left hand panel (i.e. rows), put the product code in the top panel (i.e. cells) and put the sales value in the middle. It should give you a clear picture of total sales by customer by product.
David Carter: John, we were talking about these self-teach demos. I've got some old Sage data at home. If you're interested, we'll construct a self-teach demo about bringing data out of Sage via the Report Writer.
John Stokdyk: We can't go through the step by step (actually, that's not a bad idea - maybe we'll try it next time for a real "hands-on" session). But what are the key functions you need to learn to exploit pivot tables?
Christopher Bales: John's question seconded!
James McQuaid: I've started to use Pivot Tables recently with our existing DOS based accounting software. Whilst they are great for interrogating/sorting our live data unfortunately when you need to update/change the query you need exclusive rights [everyone out of that network program] is this a DOS problem only? Otherwise it limits the potential of the product. We are soon moving to a Windows 32 bit system... will the exclusive problem then disappear?
Bob Jeffries: John, in both Sage and Pegasus the data is DIRECTLY readable, as they offer ODBC links. In the Case of Sage you need to install the Sage ODBC link, but Pegasus is a MS FoxPro database and the Microsoft provide the ODBC tools. The 'trick' is to understand which files and data fields contain useful information. You would need advice from the software house or a reseller. As a dealer myself, I have been able to help many customers get more from the accounts system than the standard software allows.
David Carter: Gulp. I'm a bit nervous about looking at live data. Presumably you're not allowed to change it. Again, I would export in comma delimited and work on a copy. But I'm very basic.
Ben Heald: Bob, I'm jumping here, but are you saying that you can use Pivot tables to analyze data out of live Sage files?
David Carter: OK the first tutorial I've done is setting up a budget using pivot tables. Whenever your budget gets beyond a certain size you need to do it in a database to give some control over changes. Pivots make the ideal database. Try the demo!!
John Stokdyk: The need to work off-line is where the Comshares and Analyst Financials attack Excel - if you're taking half an hour or more to work up a sophisticated analysis, the underlying transactional data can change in a fast-moving environment.
James McQuaid: No I'm not changing the live data, just using it to summarize data by type/dept etc. However because of the need for exclusive access to the general ledger files we have stopped using pivot tables... or wait until everyone has gone home.
David Carter: Also, I've done another doing sales analysis - sales by product group, sales by month, sales by customer by month, etc. This one shows you how to import CDF data into Excel via the import wizard. The sales analysis application is just data warehousing under another name. This second demo will be with AccountingWEB this afternoon.
Bob Jeffries: David, one of the best aspects of pivot tables is that the data cannot be 'returned' to the live system, therefore there cannot be any corruption, although the comment that it is an exclusive process. For most month-end analysis you simply click the re-fresh data icon in Excel, it will take a few seconds/minutes to refresh the data in Excel, and once finished you can then play with the info ('slice & dice' is the awful American jargon) without the original data files being accessed
David Carter: James, perhaps you don't have a report writer and are accessing the data files directly.
John Stokdyk: --Sorry for butting in Jim - your question is an important one, but it sounds to me like DOS could be the limitation. Bob - can you "grab" your data OK while other people are in the ledger programs?
Christopher Bales: James, would expect a 32-bit environment to be able to cope with live changing data - but maybe others know better than me.
Bob Jeffries: John, the answer is I cannot grab data if the files are in use, but fortunately Pegasus (my main accounting system) segments the data in a way that the files I use most are not 'open' when a user is entering normal day-to-day transactions. If they were I would adopt David's technique of exporting the data to a specific file in my control.
James McQuaid: I note mention of a demo... how do you view it?
John Stokdyk: Jim - that URL again: https://www.accountingweb.co.uk/cgi-bin/item.cgi?id=9748&d=47
David Carter: While we're at it - more applications. One customer had a common problem - their budgets were in a different format from the output of the accounts package and she spent hours re-presenting the accounts so that they matched. We used the vlookup command to pull the budget account names into the actual database, and then reformatted the actual in the same format as the budget. Also you can use this for consolidations. If you have 4 companies with different charts of account, you can set up a master group chart of accounts, then vlookup the master group equivalent into each company. Then using a pivot you display the group in total.
Christopher Bales: vlookup and hlookup are extremely useful for pulling data together.
David Carter: Absolutely, if you get seriously into pivots, vlookup becomes essential. Basically it allows you to add further analysis fields, which are not there in the original data.
Bob Jeffries: Another point on accessing the live data is that Excel uses a MS Query to grab the data. Sometimes this is not possible or usable, especially if the files are excessively large (over 64,000 records), we have overcome this in some software by hooking live data via an Access query and then using Excel to 'pivot' the Access 'copy' of the data.
Christopher Bales: Thanks Bob.
Bob Jeffries: David, thanks for the comment on VLOOKUP, I will need to look at that to see if it can be of use to me.
David Carter: Any time, Bob. One more thing, have you used the Group and Outline facility for grouping totals by month, week, day etc? It's really brilliant. I would really like to meet the bloke who designed pivot tables.
James McQuaid: Understand what you're saying Bob but even in very small data files the exclusive problem arises... if it's just happening to me and not other users it must because the DOS software technology is outdated.
Bob Jeffries: James, my software is basically DOS, but as I said, it is the data structure of the package that lends itself to using pivots without the 'exclusive' limitation being a problem.
Christopher Bales: James and Bob - which software packages are you using?
Bob Jeffries: David, I group data by year/month etc by adding a column in the query that extracts the year/month from the date field in the data, for example. This makes it easy to produce a sales by month report by customer or sales rep etc
Ben Heald: David, have you any tips on getting non-experts to use pivot tables?
David Carter: Ben you raise the point on getting people to use pivot tables. I've often thought about this. Let's face it, they've been around since Excel 5 in 1994. It's this problem of not knowing what they can do until you see them used by an expert. Anyhow, I suggest you download the demos we're setting up on AccountingWEB and going through them in your spare time. Each one contains a data file of a few hundred or few thousand records (text format, so they're small) and the data file is accompanied by a Word document which takes you through step by step - press this button, type in "123" etc. Unless you can get to a demo from an expert, that's the best way to acquaint yourself.
Phil Thomas: Can anyone help me with the actual use of the data when you have built the pivot table? I often have difficulty copying the data for subsequent use/presentation and hence I find myself often building a cross tab report in Access from an Excel spreadsheet which when built is saved as a spreadsheet file for subsequent working
Bob Jeffries: Getting people to use pivots can only be done by showing them the speed and benefits. A recent 'convert' at my office couldn't believe he had failed to realize the potential once he actually started to use them. Previously he relied on writing reports in code or in report generators
David Carter: Yes, Bob, I find managers always react the same way. First they are gobsmacked when you show them what a pivot table can do. Second they say: and you mean we've had these all the time. Why didn't anyone tell us?
James McQuaid: Christopher, I'm currently using a bespoke type DOS package by a local company, we are looking to move to a package supplied by Great Plains called E-Enterprise.
David Carter: Phil, I think you want to copy the final pivot table without the data, then reformat it. To do this, click anywhere within the pivot table. Highlight the whole pivot table by holding down Ctrl-Shift. Then press 8 at the top of the keyboard. Now Control-C to Copy. Now Ctrl-N to open a new worksheet. To copy to a new worksheet, DON'T press normal copy Ctrl-V, but choose Paste Special - Values. This will just bring over the values. You can then reformat as you wish.
Phil Thomas: David - thanks for that. James we currently use Great Plains Dynamics C/S+ and for a while I was using an ODBC link to extract data from multiple tables and analyze them using the pivot table in Excel. The ODBC link was easily built and the results blisteringly fast and a good aid in highlighting areas for further investigation
James McQuaid: Certainly pivot tables are the flavor of the month, as mentioned we are looking to purchase new accounting software and every company who demo'd their software spent a few minutes showing the usefulness of pivot tables... That's how I first discovered them. I used them to analyze travel expenses by department, staff, city and even hotel... the other directors were gobsmacked.
John Stokdyk: David - what are the key commands people need to get comfortable with in pivot tables and are there any traps for the unwary?
Christopher Bales: David - John's question seconded.
David Carter: More applications: very useful in Job Costing. I first used them with the FD of a company that designed and produced in-house magazines for people like Ford and Royal Sun Alliance. He realized he could do all his job budgeting and overhead budgeting on pivots. He's being doing this for two years now.
John Stokdyk: Yes, but... HOW? If you wanted to tell us the 3 keywords to type into Excel help, what would they be?
David Carter: Phil, what you say about OBDC interests me. The problem with my way of doing it (export in CDF) is that a week later you have to rerun the report all over again. The problem here is that the manager who uses the report has to wait for you to come in and do it. Theoretically, if you are linked to live data via ODBC, all he would have to do is to press "Refresh Data" and the report would be updated with the latest data automatically.
Bob Jeffries: John, knowledge of using MS Query is important (which means you need MS Office PRO), otherwise I prefer to stay away from the excel help and hinder section
David Carter: Bob, I know bugger all about MS Query. Tell you what, I'll teach you vlookup if you'll teach me MS Query.
Bob Jeffries: its a deal!!
John Stokdyk: 2 more help/hinder hints, David? (thanks for MS Query tip Bob, I'm another novice)
David Carter: John, my hints have to be to work through the self-teach demos from AccountingWEB. The Microsoft on-line help and workbooks don't pull all the information together. I've been using pivot tables for about 3 years now and I'm still learning.
Phil Thomas: We used Pervasive's ODBC driver and Query as the front end - it could be done using Access or Excel as the front end but as there were questions over write access to the data, Query was thought to be the better tool. The report is then as you identify a user tool that can be run at any time.
John Stokdyk: This has been a really useful session - and may have created a few more converts for Microsoft Excel. But AccountingWEB Topic Expert Dennis Howlett sent in a sarcastic email, which I'll quote: "Promoting pivot tables in Excel? Do you know the recorded failure rate with Excel spreads? KPMG last said it was close to 85% in financial services businesses. Bacardi Martini stopped using them a couple of years ago because of a major cock-up and the maintenance costs of keeping a spread current (they bought Comshare)." Do you have an answer to Dennis's complaints about manageability/scalability. Is there a limit to the situations in which you'd use pivot tables?
Bob Jeffries: Dennis may be right in big organizations, but 95% of businesses are small/medium, they can get massive benefit from tools like pivots
Christopher Bales: Trouble with many spreadsheet applications is that the euphoria of getting them to work masks the need to test them to destruction afterwards.
John Stokdyk: Ooops! Speak of the Devil!!
Dennis Howlett: And he arrives! The main problem I've seen is the lack of adequate documentation, which leaves following users scratching their heads wondering about the logic
James McQuaid: What's the comparable cost of Comshare compared to Excel?
John Stokdyk: Excel costs a couple of hundred (unless you get a "free" bundled version with your PC. Comshare etc start at a couple of thousand per seat and go upâ¦
David Carter: Dennis, why did the KPMG survey spreadsheets fail? Generally speaking, I would say you need to understand what the data actually MEANS. Often, when one works for a long time analyzing data from an accounts package, you get to know the internal gubbins and the interrelation of the data files. You need to check your pivots against the reports produced by the package, to make sure that you have not made any false assumptions about the data.
Bob Jeffries: Dennis, documentation is pointless - no matter how good the manual hardly anyone reads them. The best method is professional training or assistance. Spending a few hundred quid on training can save massive time getting the reports you need without asking KPMG for the on-going help!
Dennis Howlett: Not talking about manuals - talking about the logic in the spread - this is a notoriously common area for misunderstanding - 'fraid if it was up to me I'd opt for WinForecast. May not be totally comprehensive, but for the small business person looking for easy budgeting, I reckon it's the dog's doo-dahs
Bob Jeffries: David's comment on understanding the structure of your data cannot be underestimated
David Carter: John, warming to my subject, what we are talking about here is a whole new science - that of converting transaction data to management information. When you use a report writer of the standard sort, select and subtotal variety, the final report looks like the original data and common sense is sufficient to see what happened. But pivot tables can produce reports which bear no relationship whatever to the format of the original data. In this case common sense is no guide. I strongly feel that anyone learning IT at university etc should be trained in how to interpret transactional data files and convert them into management reports.
Christopher Bales: David - you seem to be indicating that the pivot table should be set up in Excel to show the same data as the source format in the first view before you take it further.
David Carter: Christopher, not quite. What I am saying is that it is a good idea to try to replicate an existing report in the accounts package. If you can, then you know you are interpreting the data correctly. For example, recently I tried reproducing an Aged Debtors report from the sales ledger transaction file. But as the package was designed, credit notes didn't have a minus in front. So I ran the Aged Debtors simulation, found they didn't match, then realized that I needed to multiply credit note values by minus 1. Remember, pivots are summary reports on one page of perhaps 20,000 transactions. You can't just assume the pivot is right automatically and you will look a fool in the eyes of management of you give them a report which later turned out to be wrong.
Bob Jeffries: Christopher, the purpose of pivot tables to re-format the data. Pivots do not change the data, they help you distinguish the wood from the trees. Other tools such as Crystal Reports, Forest & Trees can also be used, but most people have Excel at their fingertips
Dennis Howlett: Crystal - eeek! F&T - just that!
John Stokdyk: Right, it's about time to start winding things up. On behalf of AccountingWEB, I'd like to thank David for sharing his tutorial with us -- and we look forward to more -- as well as for taking part in a pretty heavyweight discussion. I'm really interested in trying to use the workshop to run a tutorial, so that rather than talking about it, David could give us the instructions for us to work on, and then be there online if we encounter any problems. Would you be willing to do that sometime, and would other participants be interested in working through the tutorials online?
David Carter: Yes, John. Let's talk about how to do it - or try a pilot.
Christopher Bales: John - in short, YES
Bob Jeffries: Bye everyone and thanks for the fish!
David Carter: John, in the end I'd like to give you 3 or 4 self-teach demos showing sample applications of pivots. It's the only way to learn. If anyone has any particular request, feel free to e-mail me. Cheers!
Dennis Howlett: Cheers me dears - see you all in the opinion section...
John Stokdyk: Final comments or questions, anyone?
Christopher Bales: Thanks everyone. Another useful Wednesday lunchtime. Bye
John Stokdyk: Bye Bob, Dennis, Chris & all.