Is there an easy way to fill the empty cells left by row fields in multiple columns?

Problem

When you turn a pivot table into hard data, you are left with several blank columns. The previous method works fine for one column, but it could get tedious if you have to fill in several columns, such as with the pivot table shown in Figure A.14.

Figure A.14. You will want to fill in all blanks in columns A through C.

Solution

This solution is not as intuitive as the previous method, but it works nicely when you have several columns of blanks to fill.

You will select a range in columns A, B, and C that extends from the first row with blanks to the row just above the grand total. In the present example, this is A3:C1009.

Select Go To from the Edit menu. In the lower-left corner of the Go To dialog box, click the Special button.

The Go To Special dialog box is a powerful feature that allows you to modify your selection based on various conditions. In this example, choose the option for Blanks, as shown in Figure A.15.

Figure A.15. Using the Go To Special dialog box allows you to select all the blank cells to be filled.

The result will be that only the blank cells within your selection will be selected.

Enter a formula that will copy the pivot item values from the cell above to the blank cells. You can do this with four keystrokes, but it helps if you don't look at the screen while you perform them. Type an equals sign, press the up-arrow key, and then hold down the Ctrl key while pressing Enter.

The equals sign tells Excel that you are entering a formula in the active cell. Pressing the up-arrow key points to the cell above the active cell. Using Ctrl+Enter tells Excel to enter a similar formula in all the selected cells instead of just the active cell. As you can see in Figure A.16, you will have entered a formula to fill in all the blank cells at once.

Figure A.16. With just a few keystrokes, you have the result you are looking for.

You still will want to convert those formulas to values. However, if you attempt to copy the current selection, Excel will present an erroryou cannot copy a selection that contains multiple selections. By using Go To Special Blanks, you actually selected many areas of the spreadsheet.

You will have to reselect the original range of A3:C1009. You can then use Ctrl+C to copy and use Edit, Paste Special, Values to convert the formulas to values.

This method provides a quick way to easily fill in the outline view provided by the pivot table.