Fill the Empty Cells Left by Pivot Table Row Fields

April 2nd, 2012 by datapig Leave a reply »

It's not uncommon to turn pivot tables into hard data by doing a Copy-Past Special-Values on them. This not only leaves you with the values created by the pivot table, but also the pivot table's data structure. Unfortunately, as you can see in this screenshot, this is often not ideal, as the pivot table leaves behind a bunch of empty cells that will have to be filled in before you can use this hard table.

.

In this post, I'll show you two relatively easy ways to fix this issue:

.

Option 1: Implement the New Repeat All Data Items Feature

If you have Excel 2010, you can apply the new 'Repeat Item Labels' functionality. This new feature ensures that all item labels are repeated to create a solid block of contiguous cells. The idea is to implement this feature before you Copy-Past Special-Values.

.

Place your cursor anywhere in your pivot table. Then go up to the Ribbon and select Design -> Report Layout -> Repeat All Item labels.

.

Once triggered, Excel forces the Row Field values to be repeated, giving you a contiguous table of data without gaps.

.

.

Option 2: Use Excel's GOTO Special Functionality

If you don't have Excel 2010, you'll have to use Option 2. This option takes advantage of Excel's GOTO Special functionality. First, select the range you are working with, then go to the Home tab - > Find & Select -> Go to Special.

.

.

This will activate the Go To Special dialog box. From here, choose the option for Blanks.

.

.

The result is that only the blank cells are selected.

This is effectively the same as me holding down the Ctrl key while selecting all the blank cells one by one with the mouse.

.

.

At this point, you can enter a simple formula that essentially copies the value from the cell above. This is done with two keystrokes:

Type an equal sign

Press the up-arrow key

The equal sign tells Excel that you're starting a formula in the active cell, while pressing the up-arrow key points to the cell above the active cell.

.

.

Finally, hold down the Ctrl key while pressing Enter.

Pressing Ctrl+Enter enters the same formula in all the selected cells.

The reason this works is because the formula I entered is a relative reference formula. So every formula that is entered essentially points to the value above it.

.

Now get out there and make me proud.

Advertisement

53 comments

  1. LainyB says:

    Yahoo! You saved my bacon datapig – thank you!!

  2. VeroR says:

    Thank you! I’m at a new job that has an older Excel version and I was worried I would have to wait my time. Didn’t think about this simple yet effective way.

  3. moreno says:

    Life Saver!!

Leave a Reply

Powered by sweet Captcha