Microsoft PowerPivot for Excel 2010 is a data analysis add-in for Excel 2010, and is part of the Office 2010 Beta. You can test PowerPivot in the hands-on Virtual Lab, or in the Office 2010 Beta, if you’ve downloaded that. That’s where I tested PowerPivot last weekend, and described the experience in my Contextures blog [...]

In Excel 2003, and earlier versions, the items in a page field’s dropdown list don’t have check boxes to indicate which items to show and which to hide. In the pivot table shown below, the Product field is in the page area. It’s easy to select one product, such as Chocolate Chip. Or, you can [...]

After you create a calculated item in a pivot table, you might need to change its formula. For example, in this pivot table there’s a calculated item named Sold, in the Order Status field. The Sold item sums the orders with a status of Shipped, Pending, or Backorder. Change the Calculated Item Formula You can [...]

When you add multiple fields to the Row Labels area in a pivot table, subtotals automatically appear for the outer fields. In the pivot table shown below, Region and City fields are in the Row Labels area. Two fields are in the Values area: Sum of Quantity shows the total quantity for each city, and [...]

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

An Excel Table is a new feature in Excel 2007, similar to the List feature in Excel 2003. An Excel Table makes it easy for you to sort, filter, and review your data, whether it’s a few rows and columns, or thousands. You can also use an Excel Table as the source for a Pivot [...]

You can create custom PivotTable Styles in an Excel 2007 workbook, to fine tune the appearance of your pivot tables. If you’ve invested a lot of time in create a custom style, you might like to copy that style to a different workbook. Unfortunately, there isn’t a built-in command that will let you copy PivotTable [...]

To make the pivot table easier to read, you might like each item in a pivot table field to be followed by a blank row. You can’t insert the rows manually, because Excel will display an error message if you try that method. However, in any of the pivot table report layouts, you can format [...]

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