Ever needed to emphasize certain data in your Excel spreadsheet by changing the font color? It's a simple yet effective way to draw attention to important figures or text. Fortunately, Excel allows you to apply conditional formatting, which includes changing the font color based on specific criteria. Here's a step-by-step guide on how to do this using a formula.

Before we dive in, let's ensure you have Excel 2010 or later. The steps might vary slightly for older versions. Now, let's get started!

Understanding Conditional Formatting
Conditional formatting is a feature in Excel that allows you to change the presentation of a cell or range of cells based on their values. It's as simple as setting rules for what values you want highlighted and how, such as changing the font color.

In this guide, we'll use the example of highlighting values greater than 100 in red. However, the process is similar for other criteria and coloring options.
Opening the Conditional Formatting Dialogue Box

Start by selecting the cells you want to format conditionally. Then, click on the "Home" tab in the Ribbon. In the "Styles" group, click on the small arrow below "Conditional Formatting" to open the drop-down menu.
Select "Highlight Cells Rules" and then "Greater Than" from the list. This will open the "New Formatting Rule" dialogue box.
Setting the Condition and Formatting

In the "Format cells that are GREATER THAN" field, enter "100". This tells Excel to highlight cells containing values greater than 100.
Click on the "Format" button to open the "Format Cells" dialogue box. Here, click on the "Fill" tab, and then select the red color you want from the color palette. You can also use the "Font" tab to change the font color after the rule has been applied.
Once you've selected your color, click "OK" to close the "Format Cells" box, then click "OK" again to close the "New Formatting Rule" box.

Applying the Formula to Change Font Color
Now, let's use a formula to change the font color. This can be particularly useful when you want to highlight cells based on the results of a calculation or a lookup. Let's use the formula to change the font to red when the cell value is not blank.




















Select the cells you want to format, and in the formula bar, type "=NOT(ISBLANK(A2))". Change "A2" to the first cell in your range, and press "Enter". The cells that are not blank will now be formatted with the font color you chose.
Keep in mind that the formula needs to be entered for each range of cells. So, for multiple non-adjacent ranges, you'll need to apply the conditional formatting to each range separately.
Using the "Format Only Cell That Contain" Option
If you want to apply the formatting to a different range, but only to the cells in that range that contain values from the current range, use the "Format only cells that contain" option.
Select the cells you want to format, then use the same steps as before to open the "New Formatting Rule" dialogue box. In the "Format values where this formula is true" field, enter "=A1". This tells Excel to only apply the formatting to cells in the range "A1:A100" (or your chosen range) that contain values from "A2".
Changing Font Color With UDFs (User Defined Functions)
If you need more complex conditional formatting, you might want to consider using UDFs. These allow you to create custom formulas that can be used in conditional formatting rules.
Let's say you want to change the font color to red if the cell value is greater than the average of the column. To do this, you could create a UDF like this:
="AVERAGE(OFFSET($A$2,ROW()-1,0)) - $A2 < 0"
Applying the UDF in Conditional Formatting
To use this UDF in conditional formatting, select the cells you want to format, and use the steps above to open the "New Formatting Rule" dialogue box. In the "Format values where this formula is true" field, enter the UDF. The cells that meet the condition set by the UDF will now be formatted with the font color you chose.
And that's it! You've now learned how to change font color in Excel using a formula, and even how to create custom formulas with UDFs. Next time you need to highlight important data in your spreadsheets, you'll know just how to do it.