Learning to love Pivot Tables in Excel 2007/2010

Could you reinstate the links at the bottom of the article above?

Thanks

I use Excel at a fairly low level - basic summarising and analysis - and find the 2007 menu system far too cumbersome and complex.

Thanks for the link. I'll have a look after 31 January (you can guess why).

pivot tables = my general; practice secret weapon, clients give me a mish mash of csv files and in minutes I can produce a set of balanced "books", very impressive and so easy ... well, I say easy, after many years of being perplexed the penny finally dropped.

I do wonder like the op whether Microsoft ever leaves its ivory tower to see the real world, they do so much to break what we try to do.. the classic being columns of numbers that do not add up and data table results that give all wrong results.

Happy Pivots...

Anthony

Hi Anthony - have you come across a specific problem with data tables that would be worth the rest of us knowing about and avoiding?

well.. it's an ancient problem.

create a set of data with account codes in a column,

use an excel data table (now largely superceded by pivot tables unless non dynamic out put is a pre-requisite for example for onward coding) with one or two input cells, hoping for a trial balance (or ETB) all nicely analysed between each code (where the code column goes into one of the input cells) and the result is that all codes that have their first 4 characters the same, will each include a summation as if the characters beyond character 4 do not exist, so .. that sounds complex, say for example

motor  100

motor expenses 100

motor car 100

car 90

the total for each heading above should be as shown, but assuming those descriptions are also used as the codes, the total for the first three will be 300 in each case, and 90 for the last. I muse that presumably no one ever used data tables for creating extended trial balances.

Solution is to use a data query extract unique (in a data set where we are making up the codes as we go along since we are in a nice flexible spreadsheet environment) to identify (by eye) all codes that have first 4 characters the same and make them different. Then it works.

As I say, old methods.

Be surprised if anyone suffers from the above, but happy to help herein if so.

maybe it's fixed but I doubt it as the issue has existed for well over ten years, probably 15+.

p.s. the same data in Lotus 123 gives (to this day) the correct answers, no bug.

Anthony

Thanks Anthony - very curious. By 'data table' are you meaning the Table option in the Data menu (or Data ribbon, What If Analysis, Data Table in 2007/10)? If so, what formula did you use to calculate the totals? (I ask, because coincidentally I'm just writing about Data Tables at the moment for some course notes I'm producing.)

yes.. in fact I only very recently read that this is where the old /data tables went to. I think Excel's characterisation of data tables as "what if" tools has misdirected accountants' attention from their neat ability to generate ETBs from spreadsheet data sets (aka bank/credit/cash/journals/accruals-preps/etc) when combined with DSUM formulae. It's that DSUM formula that more exactly contains the bug (come to think of it!), not the table mechanism itself, though when combining DSUM in a Table, with the first 4 chars feature in the codes, then all the related table results are wrong - in other words a perfectly balanced set of numbers suddenly reports as error filled.

Sorry, you needed to know about DSUM given you are authoring rather than suffering from the erroneous results and wondering why.

