Many times working on a MS-Excel sheet you come across a table where there are multiple blank cells scattered over multiple rows & columns. Having blank cells in your table puts some limitations to various arithmetic calculations such as Countif, Sum etc. These blank cells also make it difficult the data to analyze using the MS-Excel tools such as Pivot Table.
Now, suppose all of these blank cells needs to be filled with a same value i.e. 0. Considering a table having 100+ rows and 10+ columns, it will be very difficult & time consuming to fill all these blank cells with a zero one-by-one.
Don’t worry, there’s a trick using which, above mentioned task would be matter of just few seconds.
Let’s explore this option step-by-step with the help of an example:
Student Attendance Table
Consider you have a table like this containing daily attendance of few students for the month of May 2017.
- Here you can see student names are mentioned in column 1 and dates of the month mentioned in the column 2 onward.
- “P” represents the days on which the student is Present.
- “Blank Cells” represents the days on which the student is Absent.
Now, I want to fill all the blank cells with the number “0” which will denote Absent.
Let’s see how to do it.
Step 1: Select the entire table, click Ctrl+A simultaneously.
Step 2: Press the key F5 & click on the “Special” button
Step 3: Select the option “Blanks” in the new pop-up window & click “OK”
Step 4: All Blank Cells Are Selected
Here you will observe that all the blank cells are selected and non-blank cells are unselected.
Step 5: Enter Zero to all Blank Cells
Now keep the blank cells selected. Don’t click anywhere on the sheet otherwise, the selected blank cells will get un-selected and you will have to follow above steps again.
With blank cells selected, simply type “0”. This will get added to first blank cell among the all blank cells that are selected.
Now that zero is added to first blank cell, click the buttons CTRL+ENTER simultaneously.
Magic! All the blank cells in your table that are selected will get filled with the value “0”.
Step 6: Change Cell Color
Now, that all blank cells are selected, you can also apply the formatting tools such as cell color.
Simply select a cell color from Home -> Font section –> Fill Color.
All the selected cells will be filled with the selected color.
This is how, you can format all the blank cells in your MS-Excel Work Sheet simultaneously without any hassle. Hope you enjoyed this post.
If you have any queries, doubts, topic suggestions, leave a comment below. You can also email us at [email protected].