Loading
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:

More Power! For some people, the prospect of creating macros can be scary. Those who conquer their fears, however, find they become much more confident and productive once they learn how to make Excel do exactly what they want. ExcelTips: The Macros is an invaluable source for learning Excel macros. You are introduced to the topic in bite-sized chunks, pulled from past issues of ExcelTips. Learn at your own pace, exactly the way you want. Check out ExcelTips: The Macros 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:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (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

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

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 © 2014 Sharon Parq Associates, Inc.