In recent blog posts, we’ve looked at the pivot table Count function, the Average function, and the Min and Max functions. In this article we’ll look at the Product function. Product Summary Function The Product summary function shows the result of multiplying all the underlying values in the Values area. The result is the same [...]

In recent blog posts, we’ve looked at the pivot table Count function and the Average function. Now we’ll look at two more functions, that are closely related — Min and Max. Max Summary Function The Max summary function shows the maximum value from the underlying values in the Values area. The result is the same [...]

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 [...]

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 [...]

Sometimes it’s quicker to use a keyboard shortcut, instead of the mouse, to accomplish a task in Excel. Here are some of the keyboard shortcuts for working with pivot tables in Excel 2007 and Excel 2003. Excel 2003 Pivot Table Keyboard Shortcuts Shortcut Action Ctrl + Shift + * Select entire pivot table (not including [...]

You can group the dates in a pivot table in different increments, such as by year and quarter. However, the grouping options are based on the calendar year, and there are no options for grouping pivot table dates by fiscal year. If you want to group the dates by your company’s fiscal year, which starts [...]

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 [...]

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 [...]

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 [...]