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.
Loading
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 35 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:
Change Formatting Based On Your Data! Conditional formatting provides a way for you to adjust the appearance of your data based on the data itself. Discover how to put this amazingly powerful feature to work for you, today. This comprehensive volume is available in two editions. Check out Excel Conditional Formatting today!
Comments for this tip:
Steve Nadel 01 Jul 2013, 10:47
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.
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. :-)