Formulas are purposed to return a value whenever a calculation has been done in Excel. However, there are times that you would want to have nothing returned after using the formula. It can happen when you want to differentiate a value to some constant or return nothing if they are different. Whatever the case, formulas must return something. There are two ways to return an empty cell from a formula in excel.
In the formula =IF(A1=0,"," 1), there is a blank in between ("), which can contain one or zero. However, the following formula does not have a quotation mark; =IF(AI=0, ,1). In this case, Excel assumes that there is a zero in between the two commas. This formula, therefore, returns a zero value.
The Excel function COUNT returns the value zero; COUNTA returns something two in both formulas, while COUNTBLANK returns either 0 or 1 in both formulas.
In summary, if you want to return an empty or blank cell from a formula, follow these easy steps;
1. Select the "range of cells" that you need to apply this
2. Press F5
3. In the dialog box that appears, click Go To
4. Select the Go to Special option under Special
5. Select the Formulas radio button
6. Only select the Text box
7. Press OK
In this way, Excel will select all cells whose formula returns a text value.
Return empty or blank value with IF Function
This formula uses the IF function and returns the blank or empty value. The following formula is generally used; =IF (Value= Criteria, Result1,"). Using the IF formula, you can return a "blank cell" rather than a "zero value," for example, =IF(A2-A3=0,", A2-13). According to the formula;
- IF returns one value for a TRUE result and another for a FALSE result
- VALUE– represents the input value of the cell
- Criteria– This is usually the given criteria
- Result– The value to be returned
- The Comma Symbol– It separates the list of values
- Parenthesis ()– It usually groups the elements in the formula, thus;
1. Input values in the two columns
2. Go to the formula bar section
3. Apply the above formula in this section
4. Press Enter
The result is displayed in the cell you selected.
Using the Custom Formatting Method
This method uses zero as blanks through custom formatting. The following simple steps can be used;
1. Select the "cells" you need to apply this method
2. Press Ctrl+1 for opening the Format Cells box
3. Go to Number Tab> Category list
4. Click on Custom
5. Type 0;-0; @ in the Type box
Using the Worksheet Option
This method hides the zeros across the whole worksheet. Use the following simple steps;
1. Go to the Ribbons File Tab
2. Click on Options
3. Click Excel options> Advanced
4. Go to the Display options for the worksheet
5. Select the worksheet you are working on
6. Untick the Show Zero option in all cells with zero values.
7. Press OK