ExcelTips (Ribbon Interface)
Members
This Site
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
Author Bio
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
Helpful Links
ExcelTips FAQ
Ask an Excel Question
Make a Comment
Free Business Forms
Free Calendars
Subscribe
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:
- Select a cell within your data table.
- Make sure the Insert tab of the ribbon is displayed.
- In the Tables group, click the PivotTable tool.
- Excel displays the Create PivotTable dialog box. (See Figure 1.)
- In the Range box, make sure your entire data table is selected, then click on OK. Excel creates an empty PivotTable worksheet.
- Drag the City field from the field list to the Row Labels area.
- Drag the Name field from the field list to the Values area. Your PivotTable is complete.
Figure 1. The Create PivotTable dialog box.
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:
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Comments for this tip:
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.
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.