Now we can easily see that Janet Levering with 13.86% of sales wins. It's bonus time and they want to know how each sales person did and how they ranked overall in 2018. Our final request was from the Sales department again. Now the Pivot table has been arranged to show the % difference year on year for each country and we can see that our 2018 sales in Argentina are 72% down on the 2017 total. But they really need to see the % difference in the years so they can focus the marketing budget where it needs to go. They need to see the sales data broken down by country, year on year. Seeing how useful this data from Pivot Tables is turning out to be, the Marketing Department decides to get in on the act. Here, we have added a Slicer to the Pivot Table, so we can quickly filter the Pivot Table to show a sub-set of the data. Whilst the management team are happy with this, they need to able to quickly drill down and see the figures for a category of product. This time Management have asked us to show all sales performed year on year to make it easier to see increases and/or decreases. Not convinced? Producing the basic Pivot Table was just the start. OK, admittedly I'm pretty good at producing Pivot Tables, but with a little training, even a novice Excel user could produce this in less than a minute
The big difference? This took me 15 seconds and I didn't have to write a single formula. The next Screen shot shows the same data produced by a Pivot Table.
#PIVOT TABLE TOOL HOW TO#
(If you don't know how to do that, it's going to take waaaaay longer.) My 5 minutes were spent implementing, I already knew how I was going to do it. The real problem here is first understanding how you will solve the problem, then of course, you must implement it. Of course, I had to extract a unique list of sales people and categories first, and I used Table formulas to make my life a bit easier. This took me around 5 minutes to produce, not bad from over 30,000 rows of data. =SUMIFS(salesdata,salesdata,$C7,salesdata,D$6) Here is a table (not a Pivot Table) showing sales data broken down by Sales Person and Category.Īnd here is the formula I used to get a single value: Now, in most cases we can use other tools filters, subtotals, formulas, conditional formatting to get at this data. What was the bestselling product (or the fifth)? Which operating theatre had the highest failure rate? What was the average air temperature month by month in a specific location? These are all very useful sets of data, but the real value, the interesting stuff, is buried deep inside the data. This could be last month's sales data from your on-line shop, Medical procedures carried out in a particular NHS Trust area, or raw meteorological data recording temperature and sea level readings. Well let's imagine that you have a large set of data. Since then, most of the other contenders have fallen by the wayside and although LibreOffice and Google Sheets support Pivot Tables, the undisputed King of Pivot Tables is Excel. Microsoft joined the party in 1994, when Excel 5 included Pivot Table functionality for the first time.
Lotus was first to the market with a Pivot Table enabled spreadsheet, Improv, in 1991. Pivot Tables have been around for a long time (well in Personal Computer years anyway). I prefer to think of a Pivot Table as: A really cool way to get to the stuff that's actually important to you.Ī bit of History.
Or, as Microsoft say in their own words: "A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data." A Pivot Table is a statistical tool to extract and summarise data from large data sets. OK, let's get the boring bit out of the way first.
0 kommentar(er)
