Creating Pivot Table Reports in Excel

An Excel Pivot Table Report is a specialized table that draws information from a data source to create a brand new table.  This powerful Excel tool is an excellent way to summarize information and saves countless hours of re-configuring and reformatting data.  Simply select your data source and insert a Pivot Table Report.  The advantage of a Pivot Table Report over filtering data is that once a filter is cleared, the data rolls back to its original configuration.  However, with a Pivot Table Report, the Pivot Table is inserted on a new worksheet by default, at which time you may begin customizing and filtering the data contained in the report, saving the changes for further use.  The original data source does not change and can be updated at any time, with the updates being reflected in its associated Pivot Table Report.

An example would be that you have a very large spreadsheet, say 26 columns and over 2000 rows.  This spreadsheet details the sales of a company by sales person, country, product sold, extended price and date shipped.  If you want to include only certain subsets of the data in a report, you can select your range of data and then insert a Pivot Table Report.  Once the Pivot Table report has been added to a new spreadsheet in your workbook, you can begin adding the fields that you want to include.  You could, for example, only include certain sales people from specific countries, and then filter the data to show only their sales exceeding $500 per product.  Or you could decide to include only certain products with an extended price under a specific dollar amount.  The choices are numerous and the data can be configured by any number of user defined criteria.

Pivot Tables get their name from the ability to “Pivot” information once it has been included in the report.  If you have information in a row and want to pivot it to a column (or vise versa) that can easily be accomplished with the click of a mouse.  The new features available in Office 2007 through to Office 2013 also allow the user to quickly format the way a Pivot Table looks and achieve professional looking results with very little effort.  To create a Pivot Table Report in Excel:

  • Place your cursor anywhere within the data source you wish to use.  (When you place your cursor in the data source first, Excel will automatically select the range of data for you when creating the Pivot Table.)
  • Click the Insert Tab on the Ribbon
  • Select the Tables group
  • Click the Pivot Table button
  • In the Select a Table or Range field, ensure that Excel has selected the range of data correctly
  • In the Choose Where you want the Pivot Table report to be placed field, ensure that New Worksheet is selected
  • Click the OK button when done

Excel will insert the Pivot Table Report into a new worksheet and you will be ready to begin adding the desired fields.

Next time you need to summarize data, consider using this powerful tool to save time and energy, making the most of your working day.

This entry was posted in: MS Excel Tips.
"Hawthorn Training delivers expert and up to date software training solutions for your business."