When you add a field to a pivot table’s Values area, Count is the default summary function, if the field contains nonnumeric or blank cells. (If the field’s data are all numbers, Sum is the default function.) The Count function’s name is slightly confusing, because it’s like the COUNTA worksheet function, not the COUNT worksheet [...]

This example is based on the Work Orders workbook, used in my book, "Beginning PivotTables in Excel 2007." If you’re working through the book, this video shows the steps outlined starting on page 46. In the video, we create a pivot table that’s based on a formatted Excel Table. Then, the Values field is changed [...]

In a pivot table, there’s no built-in way to group the pivot table data by both weeks and months at the same time. If you want to show sales data by week, you can group the date field in seven-day intervals. The 7 day grouping works well, but if you try to add grouping by [...]

In an Excel pivot table, you might want to hide one or more of the items in a Row field or Column field. To do that, you can click the drop down arrow for the Row or Column Labels, then remove the check mark for items you want to remove. For example, to hide the [...]

You can create a calculated field in a pivot table, to perform calculations based on the Sum of a pivot table field. For example, this formula would calculate a bonus based on the total number of units sold for each product. If 60 or more were sold, then the salesperson would receive a 3% bonus [...]

There are many built-in PivotTable styles in Excel 2007, but you might not see one that has the exact formatting you need. For example, you might like the heading colors in the Pivot Style Medium 9, but you’d like a different color in the subtotal row. Instead of using one of the built-in PivotTable styles, [...]

When you add a field to the Values area of an Excel Pivot Table, it automatically shows the Sum or Count for all the items in that field. Here we can see the total labor cost for each Service Type. If you add more than one field to the Row Labels or Column Labels area [...]

If you format a column of dates in your pivot table source data, to show as year and month (yyyy-mm), they won’t automatically summarize by year and month in the pivot table. Format the Source Data For example, in the source data shown below, there are several orders for January 2010, and three different dates [...]

With Excel Pivot Table filters, you can limit the results to a specific part of your data. For example, you can view only the sales from the previous month, or see the products with more than $500 in sales. If you’re interested in analyzing the best or worst results in your data, use the Top [...]

If you have an Excel workbook that contains several pivot tables and multiple Excel Tables, you might want to identify which pivot cache each pivot table uses. To figure out which pivot cache a pivot table uses, you can use a macro to check the pivot table’s CacheIndex property and show the index number in [...]