Menu
The better way here is to use the COUNTBLANK(value) formula. It checks whether the value of a cell is blank. Let's say, that you want to keep only the rows with blanks. Select cells from D2 to D6 and use Ctrl + F.
The Excel ISBLANK function returns TRUE when a cell is empty, and FALSE when a cell is not empty. For example, if A1 contains "apple", ISBLANK(A1) returns FALSE. The Excel COUNTBLANK function returns a count of empty cells in a range. Cells that contain text, numbers, errors, spaces, etc.
Keep cell blank until data entered in Select first cell that you want to place the calculated result, type this formula =IF(OR(ISBLANK(A2),ISBLANK(B2)), "", A2-B2), and drag fill handle down to apply this formula to the cells you need.
If you want to leave a cell value unchanged if a condition is false in Excel, you can use the IF function. The IF function checks whether a condition is TRUE or FALSE, and then returns one value if the condition is TRUE, and another value if the condition is FALSE.
Unfortunately, there is no formula way to result in a truly blank cell, "" is the best formulas can offer.
I dislike ISBLANK because it will not see cells that only have "" as blanks. Instead I prefer COUNTBLANK, which will count "" as blank, so basically =COUNTBLANK(C1)>0 means that C1 is blank or has "".
If you need to remove blank cells in a column, I would recommend filtering on the column for blanks, then selecting the resulting cells and pressing Del. After which you can remove the filter.
Try this instead
=IF(ISBLANK(C1),TRUE,(TRIM(C1)=""))
This will return true for cells that are either truly blank, or contain nothing but white space.
See this post for a few other options.
edit
To reflect the comments and what you ended up doing: Instead of evaluating to "" enter another value such as 'deleteme' and then search for 'deleteme' instead of blanks.
=IF(ISBLANK(C1),TRUE,(TRIM(C1)="deleteme"))
I wanted to add that there is another possibility - to use the function na().
e.g. =if(a2 = 5,"good",na());
This will fill the cell with #N/A and if you chart the column, the data won't be graphed. I know it isn't "blank" as such, but it's another possibility if you have blank strings in your data and "" is a valid option.
Also, count(a:a) will not count cells which have been set to n/a by doing this.
If you want to use a phenomenical (with a formula in it) blank cell to make an arithmetic/mathematical operation, all you have to do is use this formula:
=N(C1)
assuming C1 is a "blank" cell
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With