Go To Special Series. Select Blanks

Select Blanks:

Welcome to the third post of this series – Select blanks. Click here to read to navigate to go to special and to select comments or Click here to read to select constants and formulas.

Why Select Blanks?

I often use this option in Excel and I sincerely thank Microsoft for having this option in Excel. I must admit I hate to see blank cells in my data. It is quite common that we see blank cells in a large set of data, especially for people who download data from web and people who get a pivot table paste as values as his input for further processing. One might want to get rid of blank cells or put a constant (Sometimes, I make blank cells as zero) or fill the blank cells with a common formula etc.

Bear in mind to select a range of cells while selecting blanks or else by default excel selects all blanks in the used region. I mean by used region, from A1 to the last used cell.

Navigate to Go To Special - Blanks

Click here to learn navigation.

Keyboard Shortcut: Press F5, then Alt+S+K.

Tricks using select blank cells

1) Delete rows/columns or shift cells left/up: After selecting blanks, press CTRL + -. You can see the dialog box like below.

Now, from the above options choose to shift cells or delete entire row or column. I don’t know how others use it but on an average I work on it on a daily basis. Imagine the type of data I’m working on!!

2) Fill a formula or a constant in all blank cells: I loved it, when I first learn it. I’m sure you would love it too. Assuming, you have data similar to above example. Now you want to fill in the blank cells with a value from the immediate above cell. How do you do that? Here we go, select a range and select blank cells (You know how to select blank cells now!). Now, type = and select the above cell (Shown below in the example) and press CTRL+Enter. It’s magic. You are done and you have successfully filled all the blank cells with the value of cell from it’s immediate above cell. 

Now, instead of formula you want to put a constant. Say for example, I want all the blank cells in my range to be “VaraK”. Easy, select blank cells. Type “VaraK” and press CTRL+Enter.

Share your experience:

Do you use this option more often? Share your experiences, how do you deal with this type of data. Have another tip do this stuff? I’Also, do not forget to leave your comments on this post. I learn a lot from you all.

I’ll be posting soon, fourth post of this series “Go to Special – Current Region and Current Array”.

Links Related to this series:

Go To Special Series. Select Comments
Go To Special Series. Select Constants and Formulas