Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Prevent Blank or Missing Entries in Cells in Excel?
We all know that Excel is a very robust application offered by Microsoft which we use to analyze, organize and visualize our data to extract useful information. So let us assume that we are working in excel sheets. Now while working in excel sheet, there are chances that we leave blank spaces, for eg. leaving blank rows. So from this the built in functions of excel does not work properly.
So here you will be going to learn how leaving blank spaces in cells can lead to various problems and so we will learn how to prevent blank or missing entries in cells in excel.
Some Problems Created by Empty Cells
Our data will not be analyzed properly. Generally it will lead to inaccurate results.
It will also lead to various formatting issues.
Also an excel sheet with blank spaces becomes difficult to understand for people with whom we are sharing our file.
Data Example
Now to illustrate this problem we will take an example of a simple dataset. The dataset comprises a list of some students with their course and obtained CGPA.
Methods to Prevent blank or missing entries in cells in Excel
As in the above screenshot it is clear that data is associated with students. There are many methods by which we can prevent blank or missing entries in cells in excel. In this tutorial we are going to present 3 very common and best ways by which we can prevent blank or missing entries in cells in excel. The methods which we can use protect this data are as follows :
Using Data Validation
This is one of the very simple methods which we can use to prevent blank or missing entries in cells in excel. Data validation is a very powerful feature in excel which is used to set rules and control what users can enter into the cells. To use this method following are the steps :
First of all, select the cells on which we want apply data validation
After selecting the cells, go to the data tab.
In the data tab under the Data tools group, click on Data validation.
Now in the settings tab, in the Allow dropdown menu, select the Custom option.
Now in the Formula tab, use the formula as ” =LEN(A3)>0 “ (here we are using the A3 because in our case this is our first cell).
Now click OK to apply data validation.
Using Conditional Formatting
In the previous method we have seen how we can prevent blank or missing entries in cells in excel using data validation. Now we are going to learn another simple method by which we can prevent blank entries in cells. This method is called Conditional formatting. To use this method following are the steps :
First of all select the cells on which we want to apply conditional formatting upon.
Now go to the home tab.
In the home tab under the styles group, go to the Conditional formatting.
Select the New Rule option from the dropdown menu of Conditional formatting.
Now after this go to the “Use a formula to determine which cells to format” under the heading “Select a Rule Type”.
In this dialog box we can see that there is a field available as “Format values where this formula is true”. So in this field enter the formula as : =L1(A3)=0 (here we are using the A3 because in our case this is our first cell).
Now we can choose the formatting style that we want to apply to the blank cells.
Now at last, click the OK button to apply the conditional formatting to the selected cells.
Using Formulas
Formulas are a very powerful feature of excel which is used to perform various mathematical calculations, return values or information, change the content of the cell and many more. So to prevent blank or missing entries in cells in excel we can use some specific formulas. Some examples of these formulas are :
IF function : If is a conditional function which is used to print one value if the condition is true and it prints the second value if the condition is false. So we can easily use this function to display a message if the cell is left blank or empty. For eg. "=IF(A3="", "Value is missing", "")".
COUNTA function : COUNTA function is used to count those cells which contain some values. So we can easily use this function to count those cells which contain some values. For eg. "=COUNTA(A3:A9)".
ISBLANK function : ISBLANK function is used to check if the cell is empty or not. It returns true if the cell is empty and vice versa. Thus we can easily use this function to count those cells which are empty. We have to use this function with the IF function. For eg. "=IF(ISBLANK(A3), "Please enter a value", "")".
Conclusion
Hope you have learnt and understand how we can prevent blank or missing entries in cells in excel. Here we have learnt 3 methods which are mentioned below :
Using Data Validation.
Using Conditional Formatting.
Using Formulas.
These 3 methods are very simple to implement. So use them and we will be happy if you can share with us some new methods by which we can prevent blank or missing entries in cells in excel. You can drop down your suggestions, queries, any new idea in the comment section below.
412 Views
