Forum Index
shamskm.com Home
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

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

 
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum
View previous topic :: View next topic  
Author Message
Admin
Site Admin


Joined: 25 Sep 2007
Posts: 272

PostPosted: Mon Jan 30, 2017 10:25 am    Post subject: Blank cells in Excel – how to organize them better way? Reply with quote

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)





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.





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





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








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





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.





So my result is as follows Smile





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 Smile





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.



Good work ahead Smile






*Recommended:

Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.


For comments of suggestions, please contact us: info@shamskm.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group