Excel is one the he most powerful functions in Excel. To fully understand and get the best out of this fantastic function it is best to know and understand the core terminology, concepts and foundations of getting your data correct. Once this is correct, Pivoting your data will be straightforward.
Let's start with the basics. Pivot tables allow you to analyze a dataset. A data set, sometimes referred to as a record set is simply a table or list of data which you could have typed into Excel yourself, or equally received as an output form an SQL query or an MS Access Database.
The Pivot Table will analyse the data in this data set, and will quickly allow you to summarize, perform calculations (without typing Excel formulas!!) on huge amounts of data all by dragging, dropping and selecting columns and rows, interactively moving them around to display the data summaries you want to.
What's With The Name?
So, why are they called Pivot Tables? Well, you literally can 'pivot' the data in different ways and immediately see it's effect on your information as you move the data set around.
We talk about data tables earlier. A data table is no more than a list which has more than one Column of data with a Heading for each of the columns. Its really important to get the structure of your data table or set correct as it is the basis for your Pivot Table. Once you know the concept of this, applying it is easy. Follow these short set of guidelines and you should have no issues with your Pivot Table data.
- Firstly, HEADINGS- these are essential for your columns. Each column should have its own unique heading. They should always be in the row directly above your data with no blank rows between your columns heading and your data. Ensure that they stand out as heading- I ALWAYS bold my headings just so they stand out.
- Never have BLANK cells in your data. Repeat the data as many times as you need to but no blank cells.
- I usually leave three or four blank cells above my data set headings. I use these for formulas or additional information sometimes, but they can be hidden. Handy to have none the less
- If you have more than one data set and they are related then make them into one large table. Let Excel and Pivots do the hard work!. If they are unrelated but on the same sheet if you leave a couple of columns between them, then they will be recognized as being different sets of data by Excel.
Once your data is correct you can go onto use the pivot table function to quickly build repots to show
Average Sales Per Region
Total Sales Per Region
Total Sales per Sales Person
All without having to calculate any totals or subtotals with formulas or macros.