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

You can create a calculated field in a pivot table, to perform calculations based on the Sum of a pivot table field. For example, this formula would calculate a bonus based on the total number of units sold for each product. If 60 or more were sold, then the salesperson would receive a 3% bonus [...]

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

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

Download now or watch on posterous ZoomSelection.avi (987 KB) Did you know that you can quickly zoom in to focus on the selected cells in Excel 2007? This very short video shows you how. Posted via email from contextures’s posterous

This short video shows you a quick technique for naming a range of cells in Excel. Posted via email from contextures’s posterous

Sometimes it’s quicker to use a keyboard shortcut, instead of the mouse, to accomplish a task in Excel. Here are some of the keyboard shortcuts for working with pivot tables in Excel 2007 and Excel 2003. Excel 2003 Pivot Table Keyboard Shortcuts Shortcut Action Ctrl + Shift + * Select entire pivot table (not including [...]