ExcelRibbon.Tips.Net ExcelTips (Ribbon Interface)

Counting with PivotTables

Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Counting with PivotTables.

Suppose you have a data table set up in Excel that represents your club membership. In the first column are the names of club members. In the second column are the cities in which the members live. If you want to find out how many people live in each city, there are several methods you can choose. One method is to create a PivotTable.

To create a PivotTable on your data, follow these steps:

  1. Select a cell within your data table.
  2. Make sure the Insert tab of the ribbon is displayed.
  3. In the Tables group, click the PivotTable tool.
  4. Excel displays the Create PivotTable dialog box. (See Figure 1.)
  5. Figure 1. The Create PivotTable dialog box.

  6. In the Range box, make sure your entire data table is selected, then click on OK. Excel creates an empty PivotTable worksheet.
  7. Drag the City field from the field list to the Row Labels area.
  8. Drag the Name field from the field list to the Values area. Your PivotTable is complete.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6160) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Counting with PivotTables.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!


Comments for this tip:

sharaz    17 Feb 2014, 23:21
Pivot table is the best feature in excel.Try to learn more about that if u are having a job related to immense data entry.
Steve Nadel    01 Jul 2013, 10:47
I too love Pivot Tables.

The only thing that annoys me is when you need to sort multiple columns within the Pivot Table. I wind up copying and pasting the end result pivot info onto another part of the sheet and do my multi sort. That leaves the new information static and defeats the whole purpose of the Pivot.

There must be another way. Would love to hear what others here do in that scenario. Thanks.
Mike    30 Jun 2013, 17:44
Pivot tables are the soul of Excel! But why can't you use data in external Excel workbooks. I have +200 files, all with the same structure but with different data, and it would be great to use a Pivot to interrogate them. With 900 mbs of data I cannot combine them into a single file.
Surendera M. Bhanot    12 Jan 2013, 05:37
I find people use Pivot Tables for statistical purposes. I have seen average people rise up in carrer (superceding their seniors) owing to their strong mastry over use of Pivot Tables. The examples on the net are mostly related to sales and marketing and their focus is maily on sales. Even the MS website focusonly on sales and have very limited information on use of Pivot Tables.

Please publish all sort of use of the pivot tables and how one can exploit the data in different ways. You may refer us to the good links on the net to make us familiar with the use of Pivot Tables. Thanks.

JPaules    11 Jan 2013, 08:17
This is how I started using pivot tables. All of the examples for pivot tables dealt with sales. I have nothing to do with sales so I didn't see the value. Then a coworker showed me how I could count with them and my romance began. With pivot tables, not the coworker. :-)

Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 4+5 (To prevent automated submissions and spam.)
          Commenting Terms

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2015 Sharon Parq Associates, Inc.