Excel 2007: Your Guide to Effective Pivot Tables
Pivot tables are not new to Excel 2007; in fact they’re included in all versions of the program. But surprisingly not a lot of Excel users are familiar with them. If you’re an avid Excel user don’t miss out on this very useful Excel feature since pivot tables have the ability to transform the way you look at your data.
Pivot tables allow you to easily analyze, summarize, and organize large amounts of data so that you’re able to make better sense of what you’re looking at. As the instructor of our Excel 2007 Training, Bill Kulterman, put it “Pivot tables allow you to find that needle in the haystack.”
Pivot tables in Excel 2007 also remain very flexible and easy to manipulate so that at any time you can adjust the data and reorganize it through the use of filters.
Creating a Pivot Table
When you’re working with Pivot Tables the first thing you need to ask yourself is: What do I want to get out of my data? Or basically, what do you want to see on your Pivot Table.
Start by taking a closer look at your data and make sure that you don’t have any blank rows or columns. Then select the Insert tab on the Ribbon:
Then click on Pivot Table at the far left, and select Pivot table:
A Create Pivot Table dialogue box will come up that will allow you to select all the settings:
First you will get to select a table or range. If you have no blank columns or rows in your spreadsheet, you should already have all the data selected, but you can always double check this before you click okay.
You will also get to choose where you want the Pivot Table report to be placed. If you choose existing worksheet you will need to specify where you want the table to begin. For our example, I’ll put the Pivot Table on a New Worksheet:
Click okay, and there it is! Your Pivot Table is ready to come alive. At the right you’ll notice the Pivot Table Field List which lists your column headers under Choose fields to add to report. Underneath that you will see the different areas that the data can be applied to.
On the left you will see the structure of the Pivot table and this is where your pivot table will appear:
By selecting the different fields you get to choose which information goes into your pivot table. You do this by clicking on the boxes next to the different fields or dragging them to the different areas below:
The Values area is the data field and this is where you decide what you want to analyze. So in our example, if we want to show the total number of sales in the pivot table, this is where we want the sales field to be.
Column labels and row labels determine how many columns and rows you have in your pivot table. So if we want to see particular data in a column instead of in rows all we have to do is drag the field to the appropriate area:
Next we will drag the fields that we want to be able to filter in the report and the data that we want to see displayed in rows:
The pivot table is now ready! We have our Quarters field displayed in columns, we have our Saltwater fish displayed in rows and the data that we’re looking at show us the total number of sales and a grand total:
We can also analyze this data by a particular store since this is the field that we included in our Report Filter area. So at the top of the page our filter allows us to select the store (or multiple stores) and the data that will be displayed in the pivot table will include only what we have selected through our filter:
The pivot table can be manipulated at any time to display the data and analyze it in a different way. So if we don’t want to look at our sales numbers by store, but instead want to be able to filter it out by quarters all we have to do is drag the fields to the appropriate areas:
Now we can filter out our data by Quarters and see how each store performed within each quarter:
We can also filter out the first column so that if we want to narrow down our data and see how a specific product or a group of products performed, all we have to do is click select it:
Your pivot table gives you numerous options and allows you to manipulate your information in every possible way so that every question you might need to ask will receive an accurate answer displayed in an organized, easy to read and make sense of pivot table.
Microsoft Excel 2007 Training – Available Now!
Take advantage of everything the new Excel has to offer with Train Signal’s Microsoft Excel 2007 Training – Available Now!
Our complete Excel 2007 training – beginner to advanced – offers 12+ hours of instructor led video, instructor’s notes, and plenty of Excel exercises to put your new knowledge into practice.
Some of the topics in the training include:
- - Formulas and Calculations
- - Charts, Templates, and Pivot Tables
- - Basic and Advanced Formatting
- - Multiple Workbooks and Collaboration
- - Using Macros and more!
Our Excel 2007 training also covers the Using Microsoft Office Excel 2007 Certification Exam (77-602 MCAS). So take advantage of all the new features and see how much more you can accomplish with your data.