Excel Pivot Table – Filling Blank Cells

Hi all

Pivot table is most common reporting mechanism used by many users for generating analysis reports. When using Pivot Tables frequently you probably realized it is not very handy to copy the data out of the pivot table and then go line by line and fill all the gaps in the columns from Row Fields. In this article, I am posting a simple tip which helps you to fill the row cells with appropriate data from the row on the rolled up level.

Note: Copy the pivot table contents and “paste special” into a new worksheet and use that worksheet for this purpose.

1. Select all cells you need to append with data. You can select the entire pivot table including the non-blank cells.
2. Press Ctrl + G. This open the Go to dialog in Excel.
3. Press Alt + S to open the Special sub dialog in Excel.
4. Press “k” on the keyboard (or) select the option “blanks”.
5. Press Enter to close the dialogs. The blank cells will be selected.
6. Hit equals “=” key.
7. Press the “Up” arrow.
8. Hold down Ctrl and hit enter. There will be a formula inserted in all blank cells.
   (Hitting equals and the up arrow you tell to Excel you want the blank cell to be just like the cell above it. By holding down Ctrl and hitting enter you are placing the same formula in every selected blanks cell.)

Hope this helps many folks who use pivot tables for reporting purposes.

Until next post!