If there’s a date field in your Excel pivot table, you can use it to focus on a specific date, or a selected date range. To filter the dates, you can use the following filter types:

• Date checkboxes
• Date range entry
• Dynamic date range selection

Using Date Checkboxes

If a date field is in the Row Labels area of the pivot table, do the following to show or hide specific dates.

1. Click the drop down arrow on the Row Labels heading
2. Select the Field name from the drop down list of Row Labels fields
3. In the list of dates, add check marks to show dates, or remove check marks to hide dates.
4. Click OK

Clear a Date Filter

To remove a date filter from a pivot table field:

1. Click the drop down arrow on the Row Labels heading
2. Select the Field name from the drop down list of Row Labels fields
3. Click Clear Filter From [date field name]

Filter for a Specific Date Range

If a date field is in the Row Labels area of the pivot table, do the following to show a specific date range.

1. Click the drop down arrow on the Row Labels heading
2. Select the Field name from the drop down list of Row Labels fields
3. Click Date Filters, then click Between…

   
    

4. In the Between dialog box, type a start and end date, or select them from the pop up calendars.
5. Click OK.

Filter for a Dynamic Date Range

A Dynamic Date Range is one that changes automatically, as time moves forward. For example, Tomorrow, which will represent a different date, every day that you open the pivot table file.

If a date field is in the Row Labels area of the pivot table, do the following to show data from the current month, as a dynamic date range.

1. Click the drop down arrow on the Row Labels heading
2. Select the Field name from the drop down list of Row Labels fields
3. Click Date Filters, then click This Month

Date Filters in the Pivot Table Report Filters

Unfortunately, the Date Range filters and Dynamic Date filters aren’t available in the Pivot Table Report Filters area. If you move a date field to the Report Filters area, only the Checkbox filter type is available.

If you move a filtered date field from the Row Labels area to the Report Filters area, any filtering will be discarded, unless the checkboxes were used to select specific dates.

Watch the Pivot Table Date Filters Video

To see the steps in action, please watch this short video on Pivot Table Date Filters.

______________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

______________

You can group pivot table data, as an easy way to see subtotals. For example, in a pivot table with sales order data, you can group the Order Date field by years and months, to quickly compare the sales totals each month, for a selected year.

In this pivot table example, we have sales data for six stores. Three of the stores are new, and opened in the past 12 months. The other 3 stores are older. You’re doing a presentation to the Board of Directors, and you’d like to compare the sales in the new stores to sales in the older stores.

The new stores are in:

• Los Angeles
• Philadelphia
• San Diego

The older stores are in:

• Boston
• New York
• Pittsburgh

To group the Order Date field, you clicked on one of the dates in the pivot table, then clicked Group Field on the Ribbon’s Option tab.

However, when you click a label in the City field, the Group Field command isn’t available, so you aren’t sure how to create the Old and New groups for the City field.

Group the Selected Items

The Group Field command is only available for date and number fields in the Row Labels or Column Labels area of the pivot table. Because the City names are entered as text, you can use a different method to group them.

1. Manually select the cities you want in the first group. To select nonadjacent cities, hold the Ctrl key, and then click the city names. In this example, we’ll select the cities with the new stores, Los Angeles, Philadelphia and San Diego.

   

2. With the city names selected, on the Ribbon’s Options tab, click Group Selection

   

Name the Group

This creates a new item in the City field, named Group1, with the selected cities listed under that heading. For each of the remaining cities, a heading is created, with its city name.

To change the name of the new group, click on the Group1 heading cell, and then type a name for the group, such as New Stores.

Group the Remaining Items

Next, you can group the remaining stores and name that group.

1. Select the remaining stores, and click the Group Selection command to group them.

   

2. Name the second group as Old Stores.

Alternative Method of Grouping

Instead of grouping the stores in the pivot table, you could add a StoreType field to the source data, and then enter Old or New for each record.

Next, add the new StoreType field as the first field in the Row Labels area. The city names will appear under the correct StoreType heading.

Watch the Excel Pivot Table Grouping Video

To see the steps in action, please watch this short Excel Pivot Table Grouping video.

______________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

______________

In the PivotTable Field List, the fields usually appear in the same order that they appear in the source data. If the pivot table source data has lots of fields, it might be hard to find a specific field in the list.

