If your pivot table is based on a large data source, it might respond very slowly when you add fields or move fields to a different area of the pivot table. To improve performance, try the following tips: Remove any pivot table styles and any other formatting, such as conditional formatting that you applied to [...]

In Excel 2010, you can use the free PowerPivot add-in to create a report from multiple Excel workbooks or worksheets. With PowerPivot, it’s easy to connect the tables using the Primary and the Foreign key, such as ‘ProductID’ in a Sales table and a Pricing table. In some cases though, you might want to combine [...]

If you’re working with pivot table calculated fields in Excel VBA programming, or trying to use the Orientation property for data fields, you might encounter some problems. Here’s how I managed to work around an error that occurs when you try to remove a calculated field from the pivot table layout with Excel VBA. Strange [...]

To create a pivot table from data on different worksheets, you can use a Multiple Consolidation Ranges pivot table, but the result is a pivot table with limited features and functionality. Previously, Excel MVP Kirill Lapin shared his macro for creating a standard pivot table from data on different worksheets. Now Kirill has expanded that [...]

Previously, we’ve looked at the pivot table summary functions:  Count, Average, Min and Max and Product. In this article we’ll look at the StdDev and StdDevp summary functions. StdDev Function and StdDevp Function Like the STDEV.P and STDEV.S worksheet functions, the StdDev and StdDevp summary functions calculate the standard deviation for the underlying data in [...]

Previously, we’ve looked at the pivot table summary functions:  Count, Average, Min and Max and Product. In this article we’ll look at the Var and Varp summary functions. Var Function and Varp Function Like the VAR.P and VAR.S worksheet functions, the Var and Varp summary functions calculate the variance for the underlying data in the [...]

The Pivot Table Wizard isn’t available on the Ribbon in Excel 2007. To open the Pivot Table Wizard, you can use the keyboard shortcut — Alt + D, P — as described in the article on creating a pivot table from multiple sheets. Another option is to add the Pivot Table Wizard button to your [...]

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 numeric data field to the Values area in a pivot table, the summary function defaults to either Sum or Count. You can change that field to use one of the other summary functions, such as Average. The Average Function The Average function in a pivot table works like the AVERAGE function [...]

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