|
You probably know how to create a basic pivot table. Here are some obscure pivot table tricks which will help you get the most out of pivot tables.
Pivot Table Wizard 
|
The Basics: Select a cell in an Excel list, Choose Data > Pivot Tables, and Excel will walk you through the pivot table wizard. Here on step 3 of 4, you drag fields to the row, column, data, or page section of the pivot table. |
At right, you can see the familiar old basic default pivot table. Regions and products are sorted alphabetically, totals by region. But there is more available than this! The trick is to double click a field name when you are in Pivot Table Wizard step 3 of 4. For example, if you drag the field Sales into the Data area twice, and double click on the second instance, you get the dialog box shown below left. By typing a new name in the Name field ("% of Total") and choosing % of Column from the "Show Data as" field, you can show both sales and the percent of total. Then double click the Region field. On the next dialog, click the Advanced button and you will get the pivot table shown below right. |
 |
 |
 In this dialog, select that region should be descending based on the field Sales. Double click the product field and repeat the same process for the product field. Not shown here, but in the same dialog box is an AutoShow Automatic field where you can specify that you want to see only the top/bottom x members of each group. |
Shown at the right is our "jazzed up" pivot table. The West region is shown on top because they had the highest sales. Within each region, the products are ordered in descending sales order. It shows both Sales and % of Total Sales. All of this was achieved with dialog boxes accessed from a few double clicks on the grey field names in the pivot table. So, the next time you need to create a pivot table, try double clicking the field name tiles to exert some extra control. |  |
Update: If you are using Excel 97 or higher, the new Excel grouping function offers great tips for summarizing columns of dates.
MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
|
