How to Create a Pivot Table in Excel 2007

Pivot Tables are among the most useful features of MS Excel. If you have a spreadsheet with huge amount of data, analyzing it manually or via filters becomes a bit difficult and tedious.

In this case, Pivot Tables come at your rescue, as they facilitate analyzing and summarizing huge quantity of data easily in just a few clicks. In this article, we will see how to create a basic Pivot Table in 2007 so that you can organize data well for focusing on a particular set of data.

pivot table

How to Create a Pivot Table in Excel 2007

Comprehending Pivot Tables

A PivotTable refers to a summary of data that is required for further analysis. However, as compared to a manually made summary, such a table in Excel is dynamic enough to make you fetch data in an interactive manner. By this, I mean that after creating a Pivot Table, you can easily customize or change its fields to best suit your requirements or to get the desired insights.

create a pivot table

It is just a matter of few clicks for pivoting (rotating) the summary such that the row headings become column headings and vice versa. To recognize this beauty of Pivot Table, let’s create a Pivot Table in the 2007 version of Excel. Although the concept has hardly changed, the manner in which a Pivot Table is created has changed a bit across Excel versions.

Creating a Pivot Table

Let’s assume that you have entered sales transactions in your Excel spreadsheet. Each transaction has fields or columns namely, Month, Date, First Name, Last Name, Package, Sales Amount, Payment Method, and Sales Person. Now, just select any cell that contains this data. Then, select the Insert tab that is present on the Ribbon and click the PivotTable button.

creating a pivot table

Doing so will open the Create PivotTable dialog box. Herein, in the Text/Range field, you will observe that the selected data range is already displayed. In case if it is not, do select the entire range of cells that holds data. Next, you have the option of placing the Pivot Table in the existing or novel worksheet where you have to select the location. If you do not wish to make the spreadsheet appear messy, select to put it in a new worksheet.

Also Read

How to Use VLOOKUP in Excel
How To Make Row Labels in Excel 2007 Freeze for Easier Reading 
Print Only Selected Areas Of A Spreadsheet In Excel 2007 & 2010
Hide And Unhide Worksheets And Workbooks In Excel 2007 & 2010 

The moment you click OK, the PivotTable Field List opens up to the right of the sheet and the PivotTable tools become visible in the Ribbon.

pivot table tools

Now, you can select the fields that you desire to have in the table by dragging them from the Choose fields to add to report list and dropping them into the various boxes below. These boxes are Report Filter, Row Labels, Column Labels, and Values.

organizing report

The table is built as soon as you drag the fields into these boxes. For example, you can drag the Sales Amt field in the Values box and SalesPerson field in the Row Labels box to get the total sales made by per sales person. Now, just add Payment Method in the Column Labels box and Package in the Report Filter box. This will enable you to see payment modes package wise. In short, you can move your data to best organize your summarized report.

filtering months

You can also change the PivotTable Field List view by clicking the drop-down button on the top-right corner in the right pane. There are many options that you can play with, to change the look and feel of the table as well. So, happy pivoting!

Did you enjoy working with Pivot Tables in Excel 2007? Do share your experience with us!