Tips for Excel, Word, PowerPoint and Other Applications
A Quick and Easy Way to Fill in Blank Cells
Why It Matters To You
Sometimes you'll get data sets that aren't well stacked [see Stacking Data for Better Analysis], particularly when you generate pivot tables with multiple Row elements. In the case of pivot tables, you end up with a lot of blank space between higher level data elements. Fortunately, there is a very easy way to copy data or formulas to blank cells.
How To ...
Here's what the typical pivot table, without subtotals, returns. In order to make this data set ready for analysis, we really want to fill in all of the blanks so that every row of data stands on its own, meaning that every row has a ShipToID, Order#, and Total.
The first thing we're going to do is write a simple formula in cell A3, =A2. This simply says, make the cell equal to whatever is directly above it. Easy enough, right? Once you're finished writing your formula, use CTRL+C to copy it, and then select your entire column of data ... but don't paste it any where yet.
Go to the Main Menu: Edit, Go To (CTRL+G), ...
... Click on Special, ...
... and then select, Blanks. Hit OK
What you should now see, is all of your blank cells selected.
Now, use CTRL+V to paste your formula into all of the blank cells. If you did this right, all of your blank spaces should now be filled with the appropriate information.
One last step. We need to freeze the values. Simply highlight the columns you just filled, copy (CTRL+C), and then paste as values (ALT, E, S, V). You might realize that you could have selected multiple columns of data to make this go even faster, and you would have been right.
Notes
| Last updated | 8/5/08 |
| Application Version | Excel 2003 |
| Author | Michael Kan, based on tips found at Contextures by Debra Dalgleish |
| Pre-requisites | None |
| Related Tips |
Stacking Data for Better Analysis Generating Pivot Tables Without Subtotals |