You might also be interested in
Replies (17)
Please login or register to join the discussion.
That was clear as mud.
Incidentally, my "mouse clicks " are carried out on the key board so bit of vapid claim to be able to do this without touching the keyboard
In benefits of pivot tables you said they don't update immediately like ordinary spread sheets. Thats a bit of a drawback surely. I want to monitor my spread sheet is working as I want it to and to follow the result of changes to underlying data.
"Insert Ribbon tab" I have no idea what this means. There's no button for it
"As a taste of what we will be covering in future episodes, we will go on to use our date to create a line chart showing the change in house price values over time, by country." I think County?
Perhaps it will all mean something if I actually try to do what you demonstrate. Actually I expect I will get horrbly muddled.
Definitely not convinced of any benefit in "pivot tables" yet
KABcraigmore: thanks for your comments. Of course, there are many substances more opaque than mud so it could be a stage on the way to enlightenment. I used mouse clicks to try and demonstrate the simplicity of this particular project as they require no prior knowledge. You can look at the screen and find what you need rather than needing to know what it is you need to type on the keyboard.
I agree that PivotTables and many other related features in Excel require a different way of thinking and working, which is why I stressed this point at the beginning. It's just a different way of thinking about how your spreadsheet works that most people get used to relatively quickly, but you are absolutely right that for some people it rules out all the other benefits of PivotTables.
You make an excellent point regarding the Insert Ribbon tab: I have stopped including references to versions of Excel prior to Excel 2007 so anyone using Excel 2003 or earlier will not be able to follow the instructions step-by-step.
In this post-Brexit world regional sensibilities are heightened enough without describing Wales and Scotland as counties...
KAB should get a life.
I'm sure if one was serious about learning this you would use the actual data as an exercise then much of it would I'm sure become clear. I regard myself as a fairly clued up Excel user but Pivot tables have so far remained slightly mysterious. I think choosing an example with loads of data clearly demonstrates how useful Pivot tables can be so thanks for that.
Well, not every Excel user needs to use PivotTables, but there are probably lots who don't that would find them useful. I'm glad the large data set helped, but PivotTables can also be very useful for much smaller data sets. As an example, one of my support clients wanted to summarise a few dozen bank transactions by month. A PivotTable with the date field grouped by month proved to be a quick and easy answer.
The explanations and method were pitched just right - reinforced what I'd already stumbled through myself and gave a flavour of what more can be achieved. Really smart to use a dataset that we can all access and try out. I look forward to the next instalment. Thanks.
Thanks Anita - I'm glad you found it useful. It took me a while to find a suitable public dataset, so I'm pleased that it proved to be worthwhile.
Thank you Simon for your helpful working and practical example. I look forward to learning more about pivot tables in your subsequent discussions. I particular liked your step by step approach with lots of screen prints.
I found this really helpful, taking my limited knowledge of pivot tables to the next level. I look forward to future instalments.
PivotTables - Excel's Marmite
Thanks for all the comments - supportive and critical. The different reactions seem to confirm my long-held belief that PivotTabls are like Marmite - you either love them or loathe them.
If you've never understood the appeal of PivotTables it might be worth sticking with the series for an episode or two more - at the worst it might just confirm that PivotTables are not helpful for the sort of things you use Excel for.
Thank you Simon for this. I do not use pivot table but a colleague and my son both insist I should get a grasp so more of the same please. I am more interested at the moment in WHY I should use them rather than how but HOW will be important. Again many thanks
Hello Simon, I have attended your ICAEW courses in the past & the AccountingWeb articles are helpful thank you. Occasionally I get caught out with following examples, I use Excel version 2010, & all was well until the Filter & Sort dropdown to select England, Scotland & Wales as groupings (could see how to filter specific counties)- could not see how to do that...is it a feature of later excel versions? Many thanks, David
Hi David - thanks for coming to the courses and reading the articles. The England, Scotland and Wales 'regions' should be included in the Region_Name dropdown in alphabetical order with all the counties. It's slightly odd, because you would, as you say, expect this to be an additional grouping level rather than just an item within Region_Name. Please let me know if this doesn't resolve the issue.
I'm a long term "simple" Excel user and haven't tackled pivot tables. Although this is a useful sets of mouse clicks to see "an example" of a pivot table, from the heading "Why PivotTables?" I was looking forward to being given a few examples of "Why PivotTables?".
I think there are probably a lot of users like me who would make the effort to use pivot table IF we knew that was the best approach, but because they seem so intimidating, I think we don't even start. I know I don't.
So please could you try to describe some situations when we should consider "Why PivotTables?" beyond this one example. And perhaps this article could point out that the Top 10 figures arrived at when using this particular data set aren't of any great significance, they just from a handy, publicly available data set and so useful to go through the motions described in this Part I.
Thanks for your comment Swiftman.
The intention of the article was to demonstrate that, if you are faced with a set of values, categorised in any way, PivotTables provide a simple and straightforward way to summarise the data without needing to use any Excel formulae or functions.
Because PivotTables allow you to summarise and present data from any structured set of data, the underlying assumption is that you have some sort of coherent list or table of data that contains too many values to allow you to assess the significance of different aspects of the data without summarising it in some way. To give some other practical business examples, using a PivotTable with a list of sales invoices might allow you to see:
Which customers spend the most with you
Which products generate the most revenue
How seasonal your sales are
The trend of overall sales over time
The trend of particular customers' sales over time
The trend of particular product sales over time
Which products particular customers buy
Which products are popular in which regions
Which month generated your highest sales last year
Which product's revenue is declining most rapidly
For those interested in such things, this use of PivotTables can form the calculation 'engine' behind creating an interactive dashboard to help make key management decisions, but PivotTables are equally useful in just creating quick summaries to explain data.
You could extend these ideas to any set of data, from any set of business values or transactions, through to lists of the music tracks you have downloaded or ripped, or your fantasy football league players' records.
I think the problem might be, as you say, that people see PivotTables as intimidating. That was why this first article in the series tried to show just how easy it is to summarise a set of data and turn it into something meaningful.
I'm sorry if the top ten highest regional house sales value was not of any significance to you, but hopefully you can extrapolate the idea to data that is of greater personal relevance. If you have any data of your own that you'd be happy to make publicly available I would be delighted to try and demonstrate a practical use of PivotTables that would be relevant to you.
In any case, hopefully the other articles in the series will demonstrate many of the ways that PivotTables can be useful:
https://www.accountingweb.co.uk/tech/excel/ez-guide-to-pivottables-2-fro...
https://www.accountingweb.co.uk/tech/excel/ez-guide-to-pivottables-slice...