Fill out blank cells in a Pivot table

How to fill down the blank cells in Pivot Table rows, as by default only the grouped field label is shown?

It’s a three step process basically: Selecting all non blank cells, typing a simple reference formula and propagating it in all non blank cells.

1.So copy the pivot table to a different location first. Then select all cells you want to fill out, including the non blank cells, but starting with the first row label cell.

The first trick is to get all non blank cells: Press F5 (goto) or ctrl + G. This open the Go to dialog, which has a special feature hidden under the ‘special’ button to select all the ‘blanks’. That’s where to go first.

Press OK to close the dialogs. The blank cells are now selected.

2.Next step is entering the formula. So double check that the first ‘active’ cell is the one just below your first row label (highlighted in white). Type “=” and press the ‘up’ arrow once.
You now have a formula, which basically is just a reference, for this first cell.

3.Last step is to hold down Ctrl and to hit enter. This is a trick to copy the formula throughout all blank cells.

Source: Here

Posted in Excel Tips & Tricks Tagged with: , , , , ,

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>