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.
Pivots 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 analyze 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 and on
All of this with a couple of clicks!! in Excel.
The pivot 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. Today we will concentrate on an MS Excel worksheet data source which is usually the most popular data source that is used.
It is important your data set is complete with no blank rows or columns and contiguous in nature.
Tutorial- Create Your First Pivot
- Select the data set you want to use for your table
- The first thing to do is put your cursor somewhere in your data list
- Select the Insert Tab
- Hit the Pivot Table icon
- Select Pivot Table option
- Select a table or range option
- Select to put your Pivot 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
That’s all there is is to it.