Formula to fill cell if range isn't blank?

excel formula color cell if value
excel conditional formatting based on another cell range
excel conditional formatting based on another cell text
excel conditional formatting based on another cell color
excel conditional formatting based on another cell multiple values
excel conditional formatting formula if cell contains text
excel conditional formatting formula based on another cell
excel conditional formatting formula greater than and less than

I would like to fill a column cell with an "x" if a range further across that row has any text in it.

Sample spreadsheet

'A' column would fill with "x" if there are any values in it's row from 'C:F' and stay blank if the row (in that range) is blank.

I've tried variations of =IF =ISBLANK for this.

=IF(ISBLANK(C:F),"","x") still placed an X even if the row was blank.

ISBLANK works for a single cell but we do have an alternative:

=if(countblank(C2:F2)=4,"","x")

How do you apply conditional formatting to a range of cells? To evaluate the cells are Not Blank you need to use either the logical expression Not Equal to Blank (<>””) of ISBLANK function in logical_test argument of IF formula. In case of logical expression Not Equal to Blank (<>””) logical_test argument returns TRUE if the cell is Not Blank, otherwise, it returns FALSE.

In addition to pnuts' contribution, an array-enabled solution could be

=ArrayFormula(if(countif(if(C2:F20<>"", row(2:20)), row(2:20)), "x",))

How do I color code cells in Excel based on value? The rule is applied to the entire range C5:G15, and the value in J6 can be changed at any time by the user. When a new value is entered, the highlighting is immediately updated. The formula uses the greater than or equal to operator (>=) to evaluate each cell in the range against the value in J6.

An alternative to the above

=if(LEN(CONCATENATE(A2:F2))>0,"X","")

This grabs all inputs across the row, concatenates them together in one string, counts the length of that combined string, and returns a "X" if it's greater than zero.

This has the advantage that it works if you add more headers (if you extend the range).

The disadvantage is that it would also count whitespaces

For example, if you have numbers in the range B4:G11, and want to highlight a formula to apply conditional formatting, the formula is evaluated for each cell in  To run a formula only when one or more cells are not blank, you can use the IF function with an appropriate logical criteria. In the example shown, the formula in E5 is: =IF(COUNT( C5:C7)=3,SUM( C5:C7 ),"") Since C7 has no value in the screen above, the formula shows no result.

The cells are conditionally highlighted if their respective values fall below the Prior Year Select the data cells in your target range (cells E3:C14 in this example), click the In the list box at the top of the dialog box, click the Use a Formula to  I would like to fill a column cell with an "x" if a range further across that row has any text in it. Sample spreadsheet 'A' column would fill with "x" if there are any values in it's row from 'C:F' and stay blank if the row (in that range) is blank. I've tried variations of =IF =ISBLANK for this.

If the conditions are true, the cell range is formatted; if the conditions are false, can also create your own (including by using a formula that evaluates to True or  Based on the number in a cell in a column before the start of the years I want to be able to GoTo a starting cell under any number from 1 to 84 and fill in a colour starting at that cell reference and continue to Color say x cells based on the value in anthother cell. Thanks very much for your help. Very appreciative

In this tutorial, you will learn how to highlight cells using conditional formatting based on From the 'Format Cells if' drop down, select 'Custom Formula is'. The formula in cell E5 is: To take an action only when the cell is not blank (not empty), you can use a formula based on the IF function. In the example shown, column D holds task complete dates.

Comments
  • This is a great solution thank you!