In this article we will learn how to adjust a formula to return a blank rather than a zero when operating on empty cells in Microsoft Excel 2010. When you have formulae in a range of cells and these cells include blanks, the formula will return for the empty cells. However you can revise the formulae to show you excel return blank cell instead of 0 whenever there are empty cells in the sheet.
Lets see how to make a cell blank in excel formula.
We have a list in column A which includes numbers as well as blank cells.
For example, we have this formula in B2 which multiplies each number in the list by 3 –
The output we get is
You will find that all empty cells in column A are returning 0 in column B. We can avoid this. All we need to do is change the formula from
Note that a pair of double quotes have been included instead of “nothing” so that that formula returns an empty cell when there are 0s in column A.
Edit the formula in B2 and drag it down to the last cell as before.
The output will now be –
In this way excel return blank instead of 0 & this makes the data more presentable and easy to read and you can even include headers in between the data elements if needed. You do not have to copy the formula multiple times for each data group which is separated by the blank cells and you can just drag the formula down from the first to the last cell in the range.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.