If you want to apply conditional formatting based on the value of another cell including text or number or date etc., then this can be achieved by creating a rule in conditional formatting using a custom formula.
Example of conditional formatting based on the value of another cell
In the example below, we want conditional formatting on cells with figures that fall below the yearly average.
Select the cells you want to format. You can select one column, several columns, or the entire table if you wish to apply your conditional format to rows.
Tip: If you want the conditional formatting rule to get applied to new data entries automatically, you can either:
- Convert a range of cells to a table (Go to the Excel Ribbon on the Insert tab > click Table). In this case, the conditional formatting will be automatically applied to all new rows.
- Select some empty rows below your data, say 100 blank rows.
Select the data cells in B4:C15 range, click the Home tab of the Excel Ribbon and then select Conditional Formatting → New Rule.
This opens the New Formatting Rule dialog box. Click the “Use a Formula to Determine Which Cells to Format” option.
In the formula input box, enter the formula to compare your data range values (B4:C15) with the value in the comparison cell ($E$4).
Here, as you are only comparing the sales figures in column C with yearly average figure in cell $E$4, so you will fix the column by using $ sign with column name, and will keep the row number free or relative to evaluate each cell value in column C against value based on cell $E$4.
This formula will compare the sales figures in column C with yearly average value in cell $E$4, and will conditionally format the data rows in the selected range.
Then, click the Format button and select the option for formatting of font, border, and fill for your target cells. Click the OK button to confirm your changes and return to the New Formatting Rule dialog box. Again press OK button to confirm your formatting rule.
As you want to apply conditional formatting based on cell value of $E$4, so the above formula will evaluate the results in column C based on the value in $E$4 and conditional formatting will take effect where the formula will return TRUE.
Monthly sales data will be highlighted conditionally as per your chosen format if it is less than the yearly average figure in E4 as shown below.