ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to use Pivot Tables in Excel 2007 and Excel 2010

Updated on July 12, 2013

Introduction to the powerful and versatile Pivot Table in Excel 2007 and Excel 2010

Hi and welcome to my latest hub on Excel. Today I will examine the pivot table. Pivot tables are a very powerful way of presenting your data. They are far more versatile and flexible than a normal Excel table. It is this versatility that makes the pivot table so useful and also so powerful. With pivot tables:

  • You can take a large amount of data and quickly and easily create focused reports based on any aspect of that data you choose
  • The focus of your pivot tables can also be changed quickly and easily should you require different data to be displayed
  • The table you create can be refreshed easily without the need to recreate or manually change it
  • You can perform a number of statistical tests (SUM, COUNT, AVERAGE, MIN, MAX etc) on the data in your table
  • Filters and sorting can also be used. Pivot tables make it easy to sort a column by the data held in another column

Using a pivot table to summarise an aspect of a larger data source in Excel 2007 or Excel 2010.
Using a pivot table to summarise an aspect of a larger data source in Excel 2007 or Excel 2010. | Source

In today’s example, we will look at data from our team of salesmen. We will create a series of tables to illustrate how versatile and flexible pivot tables can be.

  • First, we will look at sales for January
  • Next, we will examine the top 5 salesmen for the same month
  • Finally, we will show their expenses and also the average expenses for January

Pivot charts similarly are very versatile and work well with dynamic or frequently changing data. They allow you to display the results of a pivot table graphically (and therefore the underlying data source) and are as flexible and versatile as a pivot table. I have a hub that goes into Pivot charts into far more detail, including how to create, configure and refresh them. That hub can be found here:

http://robbiecwilson.hubpages.com/hub/Creating-Configuring-and-Using-Pivot-Charts-in-Excel-2007

Example of a pivot chart created in Excel 2007 or Excel 2010.
Example of a pivot chart created in Excel 2007 or Excel 2010. | Source

Creating a Pivot Table in Excel 2007 and Excel 2010

To create a pivot table, first we need data we are going to use in our new table.

Note: Before creating a pivot table, ensure that each column has a title or Excel use the first row of your data as the column headers

To create a pivot table:

  • Select the data you intend to use to create it
  • Choose Pivot table under the Pivot table button which is in the Tables Group on the Insert tab
  • For Choose where you want the Pivot Table report to be placed, either, select a New Worksheet or a Location on an Existing Worksheet

Configuring the range and position of your new pivot table in Excel 2007 or Excel 2010.
Configuring the range and position of your new pivot table in Excel 2007 or Excel 2010. | Source

Now we are presented with the Field list. We use this to build the table.

Building a pivot table using the Field list in Excel 2007 or Excel 2010.
Building a pivot table using the Field list in Excel 2007 or Excel 2010. | Source

To begin with, we will create a simple table containing our salesmen and their sales.

  • We select the check boxes for both and Excel builds a table for us

Using Field list to build a pivot table in Excel 2007 or Excel 2010.
Using Field list to build a pivot table in Excel 2007 or Excel 2010. | Source

Note: If you click away from the table, Field list will close. To re-open it, click back on the table.

Configuring a Pivot Table in Excel 2007 and Excel 2010

Now that we have created our table, we need to configure it to meet our needs. Firstly, let’s remove the Grand Total, as we don’t want to calculate that at this time. To do this:

  • Right click anywhere on the pivot table and select Pivot Table Options
  • On the Totals & Filters tab, clear the Show grand totals for columns
  • We also want to rename the column header from Sum of Sales to Sales for January. Simply click on the cell and rename

Next, we will alphabetise the salesmen’s names. To do this:

  • Select the names (cells H5 to H15) and click the Sort A to Z button on the Data tab in the Sort & Filter group

Now we have our pivot table completed.

A completed pivot table created in Excel 2007 or Excel 2010.
A completed pivot table created in Excel 2007 or Excel 2010. | Source

Using filters and sorting in your pivot table in Excel 2007 and Excel 2010

Now suppose that we want to show the top 5 salesmen in terms of sales. Using the existing pivot table:

  • Click the drop down box for the cell that contains the Salesman header (cell H4)
  • Select Value filters
  • Next, choose Top 10
  • Change the Top 10 to be Top 5

Filtering a pivot table to show the top 5 in Excel 2007 or Excel 2010.
Filtering a pivot table to show the top 5 in Excel 2007 or Excel 2010. | Source

Now our table looks like this:

Note: the drop down box in H4 has changed its icon again to indicate that there are now two sorts on that column.

Cell H4 indicating that Excel is sorting column H in our pivot table in Excel 2007 or Excel 2010.
Cell H4 indicating that Excel is sorting column H in our pivot table in Excel 2007 or Excel 2010. | Source

