Chandoo, at the Pointy Haired Dilbert blog, has posted 5 Excel Pivot Table tricks today. His blog posts are always informative and entertaining, so go and take a look. The 5 Pivot Table tricks are: Drill Down Pivot Tables Change Summary from Total Slice and Dice Pivots Difference from Last Month Calculated Fields in Pivots [...]

After you create a calculated field in an Excel pivot table, you might want to remove it from the pivot table layout. You can temporarily remove a calculated field, or you can permanently remove it. In this example, the pivot table has a calculated field named Bonus. It appears in the Values area as Sum [...]

In an Excel 2007 pivot table, you can filter a date field by using the Date Filters option in the Row Labels drop down. For example, you can select all the dates between two specific dates, or filter for a dynamic range, such as Last Week.   However, when you use the row label date [...]

Sometimes when you add fields to the Values area of a pivot table, they appear as Count of Field instead of Sum of Field. In the screen shot below, the Quantity field shows the COUNT, and the TotalPrice field shows the SUM.   COUNT Summary Function The COUNT summary function is the default if a [...]

When you create a pivot table in Excel 2007, a default PivotTable style is automatically applied to a pivot table. For example, in the screen shot below, the pivot table uses the Pivot Style Light 16. Remove the Pivot Table Style In some workbooks, you might prefer to have a pivot table with no fill [...]

Even though a pivot table is automatically formatted when you create it, you can add your own formatting later. For example, in the pivot table shown below, I’ve added colour to the subtotal rows, and made column B narrow. However, some of that pivot table formatting might be lost if you refresh the pivot table [...]

In Excel 2003, you could drag fields from the PivotTable Field List onto the pivot table layout on the worksheet. In Excel 2007, you can only move the fields to the areas in the PivotTable Field List. In the screen shot below, if you try to drag the Promo field onto the pivot table, the [...]

Occasionally, you might need to change a column heading in the pivot table’s source data. For example, you could change a column heading from Qty to Quantity, to make it easier to understand. However, when you refresh the pivot table after making the changes, the renamed field will disappear from the pivot table layout. In [...]

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