You probably sing your own songs about the joy of pivot tables, while you work in Excel every day. If you want to add to your pivot table song repertoire, here is a new one for you. The students in this video sing their original composition, Pivot Tables Make Everything Just Right. It’s got a [...]

The Excel PivotPower add-in on the Contextures website has been updated. The PivotPower add-in makes it easier to change the summary functions in a pivot table, or add protection, and has several other helpful commands. After you install the add-in, it shows up as a drop down list on the Excel Ribbon’s Add-Ins tab. In [...]

Pivot table macros can run painfully slowly, even if you have turned off screen updating and disabled events. To make the code run faster, you can try adding a line that turns off automatic updating in the pivot table. For example: Set pt = Worksheets("PivotSales").PivotTables(1) pt.ManualUpdate = True At the end of the code, you [...]

Instead of creating formulas in the source data for your pivot tables, you can create formulas in the pivot table, by using calculated fields and calculated items. In this example, we’ll create a calculated field, to show the number of tasks per hour that are completed by each worker. In the source data, each record [...]

When you have a long list of fields in an Excel 2003 pivot table, it can take a long time to drag them into the pivot table layout, using the PivotTable Wizard. For a quicker way to add the fields, don’t go into the Layout screen in Step 3 — just click the Finish button. [...]

If you need to sum specific items in a pivot table field, you can create a calculated item. For example, if your pivot table contains an Order Status field, with four types of status: Shipped Pending Backorder Canceled You could create a calculated item, named Sold, that sums the units sold, for orders with a [...]

One of the benefits of using an Excel pivot table to analyze your data, is that you can use filters to focus on a specific part of the summarized results. Hiding some of the data with filters can be misleading though, if you can’t tell that filters have been applied. If you apply a filter [...]

If you create a pivot table in Excel 2007 and later versions, formatting is automatically applied by the PivotTable Styles feature. This feature makes it quick and easy to format your pivot tables, and helps you stay consistent with the Theme settings in your Excel file. Problems with Format Copying Occasionally, you might want to [...]

In some pivot tables, you might want to add comments to a few cells, to help the users understand what the results mean, or to explain the different pivot fields. However, if you add comments to a pivot table, you’ll run into a couple of problems. Tooltips Block the Comments One problem with comments in [...]

A powerful feature of pivot tables is that you can filter a pivot field, to see specific results. However, if you apply a different filter to that pivot field, the first filter is removed. For example, if you filter a Row field for region names starting with "East", and then add a Top 10 filter, [...]