To make it easier to find the fields in the long list, you would like the field list in alphabetical order.

Sort the List

You can change a pivot table option, to make the PivotTable Field List show the fields in alphabetical order.

1. Right-click a cell in the pivot table, and in the pop-up menu, click PivotTable Options.
2. In the PivotTable Options dialog box, click the Display tab.
3. At the bottom of the Display tab, in the Field List section, select Sort A to Z, and then click OK

Later, if you want to return the field list to its original order, select Sort in Data Source Order, instead of Sort A to Z.

_______________

This week, John Walkenbach, aka Mr. Spreadsheet, used a pivot table report to analyze his website’s Amazon sales for 2009. Here’s a screenshot of the results, showing the top sellers, and number of units sold.

John knows a thing or two about Excel, having written over 40 Excel books, so take a look at the full list, and see if there’s something there that will help you.

Excel 2007 Power Programming with VBA

I bought a copy of John’s Excel 2007 Power Programming with VBA a couple of years ago, and highly recommend it. The book is a great reference when you’re learning VBA, or upgrading from an earlier version of Excel.

In its 1104 pages, the book covers pretty much everything you need to know about Excel VBA, and comes with a CD that contains:

• Sample files for the book’s VBA tutorials
• A searchable PDF version of the book

And best of all, there’s a chapter on Pivot Table Programming, that will help you get started with the new pivot table features in Excel 2007. In that chapter, you’ll discover what’s new in Excel 2007 pivot tables, and learn how to:

• create pivot tables with VBA
• create a complex pivot table
• create multiple pivot tables
• use VBA to change a summary table to a worksheet table

_____________

Note: I earn a small commission if you purchase products through the Amazon links. Thanks!

_____________

Chandoo, at the Pointy Haired Dilbert blog, has posted 5 Excel Pivot Table tricks today. His blog posts are always informative and entertaining, so go and take a look.

The 5 Pivot Table tricks are:

1. Drill Down Pivot Tables
2. Change Summary from Total
3. Slice and Dice Pivots
4. Difference from Last Month
5. Calculated Fields in Pivots

Did you know all those pivot table tricks? Learn anything new?

______________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

______________

The team at PowerPivot for Microsoft Excel 2010 created the Excelerators Quiz, where you can test your Excel skills. To make the challenge more exciting, they’re sponsoring a giveaway on the Contextures blog (for USA residents only).

The blog giveaway prize has a total value of over $250, and will include a Dell ST2310 23 inch flat panel monitor, keyboard, and mouse.

How to Enter

To enter the giveaway, after you take the Excelerators Quiz, go to the Contextures blog, read the rules, and add a comment. In your comment:

• Create your own unique question for the Excelerators Quiz.
• Make your question multiple choice, with the correct answer as one of the four options.

The Alpha Geek Challenge

The PowerPivot team has also launched an Alpha Geek Challenge for more advanced excel geeks. Donald Farmer will host a PowerPivot competition in which the Grand Prize winner will receive an all-expenses paid trip to the 2010 Microsoft BI Conference in New Orleans, LA in June.

After you finish the Excelerators Quiz, and enter the Contextures blog giveaway contest, see how you do in the Alpha Geek Challenge!

_____________

After you create a calculated field in an Excel pivot table, you might want to remove it from the pivot table layout. You can temporarily remove a calculated field, or you can permanently remove it.

In this example, the pivot table has a calculated field named Bonus. It appears in the Values area as Sum of Bonus. You could hide the Bonus calculated field, or delete it from the pivot table.

Temporarily Remove a Calculated Field

To temporarily remove a calculated field from a pivot table, follow these steps:

1. In the pivot table, right-click a cell in the calculated field. In this example, we’ll right-click the Bonus field.
2. In the popup menu, click the Remove command that shows the name of the calculated field.  

   

The calculated field is removed from the pivot table layout, but remains in the PivotTable Field List.

Later, you can add a check mark to the calculated field in the PivotTable Field List, to return it to the pivot table layout.

Permanently Remove a Calculated Field

To permanently remove a calculated field, follow these steps:

