In an Excel pivot table, you might want to hide one or more of the items in a Row field or Column field. To do that, you can click the drop down arrow for the Row or Column Labels, then remove the check mark for items you want to remove. For example, to hide the [...]

There are many built-in PivotTable styles in Excel 2007, but you might not see one that has the exact formatting you need. For example, you might like the heading colors in the Pivot Style Medium 9, but you’d like a different color in the subtotal row. Instead of using one of the built-in PivotTable styles, [...]

In a pivot table, you usually sort the data by the values in a column, such as the Grand Total column. By sorting, you can highlight the highest or lowest values, by moving them to the top of the pivot table. To sort a pivot table column: Right-click on a value cell, and click Sort. [...]

When you add a field to the Values area of an Excel Pivot Table, it automatically shows the Sum or Count for all the items in that field. Here we can see the total labor cost for each Service Type. If you add more than one field to the Row Labels or Column Labels area [...]

If you format a column of dates in your pivot table source data, to show as year and month (yyyy-mm), they won’t automatically summarize by year and month in the pivot table. Format the Source Data For example, in the source data shown below, there are several orders for January 2010, and three different dates [...]

With Excel Pivot Table filters, you can limit the results to a specific part of your data. For example, you can view only the sales from the previous month, or see the products with more than $500 in sales. If you’re interested in analyzing the best or worst results in your data, use the Top [...]

If you have an Excel workbook that contains several pivot tables and multiple Excel Tables, you might want to identify which pivot cache each pivot table uses. To figure out which pivot cache a pivot table uses, you can use a macro to check the pivot table’s CacheIndex property and show the index number in [...]

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

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

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