How to get Formula Behind Conditional Formatting in Excel

0
votes

How can I get formula applied behind conditional Formatting for highlighting Cells?

Conditional Formatting is applied based on text in each cell of Test Result column(third column). Column is highlighted

Formulas List: Total 5 Formulas are applied

Purpose is to reuse (copy paste) the formula in other sheets.

1
excelexcel-formula

1 Answers

1
votes

The formula is shown in your first screenshot. The dialog has the formula in the column "Rule applied (in order shown)". These rules use the out of the box settings to format cells based on their values, so the "formula" is not accessible for editing.

But the format can be copied to other cells very easily.

Here are the steps:

  • Select the cell with the conditional format and copy it
  • click the cell where you want to apply the same format
  • use Paste Special > Format to paste just the format to the selected cell

Edit: If you don't use the built-in conditional formats but instead select "Use a formula to determine ...", you can construct the formulas manually. enter image description here

There is no automated way to convert the existing out-of-the-box rules to fomulas. You will need to use your human understanding of the logic and appy this to formulas. So, for example, if the selected cell is H3, you can us this formula

=H3="Fail"

and format the cell with red font and light red background. Note that there are no $ signs in the reference to the cell H3. If you copy that format to another cell, it will apply to that other current cell.