Separate Pivot Table Field Items with a Blank Row

To make the pivot table easier to read, you might like each item in a pivot table field to be followed by a blank row. You can’t insert the rows manually, because Excel will display an error message if you try that method.


However, in any of the pivot table report layouts, you can format the outer row fields, so each item has a blank row after it.

Note: All the outer row fields will be affected by this setting – you can’t limit it to one or more selected fields.

Add a Blank Row in Report Layout

  1. Select a cell in the pivot table, and on the Ribbon, click the Design tab.
  2. In the Layout group, click Blank Rows, and then click Insert Blank Line After Each Item.


Format the Row

To highlight the blank rows, you can manually add a fill color:

  1. Click at the left of one of the blank rows, to select all the blanks for that field.
  2. On the Ribbon’s Home tab, click the drop down arrow for Fill Color.
  3. Click on the color you’d like in the blank rows.


Please visit my Contextures web site, for more pivot table formatting tips.


This entry was posted in Layout. Bookmark the permalink.

One Response to Separate Pivot Table Field Items with a Blank Row

  1. BennyB says:

    But when I refresh the pivot, the filled blank rows become unfilled (i.e. if filled with solid black, after refresh is white again).

Leave a Reply

Your email address will not be published. Required fields are marked *