Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
Null is a type of error which occurs in excel when the two or more cell references provided in a formulas are incorrect or the position they have been placed is incorrect, if we use space in formulas between two cell references we will encounter null error, there are two reasons to encounter this error one is if we used incorrect range reference and another when we use the intersect operator which is the space character.
Null in Excel (Table of Contents)
- Null Value in Excel
- How to Find NULL Cells in Excel?
- Shortcut Way of Finding NULL Cells in Excel
- How to Fill Our Own Values to NULL Cells in Excel?
Null in Excel
NULL is nothing but nothing or blank in excel. Usually, when we are working in excel we encounter many NULL or Blank cells. We can use the formula and find out whether the particular cell is blank (NULL) or not.
We have several ways of finding the NULL cells in excel. In today’s article, we will take a tour of dealing with NULL values in excel.
How do you find which cell is actually blank or null? Yes, of course, we just need to look at the particular cell and make our decision. Let’s discover many methods of finding the null cells in excel.
ISBLANK Function to Find NULL Value in Excel
In excel we have a built-in function called ISBLANK function which can find the blank cells in the worksheet. Let’s look at the syntax of the ISBLANK function.
The syntax is simple and straight forward. Value is nothing but the cell reference we are testing whether it is blank or not.
Since ISBLANK is a logical excel function it will either return TRUE or FALSE as the result. If the cell is NULL then it will return TRUE or else it will return FALSE.
Note: ISBLANK will treat the one single space as one character and if the cell has only space value then it will recognize as a non-blank or non-null cell.
#1 – How to Find NULL Cells in Excel?
Assume you have below values in the excel file and you want to test all the null cells in the range.
Let’s open ISBLANK formula in cell B2 cell.
Select the cell A2 as the argument. Since there is only one argument close the bracket
We got the result as given below:
Drag-drop the formula to other remaining cells.
We got the results but look at the cell B7, even though there is no value in the cell A7 still formula returned the result as a False i.e. non-null cell.
Let’s apply the LEN function to find the no. of characters in the cell.
It counts the no. of characters and gives the result.
LEN function returned the no., of character in the A7 cell as 1. So, there should be a character in it.
Let’s edit the cell now. So, we found the space character here, let’s remove the space character to make the formula to show accurate results.
I have removed the space character and ISBLANK formula returned the result as TRUE and even LEN function says there are zero characters in the cell A7.
#2 – Shortcut Way of Finding NULL Cells in Excel
We have seen the traditional formula way to find the null cells. Without using the ISBLANK function we can find the null cells.
Let’s open the formula with an equal sign (=).
After the equal sing selects the cell A2 as the reference.
Now open one more equal sign after the cell reference.
Now mention open double quotes and close double quotes. (“”)
The signs double quotes (“”) says is the selected cell is NULL or not. If the selected cell is NULL then we will get TRUE or else we will get FALSE.
Drag the Formula to remaining cells.
We can see that in cell B7, we got the result as “True”. It means it is a null cell.
#3 – How to Fill Our Own Values to NULL Cells in Excel?
We have seen how to find the NULL cells in the excel sheet. In our formula, we could only get TRUE or FALSE as a result. But we can also get our own values for the NULL cells.
Consider the below data for an example.
Step 1: Open IF condition first.
Step 2: Here we need to do a logical test i.e. we need to test whether the cell is NULL or not. So apply A2=””.
Step 3: If the logical test is TRUE (TRUE means cell is NULL) we need the result as “No Values Found”.
Step 4: If the logical test is FALSE (FALSE means cell contains values) then we need the same cell value.
We got the result as the same cell value.
Step 5: Drag the formula to remaining cells.
So we have got our own value of No Values Found for all the NULL cells.
Things to Remember
- Even space will be considered as character and treats as a non-empty cell.
- Instead of ISBLANK, we can also use double quotes (“”) to test the NULL cells.
- If the cell seems blank and formula shows it as non-null cell then you need to test the number of characters by using LEN function.
This has been a guide to Null in Excel. Here we discuss the top methods to Find Null Values in Excel using ISBLANK and Shortcuts to replace those null cells along with practical examples and a downloadable template. You may learn more about excel from the following articles –