One of THE most useful features in Excel is the pivot table which is used to summarize, analyze, explore, tabulate and present your data. Not just small amounts of data, but we can cope with huge amounts. Whether it is 500 or 50,000 lines of data no problem with your friend the pivot table.
Pivot tables allow you to summarise your data in a condensed form, and at the same time having the functionality of a cross tabulation summary, i.e. you can easily rearrange and display different parts of your data set in multiple views, hence the name Pivot Table. Essentially you can pivot your data and move it around to analyse in numerous ways. You can turn a two dimensional data set into a three dimensional one.
Example uses of Pivot Tables- there are many but let’s detail a few to get an idea….
- Summarizing data- for example, sales per sales person, per region, per product per period of time- no problem with a pivot
- Filter, drill down, sort data on reports with little effort and absolutely no macros or programming – cool
- Show your data as percentages, average, min, max- the list goes on
All of this with a couple of clicks!!
The pivot table can be created from either a data list on one or more MS Excel worksheets, an MS Access database, external data file or another pivot table. Today we will concentrate on an MS Excel worksheet data source.
It is important your data set is complete with no blank rows or columns and contiguous in nature.
Tutorial- Create Your First Pivot Table
- Select the data set you want to use for your pivot table
- The first thing to do is put your cursor somewhere in your data list( why not use my example data in the Download?)
- Select the Insert Tab
- Hit Pivot Table icon
- Select pivot table option
- Select a table or range option
- Select to put your Pivot Table on a New Worksheet or on the current one, for this tutorial select the first option
- Click Ok
- The Options and Design Tab will appear under the Pivot Table Tool
- Select the check boxes next to the fields you want to use to add them to the Pivot Table
You can download the workbook for this tutorial and work through the example right[wpfilebase tag=fileurl id=4 linktext=’HERE.’ /]
Want to see a Video Tutorial on how to create your first Excel Pivot Table? Check it out below!