Pivot Tables in Microsoft Excel

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.  Thisdata table 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!

Now let’s learn how to create a Pivot Table in Microsoft Excel.

One thought on “Pivot Tables in Microsoft Excel

  1. Mark

    Google pivot tables are a great feturae, but there doesn’t seem to be a facility for adding the column headings in the source spreadsheet to the data in the pivot table. For example, my data has names of people categorized by age. I need the pivot table to show “Names of 5-11 year-olds”, “Names of 12-18 year-olds” etc at the top of the columns, but it will not do this. It just lists the names in the columns, but there is no way of seeing the age category they come into because the header is missing. The heading is on the source spreadsheet, so why can’t the pivot table pick this up? I gather this facility is within Excel 2010, so please can we have it in Google Spreadsheets too?

Leave a Reply

Your email address will not be published. Required fields are marked *