Pivot tables look "nicer" and are fast for what-if use when error hunting in csv file imports and matching transfers and so on, in fact very powerful I have found in recent times, but data tables have offered the same (for my TB purposes) reporting in a non dynamic form since the year dot (or rather when Lotus 123 developed them... now I am scratching my head, I don't think Excel for the Mac had data tables when Lotus did... circa 1986).

Dynamic Pivot versus Fixed Data Table: the thing is that if you want to do further data processing after the table is produced, the Fixed Data Table facilitates that, because it's length is firmly controlled by the user, whereas the Pivot Table length flexes. I add a concatenation of amount, code, description with commas between to create a CSV file for output and import into IRIS, though it's only a matter of moments to add manually when the Pivot report is final - and Pivot Table Calculated Fields cannot concatenate across results because they work only on the underlying data, not the reported results (as well as not applying formulae to text).

Of course the Pivot Table doesn't get it's summation of analysis codes wrong: it reserves that for its totals when Calculated Items are added - we are going to get got with sums that don't sum one way or another! There's no avoiding control totals and =ROUND() 

I remember the days when we proudly presented our spreadsheets only for the sales director to comment something didn't add up - (I read it in Accountancy!) So while we all now know about such spreadsheet issues, the op in 2012 still finds (as do I) that bad results that could be programmed out, are not. If MS fixed such things when informed I would sympathise, but they do not and bugs subsist for years and years and years - even arguments are made to justify them. Numbers not adding up, cannot be justified (aside from left right or central). For years I have treated it as unsupported software, that way I remain relaxed instead of irate.

Thanks Anthony, for such a full explanation and for some interesting ideas on the ETB generation issue. I've been looking at doing something similar with 'normal' Excel tables in 2007/10 - I'll look into this a bit further some time. With regard to PivotTables and calculated fields, the new PowerPivot AddIn for 2010 that David mentions includes a whole new area of calculated 'measures' and a new set of functions to work within them.

An Excel feature related to PivotTables that is under-appreciated (in my opinion), is Cube Formula Reports.

In Excel 2007 these are only available to users who access OLAP cube data, usually from Microsoft Analysis Services. But with Excel 2010 and the PowerPivot add-in you can build them from a PowerPivot database that can be an Excel table or range, an Access DB or a variety of other data sources.

Having created a PivotTable from this data, there is an option (under OLAP Tools on the PivotTable Tools tab) to Convert to Formulas. Then you have Excel's cube functions that are still dynamically linked to the data but can be moved wherever you want (like regular Excel functions) and don't have the layout limitations of PivotTables. I find these very powerful for financial and management reports that require careful placement.

I have been thinking I should learn about powerpivot asap, not least because I have not yet ever managed to get ODBC to work, my nemesis (a mac problem). All I want is to use MS Query to csv and excel files, in case anyone has an instant solution regarding 3rd party ODBC drivers for Mac.

Any suggestions where to learn about the concepts of Powerpivot and the OLAP matters to which you refer? I can see many trees, but not the shape of the forest.

Excel 2010 KPI tutorial 3: PivotTable dashboards included a brief introduction to PowerPivot but certainly didn't get as far as CUBEMEMBERS and CUBEVALUES. I found MrExcel's book  'PowerPivot for the data analyst' very useful both as an introduction and for more detailed information. I also included a couple of short chapters on the use of PowerPivot in my own book on Excel but just as an introduction to the possibilities and concepts rather than a comprehensive tutorial. The Microsoft PowerPivot site (formerly www.powerpivot.com but now http://www.microsoft.com/en-us/bi/powerpivot.aspx ) also has lots of information including demonstrations and videos.

what's your book called?

I keep seeing Sharepoint being pushed and pushed, but not a pricing model to suit me as a one man band. For example I'd love to be able to say to my client "go here and login where we can share a spreadsheet while I go through it with you". I spent three hours on the phone talking a client through a pivot table report the other day, but this was unusual because he happened to have the same version of excel on the same platform as me.

Actually, there was not so much luck involved because I was running both a mac and a pc using a KVM two computers one screen/mouse/keyboard combo) so while I talked him through it in Excel 2010 for PC while we each ran the same file, I was also toggling between the mac and pc computer screens making updates to the same file on the Mac. That way we could use the PC version as a demo and safely update the mac version.

Of course I could just have run two versions of the same file in different windows on the pc, but I enjoyed the twin machine experience, having just bought the KVM switch, which switched instantly, quietly impresses me, so while the mac loads something I work on the pc and vice versa - and the pc is now running with twin SSDs (solid state disks) and can reboot in 30 seconds. Amazing (to me).

The client said he was "blown away" by the experience, so I wished I could bottle it!

I did sign up to Office 365 at one point, but cancelled it because the two files I tried to use turned out to have "unsupported features" (I think one was "protected sheets") and given they were Excel files written in standard Excel I concluded that (dare I say) as usual the MS offering does not work as it says it does.

I love the look of sharepoint from a client Excel pivot table file use sharing point of view, but seek a solution within my one man band budget. If the £4/month includes sharing a file with a client I might bite, but I think I would be required to pay for another "user". All this is the road back to Google docs.Which is another can of incompatible worms: or screen sharing, which totally lacks the screen response  as compared with each of using the same file on our own computers (so nothing is remote) and chatting by phone, which is crude but effective as long as it's not the live file (because of the risk of making mistakes when in "demonstrate to the client" mode of my mind).

Thanks for asking! I discussed the title with the publisher for some time and in the end we went with snappy over informative: Maximising the Impact of Accounting & Financial Spreadsheets for Finance Users. There are some extra details and links on my website: www.tkb.co.uk

Shameless advert out of the way! There are lots of hosted SharePoint services available but I don't know how much they cost and I have no personal experience of them. Just a thought, would some sort of online collaboration application like GotoMeeting achieve what you want - and give you the flexibility to discuss any content. Although GoToMeeting costs about £30-40 a month, there are cheaper alternatives.

Another thought - Excel 2010 makes it much easier to share documents over the Internet (security concerns permitting). I think you just need a Windows Live account and can then save Excel 2010 documents directly to an MS hosted site and then collaborate on them.

that sounds just the ticket and the right price!

any idea where or what I can search for?

I'll start with sky drive

Thinking about it, I think SkyDrive, like PowerPivot, is only built into Excel 2010 (I'll edit my earlier comment to avoid confusion), so sorry to raise your hopes if you've only got 2007. If you do have 2010 then just go to File, Save and send, Save to Web. Your client shouldn't need Office 2010 because they can view the file in a browser.

I should have added that the Excel WebApp has nowhere near as much functionality as desktop Excel so whether it all works will depend on just what the spreadsheet contains and the features that you want to collaborate on.

I share your love of pivot tables. One of my recent assignments was for an accountant who had always done things manually and he had no accounting software such as Sage/Pastel/Quickbooks.  I used MS Excel to do the bookkeeping and year-end journal entries and then used Pivot Tables to produce the draft financial statements and working papers.  It was a great assignment which impressed the boss so much that he asked me to produce pivot tables to summarise the previous year ends of all his clients so that the following year-end would be so much easier to do.  Sadly, many accountants out there have little knowledge of what a fantastic application Microsoft Excel is.