Pivot tables. You may ask yourself why are they called Pivot Tables?
Essentially Pivot Tables in Microsoft Excel allow you to ‘pivot’ your data using the Pivot Table’s in built drag and drop feature.
Doing this provides instant results, and does not change any element in your originating table of data. So, if even you get it wrong, you will not affect the source data! Perfect for practicing and generally playing around with your data!!
A Pivot Table can be used to provide useful information generated from another table of information. Let’s assume you have a data table that contained information including; product name, price, purchase date, delivery post code, and order quantity. Using a Pivot Table will quickly show you some of the following:
- How many unique products have been ordered
- How many unique products sold by date
- How many items dispatched by post code
You can extract so many different results, and quickly, that it can be a real advantage using Pivot Tables to provide easy to understand reports. And yes, you can use Excel’s functions to provide exactly the same result, but this would be a longer process, not to mention use more PC memory… although this is not really a concern with modern computers. Using Excel’s SumProduct is a similar way of producing the same results without using a Pivot Table. But you cannot easily change it and experiment.
Using Pivot Tables
When you decide to use a Pivot Table it is important that your data is formatted in a way that looks like a table or a list. The Pivot Table uses this data for everything and so it is important that the table is set in a specific way. Let’s look at what would be best practice for your source data before creating your Pivot Table!
You must use headings for your columns of data. Ensure the heading uses different formatting to the data itself. For example, make headings bold. Do not have a blank cell between your data and it’s heading.
If you have multiple tables serving different Pivot Tables then separate each source data table with a blank column. This should aid Excel in identifying each data set as unique.
Try not to have blank cells in your data.
For ease of reading with a human eye, format the data font at a minimum of 11 pts. You could also sort the data by a specific column, for example date. These two points do not affect creation of a Pivot Table, but aid you, or someone else, reading the data.
So now the data is formatted to assist us, and Excel, let’s create a Pivot Table. Luckily Excel provides more help with the Table and Pivot Chart Wizard. Great!