The table now shows the top 5, but they are not in order from highest to lowest as befits a top 5 list. To resolve this we will add a sort:

  • Again, we click the drop down box in cell H4
  • This time, we select More Sort Options

We want them in descending order and we want them sorted by their sales in January so:

  • Select Descending (Z to A) by: and choose Sales for January

Showing more sorting options available in a pivot table in Excel 2007 or Excel 2010.
Showing more sorting options available in a pivot table in Excel 2007 or Excel 2010. | Source
Sorting completed on our pivot table created in Excel 2007 or Excel 2010.
Sorting completed on our pivot table created in Excel 2007 or Excel 2010. | Source

Note: To find out what filters or sorting has been applied, hover the mouse over the drop down box (e.g. H4) and Excel will display all those that have currently been applied.

Tool tip showing the filtering and sorting applied to column H in our pivot table in Excel 2007 or Excel 2010
Tool tip showing the filtering and sorting applied to column H in our pivot table in Excel 2007 or Excel 2010 | Source

Changing the data displayed in a pivot table in Excel 2007 and Excel 2010

Pivot tables become powerful when you have data that constantly changes or situations where you are called to produce multiple reports from the same data. We now have ours set up, but then our manager comes and asks us to produce a new report this time looking at the salesmen’s expenses for January. In addition, they want to know the overall average across all of the salesmen. To achieve this:

  • First clear the filters on the Salesman column. To do this click on the drop down box and select Clear Filters from “Salesman”
  • Next, clear the tick next to Sales in the Field list and select Expenses

Use Field List to quickly change the focus of your pivot table in Excel 2007 or Excel 2010.
Use Field List to quickly change the focus of your pivot table in Excel 2007 or Excel 2010. | Source
  • Select the drop down box for Sum of Expenses in the Values box
  • Choose Value Field Settings
  • Click Average

Now we want to know the overall average, so we need to add the Grand Total back

  • Right click anywhere on the pivot table and select Pivot Table Options
  • On the Totals & Filters tab, select the Show grand totals for columns option
  • Rename Grand Total (in cell H16) to Average Expenses in January

Alter cell I16 to show a number with two decimal places by:

  • Right clicking and choosing Format Cells
  • Select the Number tab and pick Number
  • Rename cell I4 to Expenses in January

Now we have the following pivot table that we were able to configure quickly and easily:

Pivot table created quickly and easily from an existing pivot table in Excel 2007 or Excel 2010.
Pivot table created quickly and easily from an existing pivot table in Excel 2007 or Excel 2010. | Source

Refreshing a pivot table in Excel 2007 and Excel 2010

Another key strength of pivot tables is how it deals with changing or dynamic data. Suppose due to an administrative error, Thomas’s expenses were 3,600 and not 3,500. If you used normal tables, you would have to update them manually. To update a pivot table:

  • Select the pivot table
  • Right click and choose Refresh

Excel will check the data source for the table and update any cells automatically

Changing the Data Source of your pivot table in Excel 2007 and Excel 2010

Should the need arise; you can easily change the data that Excel uses to create your pivot table to do this:

  • Select a cell in your table
  • Navigate to the Options tab which is part of the PivotTable Tools group of tabs. Click the Change Data Source button in the Data group

You can now adjust the cells you want to connect your table to.

Moving or deleting a pivot table in Excel 2007 and Excel 2010

If you are not happy with the position of your pivot table:

  • Simply click on your pivot table
  • Select the Options tab and the click the Move PivotTable button which is in the Actions group

If you decide that you are not happy with your pivot table and you want to start again and delete it:

  • Select the entire table
  • On the Home tab, in the Editing group, click the Clear All button and the pivot table will be deleted

Conclusion

Pivot table are immensely powerful and versatile. They offer several advantages over a standard Excel table:

  • It is easy to create focused reports from large amounts of data
  • Those reports can be quickly and easily changed to show different data as we showed above
  • Pivot tables work brilliantly with dynamic or frequently changing data as they can be easily refreshed
  • You can perform a number of statistical tests on the data in your pivot table including (SUM,COUNT, AVERAGE, MIN, MAX etc) which allows you to perform additional analysis
  • Filters and sorting can be used to give users of your tables more useful information from the existing data

Thanks for reading my hub on creating and configuring pivot tables in Excel. I hope you have enjoyed reading my hub as much as I have enjoyed writing it and that you found it useful and informative. Please feel free to leave a comment below.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image
      Author

      Robbie C Wilson 2 years ago

      Thanks so much for your comment, glad that you liked my hub on Pivot tables. Thanks also for pinning them as well :)

    • Kailua-KonaGirl profile image

      June Parker 2 years ago from New York

      Great tutorial! I pinned it to my Excel & Word Tutorials board (https://www.pinterest.com/konagirl/excel-word-tuto...

    Click to Rate This Article