If you don’t know and/or understand Pivot Tables yet, read on!
I Need to Keep Talking about Pivot Tables
The course I have just finished had a large Excel element and for the first time I ran an Excel Mini Project throughout the course. Well, not quite throughout but I got the delegates to tell us about a file or topic or small niggle that they had and that we would work on. It didn’t go entirely according to plan in that we didn’t have the time to spend the 30 – 45 minutes a day on the projects that I would have liked.
Every Excel based course brings out something good but this time I got one of my specials.
Firstly, a delegate is using a bridge chart in one of his monthly Excel based reports but he was making manual changes every month because he couldn’t work out how to automate it … I gave all delegates a copy of my own Excel Bridge Chart file for that together with full instructions. By the way, I am uploading all of that to this blog now too … Excel How To … Others … Waterfall and Bridge Chart.
Secondly, a delegate wanted to know how to use capital investment appraisal methods in a project setting. I started by taking him right back to the classification and coding of costs, costs per department, per unit, per activity … and gave him a file I had developed for product cost development.
Thirdly, this week’s winner: the delegate who showed me his working file, his monthly report. It looked good: all costs classified and analysed by area, department, cost heading, cost element … real cost accumulation, real management reporting. However, he showed me that every time he recalculated the 30 worksheets in the work book, he had to wait up to 15 seconds for it to finish EVERY TIME HE DID IT. Just entering a new number in one cell caused the same effect. Yes, he could have set calculation to manual but I didn’t think that was the solution.
This delegate was using =SUMIF(…) in every cell of his management reports to go to data input from management report and back again to gather the values for each column and row and it worked. I tested it and was happy that it was doing what he wanted.
The data input sheet comprised a listing of all of the month’s costs under the various accounts and elements, as taken from the SAP database. As far as I could tell, it was good, fine, nothing in there to cause the 15 second delay.
Well, it took me 10 – 15 minutes of exploration and waiting those 15 seconds and suffering a “memory out” problem on my MacBookPro to decide what to do: create a Pivot Table!
I deleted the 30 worksheets that contained the management reports, having first looked at them and having made a note of the row and column headings. Within five minutes I had created the Pivot Table I wanted. I then moved the Department classification into the Field Page area and asked Excel to generate the 30 departmental reports for me. Within 5 seconds I had replicated the work of the delegate, with just one minor omission, one level of classification that I did not have but that he would be able to include very easily.
Now, this file is clean and tidy, it takes just seconds to update … MONTHLY and not every time someone enters or calculates something.
I was pleased with that and it took me just 10 minutes to demonstrate what I had done to the group. The pay off for me is that I was able to take an Excel problem without warning and preparation and within 30 minutes had solved it and created the solution. Moreover, the delegate told me that he had been discussing this problem around his office for a long time, many months; but that no one had been able to provide the solution