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

In some workbooks, you might want to allow users to make changes to a pivot table, but you need to protect formulas or data in other areas of the worksheet. If you protect the worksheet and enable pivot table use, users will be able to modify the pivot table, but won’t have access to other [...]

In some workbooks, you might want to prevent users from making any changes to a pivot table. You want them to see the pivot table, but not change it. However, users might need to make changes to data in other areas of the worksheet. If you protect the worksheet without enabling pivot table use, users [...]

When you add multiple fields to the row or column area, the outer fields automatically get subtotals. For example, in this pivot table, Region and Employee are the outer fields, and a Total row was added for each item. Sometimes these subtotals are useful, but in other pivot tables you might want to stop them [...]

If you’ve used calculated items and calculated fields in your pivot table, you can automatically create a list of all the formulas. List the Formulas in Excel 2007 Select any cell in the pivot table. On the Ribbon, under the PivotTable Tools tab, click the Options tab. In the Tools group, click Formulas Click  List [...]

If there are blank cells in one of the fields in your source data, you might want to show a count of the blank cells in the pivot table. In this example there’s a Region field in the source data, and some of the records have no region name entered. In the pivot table, you’d [...]

Usually, when you add a field to the Values area in a pivot table, it automatically uses the Sum function. Sometimes though, a field automatically uses the Count function. You might not understand why different functions are used for two fields that contain similar data. If there are blank cells, or non-numeric data in the [...]

When you create a pivot table in Excel 2007, a PivotTable Style is automatically applied. You always select a different PivotTable Style, so you’d like to change the default PivotTable Style, to save time when you create new pivot tables. Follow these steps to change the default PivotTable Style: Select a cell in the pivot [...]