Category: Formatting

It’s a little thing, but it can be frustrating if your pivot table column widths keep changing. In the screen shot below, I’ve made column B narrow, so the pivot table takes up less room across the worksheet.

But, when I select a customer name, instead of showing all the customers, the column width adjusts to fit the selected name.

Now, column B is much wider, and it’s hard to read the numbers in that column, because they’re off to the right of the heading.

This automatic adjustment of the column width is useful sometimes, but for other pivot tables, where the column layout is fairly static, it’s not so helpful!

Change the Autofit Setting

If you have adjusted your pivot table column widths, and you want them to stay that way, you can change a setting in the pivot table options.

1. Right-click any cell in the pivot table, and click PivotTable Options
   • 
2. In the PivotTable Options window, click the Format tab
3. In the Format section, remove the check mark from Autofit column widths on update
   • 
4. Click OK, to close the PivotTable Options window.

With the Autofit setting turned off, the column widths will stay the where you set them.

If you want the Autofit feature back on at any point, go back to the PivotTable Options, and add a check mark for Autofit columns.
____________

When you add fields to the value area in a pivot table, custom names are automatically created, such as Sum of Quantity or Count of Customer.

Excel won’t let you remove the “Sum of” in the label, and just leave the field name. However, you can change the heading to the field name, plus a space character, to work around this problem.

In the screen shot below, the heading has been changed to [space]Qty.

Change (Blank) Labels

Another formatting fix that you can make is to get rid of the labels that say “(Blank”)”. These appear if cells are blank in the source data, and you add those fields to the row or column labels area.

Excel shows an error message if you just try to delete those labels, but you can use a space character to replace them.

Watch the Video

Watch this short video tutorial to see how to make these changes to the pivot table headings and labels.

________________________

In a pivot table that contains a long list of dates, you can use conditional formatting to highlight a specific date range. In this example, the pivot table contains forecast data for 2.5 years, with dates in the OrderDate field.

When the file opens, we’re usually interested in checking the forecast amounts for the upcoming month. It can take a bit of time and concentration to find those dates in the long list. We’ll highlight the labels in yellow, so they stand out, and are easy to find.

We’ll use dynamic conditional formatting (Next Month), so the highlighting will change each month when we open the workbook.

Highlight the Upcoming Month

Follow these steps to highlight Row Labels where the order forecast date is in the upcoming month. It’s currently August, so the September dates will be highlighted.

1. In the pivot table, remove any filters that have been applied – all the rows need to be visible before you apply the conditional formatting.
• 
2. Select all the dates in the Row Labels that you want to format.
3. On the Ribbon, click the Home tab, and then in the Styles group, click Conditional Formatting.
4. In the list of conditional formatting options, click Highlight Cells Rules, and then click A Date Occurring.
• 
5. In the date range drop-down, select Next Month, and then click the arrow to open the formatting drop-down list.
6. Select one of the formatting options, or create a Custom Format. I selected Custom Format, and used a yellow fill colour.
• 
7. Click OK to close the A Date Occurring dialog box.

The dates from the upcoming month are highlighted, and will stand out in the report when you open the workbook.

 

Conditional Formatting Warnings

This pivot table’s source data won’t change, because we don’t alter the forecast after it has be finalized.

• However, if you apply conditional formatting to a pivot table, and new data is added, it might not be included in the formatted area. Be sure to check the range, in the Manage Rules box for Conditional Formatting, and edit the rule, if necessary.
• Also, if you change the location of the date field, the conditional formatting will not automatically move with that field. You will have to modify the conditional formatting rule, to point to the new location.

___________________________-

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 pivot table in Outline form. The Region field is in column A, with a separate row for each new heading.

Repeat the Row Labels

A new feature in Excel 2010 lets you repeat those row labels, so they appear on every row in the pivot table.

To turn on that feature for all the fields, select the Repeat All Item Labels on the Ribbon’s Design tab.

Here’s the pivot table in Outline form, with repeating row labels.

Repeating Labels for a Single Field

Instead of turning on repeating labels for the entire pivot table, you can apply the feature to a specific pivot table field.

In this example, the Region, City and Store fields are in the Row area, and we’ll turn on repeating labels for the Region field only.

1. Right-click one of the Region labels, and click Field Settings
2. In the Field Settings dialog box, click the Layout & Print tab
3. Add a check mark to Repeat item labels, then click OK

Now, the Region labels are repeated, but the City labels are only listed once.

Watch the Pivot Table Repeat Labels Video

To see the steps for repeating labels in all fields, or a single field, please watch this short Excel pivot table video tutorial.

_________________