Overview
A Pivot Table is a built-in tool in Excel that is used for organizing or summarizing selected data from tables in databases and spreadsheets, and to use this Pivot Table feature to create a report without changing the original information.
What The Heck Is A Pivot Table?
Pivot Tables are tables which can compile huge amounts of raw data and display it for the user into an easy to digest format that makes it much easier to find trends and sequences that lie within.
Considered to be the most powerful tool in Excel by many experts (due to their quick use and strong analytical power) they can be used to quickly re-organize and filter selected data however you want.
Why The Heck Would I Want To Use An Excel Pivot Table?
The main strength of using an Excel Pivot Table is the ability that they provide in allowing the user to alter the arrangement of the summarised information by rotating the various row and column headings to tweak the data on display and provide a new representation of your source material.
The first thing that you need to check is that your database consists of the appropriate source data to use a pivot table, as it requires:
- At least one of the columns must contain repeating information, such as a yes / no, or a similar type of value from a limited range of options. These are referred to as duplicate values.
- It should include numerical data (values to crunch) otherwise the search function of a database may be more appropriate for your needs.
Creating a Pivot Table
Using the Pivot Table command button on the Insert Tab is the first step using this very cool feature. Once you place your cursor inside of a table and then click the pivot table button, the interface is relatively straightforward. In our seminars we spend a lot of time experimenting with different tables to help provide a greater understanding of how to build, configure, and use the Pivot Table to its full potential. The following is a quick summary of how to create a Pivot Table. But if you really want to become a “Pivot Table superstar” you may want to find more in-depth guides (and do some experimentation by yourself) to gain a fuller understanding of the “ins and outs” of Pivot Tables.
- In the workbook containing the data you wish to use, click anywhere in the table you want to analyze.
- Go to the Insert tab and select the Pivot Table option to open the dialog box.
- All of the data from the table in your worksheet will be automatically selected but this can be altered using the table/range field in the dialog box.
- Next, select the location that you wish to put your new table, again it has a default option which is to create the pivot table in a new worksheet (hint: this is what most people do). Then select OK to set-up your new Pivot Table.
- You now simply need to create your Pivot Table by assigning the fields you wish to compare from the list at the right side of your screen by dragging them into one of the four drop zones below the list, these are:
– Column labels
– Row labels
– Report filter
– Values - As you are dragging these fields into their drop zones, you’ll notice that on the left you are actually creating your own personal Pivot Table.
- The way to get better with Pivot Tables is to continue to play with these “fields/drop zones” variations until you achieve a result that you think looks good, and that fit for your needs.
Some Useful Tips for Pivot Tables
Having your original data well organized and functional is the key to making the most from a Pivot Table, as the computer applies a logical process that will not account for discrepancies in data entry or the inclusion of empty cells and built-in subtotals to the source material.
Performing a simple count procedure as your first task with the Pivot Table can help you ensure that all of the information you wish to include has been incorporated and that the data is being read correctly from the drag and drop.
Also you’ll notice that when you create your Pivot Table, new “Pivot Table-specific tabs and ribbons” will appear at the top along with the normal tabs and ribbons. There are a ton of great options here that will allow you to customize your Pivot Table and make it look more presentable from an aesthetic point of view.
