Blank cells in Excel – how to organize them better way?

Forum to talk about all the latest technologies - ins and outs!!
Post Reply
Admin
Site Admin
Posts: 325
Joined: Tue Sep 25, 2007 12:41 pm

Blank cells in Excel – how to organize them better way?

Post by Admin »

If you have lot of blank cells in your Excel data, there are several ways you can organize them nicely.


First one: Fill the blank cells with 0

Select your data range (A2:C16)


Image


Go to Editing (under Home tab) > click on Find & Select > click on Go to Special...


Image


'Go To Special' Dialogue box opens > select Blanks > click OK.


Image


All blank cells in your data range are now selected.


Image


Just type a 0 (zero) - you will notice that the active cell, in this case A4, is immediately filled with a 0.


Image


Now press Control + Enter. All your blank cells are now filled with 0.


Image





Second one: Delete the blank cells

Do like before:

Select your data range (A2:C16)

Go to Editing (under Home tab) > click on Find & Select > click on Go to Special...

'Go To Special' Dialogue box opens > select Blanks > click OK.

All blank cells in your data range are now selected (like before).


Now right mouse click on any of the blank cells > choose Delete


Image


The small 'Delete' window opens. Now you have to select any of the four options, and it depends on how your data is distributed on the spreadsheet. In this case, I will select Entire row (may be in your case, you will need to select Shift cells left or Shift cells up) > click OK.


Image


So my result is as follows :-)


Image


Don't worry about the light blue shade you still see in the above image, just clik anywhere on your spreadsheet, it will disappear immediately :-)





Number of blank cells:

If for any reason, you need to count the number of blank cells in your data range, then you can use the COUNTBLANK function – it counts the empty cells or blank cells in a specified range.

It's Syntax is the simplest:

COUNTBLANK(range)


So, enter in cell B19 =COUNTBLANK(A2:C16)

As you see, here in the example, I have 18 blank or empty cells.

Image

Good work ahead :-)
Post Reply