Master Excel: Change Font Color Using Formula

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.

Color-Coded Drop-Down Lists in Excel – Make Data Entry Smarter
Color-Coded Drop-Down Lists in Excel – Make Data Entry Smarter

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!

Change the Gridline Color in Excel Spreadsheets - 2 Ways!
Change the Gridline Color in Excel Spreadsheets - 2 Ways!

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.

Change Cell Colour using Conditional Formatting
Change Cell Colour using Conditional Formatting

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

How to Change Case in Excel: How to Use PROPER, UPPER & LOWER formulas - Tutorial
How to Change Case in Excel: How to Use PROPER, UPPER & LOWER formulas - Tutorial

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

the text sum by color in excel is displayed above an image of a computer keyboard
the text sum by color in excel is displayed above an image of a computer keyboard

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.

How To Change Lowercase Letter To Uppercase In Excel
How To Change Lowercase Letter To Uppercase In Excel

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.

the color code is shown in this screenshot
the color code is shown in this screenshot
Top 21 Excel Formulas
Top 21 Excel Formulas
Excel formulas
Excel formulas
Change the Theme in Excel
Change the Theme in Excel
excel create color - coded drop - down menu
excel create color - coded drop - down menu
ms excel formula
ms excel formula
the color coded drop down in excel is shown with an arrow pointing to it
the color coded drop down in excel is shown with an arrow pointing to it
How to Use the Conditional Formatting Function in Excel
How to Use the Conditional Formatting Function in Excel
Change the Gridline Color in Excel Spreadsheets - 2 Ways!
Change the Gridline Color in Excel Spreadsheets - 2 Ways!
Excel Formulas
Excel Formulas
Excel Formulas You Must Know
Excel Formulas You Must Know
Excel formulas you should know
Excel formulas you should know
ms excel formula
ms excel formula
Custom Excel number format
Custom Excel number format
How to Use FORMULA TEXT Function in Excel
How to Use FORMULA TEXT Function in Excel
How to use choose formula
How to use choose formula
50 Must-Know MS Excel Formulas Every Student & Professional Should Learn 📊⚡
50 Must-Know MS Excel Formulas Every Student & Professional Should Learn 📊⚡
#Excel #Tricks: Remove formula but keep the values or numbers or results
#Excel #Tricks: Remove formula but keep the values or numbers or results
How to Change Your Font in Google Sheets
How to Change Your Font in Google Sheets
Example of Using a Formula in Excel Conditional Formatting
Example of Using a Formula in Excel Conditional Formatting

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.

Related Articles

How To Change The Screen Color From Black And White To Proper Color On Iphone How To Change Black And White Screen Back To Color How To Add Color To A Black And White Photo In. Gimp How To Change The Black And White Screen To Color Samsung How To Convert Color Pdf To Black And White In Foxit How To Add Color To A Black And White Photo In Lightroom Picture Of Turkey Feathers In Color How To Change My Phone From Black And White To Color Iphone How To Convert Black And White Photo To Color In Lightroom How To Change Iphone Screen From Black And White To Color