1. Select any cell in the pivot table.
2. On the Ribbon, under the PivotTable Tools tab,  click the Options tab.
3. In the Tools group, click Formulas, and then click Calculated Field.
4. From the Name drop down list, select the name of the calculated field you want to delete.

   
    

5. Click Delete, and then click OK to close the dialog box.

______________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

______________

In an Excel 2007 pivot table, you can filter a date field by using the Date Filters option in the Row Labels drop down. For example, you can select all the dates between two specific dates, or filter for a dynamic range, such as Last Week.

However, when you use the row label date filters, you can only select a consecutive date range, or one specific date. You can’t select two or more nonconsecutive dates.

For example, in the screen shot above, the Work Date field is in the Row Labels area, and Labor Hours (Sum of LbrHrs) is in the Values area. In the Work Date field, you can’t use the Date Filters option to select to select January 13th, 17th and 21st.

Manually Select the Dates

Instead of using the date filter options, you can manually select items in the Row Labels drop down list. To manually filter dates in the Row Labels filter list, follow these steps:

1. In the pivot table, click the arrow on the Row Labels heading.
2. In the list of dates, remove the check mark from Select All, to remove all the check marks from the list.
3. Add check marks to the dates you want to see, and then click OK.

The pivot table now shows results for work done on the selected dates, and you can focus on that data.

______________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

______________

Sometimes when you add fields to the Values area of a pivot table, they appear as Count of Field instead of Sum of Field. In the screen shot below, the Quantity field shows the COUNT, and the TotalPrice field shows the SUM.

COUNT Summary Function

The COUNT summary function is the default if a field in the pivot table’s source data contains blank cells, or cells with text. For example, in a column of invoice dates, someone might have mistyped a month name, so it isn’t recognized as a valid date. Or, in a column of sales quantities, you might have typed “N/A”, instead of a number, or maybe you left a blank cell to fill in later.

In these cases, where the source column contains blank cells or text, the summary function defaults to Count; otherwise, it defaults to Sum.

Change the Summary Function

You can manually change the summary function from Count to Sum, after the Values fields have been added. Or, to make things easier, you can run a macro to change the summary function.

This sample macro changes all the Values fields in the first pivot table on the active sheet to use the Sum function (xlSum). The code refers to the pivot table by index number (1), instead of using a specific name ("PivotTable1"), and that makes the macro more flexible.

You can type this code on a regular code module in your workbook, and run it when you want to change the summary functions for all the Value fields.

Download the Sample File

You can download the Excel 2007 file with the Change Summary Function to Sum sample code.

The SumAllValueFields macro is stored on the modPivot code module in the sample file. You can run the macro in the sample file, or copy the Excel VBA code to a regular code module in another workbook, and run it there.

Pivot Table Add-in

Another option is to use my free pivot table add-in, that already has the summary function code in it. There’s a feature that changes all the Value fields to SUM, or any other summary function that you’d prefer. For example, you can change all the field to AVERAGE or COUNT, instead of SUM.

1. After you download and install the pivot table add-in, select any cell in the pivot table.
2. Then, on the Ribbon’s Add-Ins tab, click the Pivot drop down.
3. Click Data Fields, then click the Summary function that you want to use. All the Value fields in the selected pivot table will change to that summary function

______________

P.S.: For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

______________

When you create a pivot table in Excel 2007, a default PivotTable style is automatically applied to a pivot table. For example, in the screen shot below, the pivot table uses the Pivot Style Light 16.

Remove the Pivot Table Style

In some workbooks, you might prefer to have a pivot table with no fill color or header formatting.  In the PivotTable Styles gallery, you can apply a special style to remove the existing style.

Follow these steps to clear the PivotTable style:

1. Select a cell in the pivot table.
2. On the Ribbon, click the Design tab.
3. In the PivotTable Styles gallery, click the first style, None, at the top left of the Light styles.

Pivot Table Style Removed

After you click the None option, the existing style is removed, and a thin border remains around the pivot table sections. The worksheet gridlines, if displayed, are not visible within the pivot table.

Also, when no PivotTable style is applied, the preview function does not work if you point to a different style in the PivotTable Styles gallery.

Clear the Pivot Table Style

Another way to remove the PivotTable style is to click Clear, at the bottom left of the PivotTable Styles gallery.

______________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

______________