Pivot Table Summary Functions: Product

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

Pivot Table Summary Functions: Max and Min

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

Pivot Table Summary Functions: Count

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

Create an Excel 2007 Pivot Table and Show Averages

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

Problem With Pivot Table Calculated Field Totals

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

Excel Pivot Table Keyboard Shortcuts

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

Grouping Pivot Table Dates by Fiscal Year

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

Pivot Table Date Filters

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

Manually Group Pivot Table Items

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

Change the Pivot Table Field List Order

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