How to make Pivot Table field labels fill downwards and not be blank

This tip for Excel shows you how to remove the annoying blank space in pivot tables between items

Often when you make a a pivot table you’ll have the image below if you’ve gone for the classic pivot table layout.  If you have more than one row dimension in your pivot table then you’ll see that the earlier field is blank in every row until it changes.  Often there are times when this is unhelpful so here’s how you can fix this in Excel 2010.

 

Pivot Table with blank rows

Pivot Table with blank rows

 

To fix this problem simply right click the field you want to fix and select Field Settings.  Choose the second tab,’ layout and print’ and then tick the box Repeat Item Labels.  You’ll now have the below without any blanks.  This tip is very useful for if you want to do complicated vlookups of pivot table data where a blank row would have normally caused a problem.

Pivot Table Item Labels

Pivot Table Item Labels

Pivot Table Item Labels

Pivot Table Item Labels

 

Leave a Reply

  

  

  


9 − = four

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>