October 5, 2010

It is many times the case where my clients inherit data that contains many blank cells and to achieve the transformation to a datalist, much cleaning up is required. One of the most useful arrows in your Excel quiver in this case is the GoTo dialogue box. Where you have dispersed data in one column and would like to fill the blank cells, do the following:

1) Select the area that you would like to fill, including the data

Fill Blanks

Fill Blanks

2) Hit the ‘F5’ key and click on the ‘special..’ button in the GoTo dialog box

3) Select the ‘blanks’ radio button and click ‘OK’

Fill Blanks

Fill Blanks

4) You will notice all the blank cells selected and the top selected cell being active

5) Now type ‘=’ and then hit the up arrow once to point the active cell to the cell above it

6) Hold the ctrl button down and hit ‘Enter’ and all the blank cells are now populated

Enjoy this trick as it will save you many painful hours.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: