How to Create a Pivot Table in Excel 2007

Audio Transcript

If you have a spread sheet with a large amount of data — like a year's worth of inventory management or sales figures — you can create a pivot table in Microsoft Excel to help you look at specific sections or identify key trends.

First, place your courser anywhere in your existing spreadsheet. Then click on the insert tab in the top navigation bar and click on the button for pivot table. Excel will automatically select all the data in your worksheet and show you in a dialog box the cell range it has selected. Then at the bottom of the dialog box, you can choose to create your pivot table in a new worksheet within the same Excel file or in another location.

Generally you're going to choose a new work sheet to keep the pivot table close to your data without having everything crowded on the same page. Initially, before you actually create the pivot table, you'll see a place holder box and the pivot table field list. This list is where you will need to specify to Excel exactly what data you want to present in the pivot table.

I'm going to move the list a little closer to the data so you can see how it builds. The different columns from your original spreadsheet are listed in the top half of the pivot table field list. You don't need to use all of these in your table, you can simply drag any fields that you want made visible down to the appropriate area in the bottom of the list.

So the first thing I'm going to do is tell Excel which values to summarize. This might be one value, like the total quantity ordered of a particular item or it can be multiple values. In this example, I'm going to choose just one item here, the salary column. You'll see on the spreadsheet that Excel has started to build your pivot table. Next, I'm going to tell Excel how to subgroup the different values.

In this case, I'll do it by location and also by department. Just like before, you're going to grab the field in the top of the pivot table field list and drag it to the appropriate region in the bottom. There, you can see the changes also reflected in my pivot table in the spreadsheet. Obviously I can start to rely on this pivot table to analyze some of my data. For example, I can see the total salary expenditure by city.

If I want to make any changes to the data that's visible in the table, all I have to do is grab the appropriate field in the list on the right, and drag it outside of the list to remove it. The pivot table will automatically reflect the change. I can also move different fields around to look at the data from different angles. So for example, the location field that I just removed from the row labels, I'm actually going to grab with my mouse and drag it into the column labels. Now I can analyze the total salary expenditure for each department, in each location. If I want to look at even more detail for the data that I have available, I can keep adding appropriate fields to the columns and rows. For example, I'm going to drag and drop the gender field into the row label section. And now I can see how the salaries are allocated by gender, as well as by department and location.

There are many more ways you can look at your data in a pivot table, including adding filters to further narrow down the information for analysis. Try experimenting with the different field options to see how using a pivot table in Microsoft Excel can help you work more effectively.

dummies

Source:http://www.dummies.com/how-to/content/how-to-create-a-pivot-table-in-excel-2007.html