When you create a pivot table in Excel 2010 or Excel 2007, the row labels are in a compact layout – all the headings are listed in column A. You can change the Report Layout setting, to either Outline form or Tabular form, so each row field is in a separate column. Here’s the same [...]

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

This example is based on the Work Orders workbook, used in my book, "Beginning PivotTables in Excel 2007." If you’re working through the book, this video shows the steps outlined starting on page 46. In the video, we create a pivot table that’s based on a formatted Excel Table. Then, the Values field is changed [...]

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

If you rearrange the fields in a pivot chart layout, the related pivot table changes too. Unfortunately, there’s no setting you can change if you want the pivot chart and pivot table to work independently. As a workaround, you can create a second pivot table, based on the first one, and arrange it as you’d [...]

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

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

A common question is “How can I show text in a pivot table’s values area, instead of numbers?” For example, if you add the Region field to the Values area, you’d like to see the region’s name, instead of a Count Of Region number. If you use Crosstab queries in Microsoft Access, you might have [...]