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

Normally, a long pivot table prints to the bottom of a page, then continues on the next page. The page break occurs naturally, not at a specific position in the pivot table. You can control the page breaks, by changing the pivot table settings. The Sample Layout In this example, the pivot table has two [...]

When you add a numeric field to the Values area of a pivot table, it usually appears as a Sum. For example, in the pivot table shown below, the Units field became Sum of Units. Occasionally, when you add a numeric field to the Values area, it shows the Count, instead of the Sum. In [...]

If you add new products to your pivot table source data, and refresh the pivot table, the new products will appear in the drop down lists. Sometimes though, the new items appear at the end of the list, instead of in alphabetical order. This problem can occur if you have manually rearranged the items in [...]

If you accidentally delete the Excel 2003 worksheet that has the source data for your pivot table, you may be able to use the pivot table’s Drill to Details feature to re-create it. Extract the Pivot Table Data To re-create the source data in Excel 2003, follow these steps to use the Drill to Details [...]

If your pivot table has column labels, and more than one field in the Values area, you might want to centre the column labels. For example, in the pivot table shown below, there are Region headings in the Column Labels area. Below each Region label, there are headings for the Qty Sold and $ Sales [...]

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

Usually, things go smoothly when you when you try to create a pivot table. In the screen shot shown below, there’s a list of sales orders, and we’d like to create a pivot table from that data. Everything looks okay in the source data, but when you try to insert a pivot table, a confusing [...]

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

In a comment on this blog, someone asked how to create two pivot tables on the same Excel worksheet. Shown below is a worksheet named Pivot_Reports, with a pivot table on it, based on the data on the Sales_East sheet. We’ll add another pivot table to the Pivot_Reports sheet, based on data on the Sales_North [...]