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.)
Figure 1. The Create PivotTable dialog box.
- 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.
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.
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: