Function Problems

How to Automatically Display Text Based on Another Cell's Fill Color in Excel

Algirdas Jasaitis Oct 19, 2026 868 views

Question details

The user wants to automatically return or display specific text in a cell depending on the manual background fill color applied to another referenced cell.

How to Automatically Display Text Based on Cell Fill Color in Excel

Product: Excel
Device & OS: not provided
Scenario: When organizing, categorizing, or analyzing a spreadsheet where cells have been manually color-coded, and text labels need to be generated automatically based on those specific colors.
Observed behavior: Excel does not provide a standard built-in worksheet formula that can directly read a cell's formatting, such as its background fill color, to return a text value.

Before you start

Determine whether the cell color was applied manually or through conditional formatting. If the color is the result of conditional formatting rules, it is much more reliable to base your IF formula on the original rule condition rather than trying to detect the color itself.

Solution 1Recommended

Use the Legacy GET.CELL Function via Name Manager

Create a custom defined name using the legacy GET.CELL macro function to read the color index of an adjacent cell, then translate that index into text.

Because standard formulas cannot read background colors, you can use a hidden Excel 4.0 Macro function called GET.CELL. This function cannot be typed directly into a cell; it must be run through Excel's Name Manager.

Open the Name Manager

Navigate to the Formulas tab on the top ribbon and click on 'Name Manager', then click 'New' to create a defined name.

Define the GET.CELL formula

In the 'Name' field, type a descriptive name such as 'GetLeftColor'. In the 'Refers to' field, input the exact formula: `=GET.CELL(63,INDIRECT("RC[-1]",FALSE))` and click OK. The number 63 specifies that you want to extract the cell's background color index.

Apply the custom name in your worksheet

Select the cell immediately to the right of your colored cell (e.g., cell B1 if A1 is colored). Type a nested IF formula using your new defined name, such as: `=IF(GetLeftColor=33,"blue",IF(GetLeftColor=43,"green",IF(GetLeftColor=6,"yellow","")))`.

Verify your color index numbers

Color index values vary depending on the exact shade applied. You may need to test the output of `=GetLeftColor` on its own to find out the specific index number of the colors used in your workbook.

Use the Legacy GET.CELL Function via Name Manager

Macro-Enabled Format Required: Because GET.CELL is considered a legacy macro function, you must save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to prevent the function from being deleted upon closing.

Solution 2

Create a Custom VBA Function (UDF)

Write a short User Defined Function in Visual Basic for Applications (VBA) to directly detect a cell's interior color.

Automate Data and Cell Indexing Easily with WPS Spreadsheet

WPS Office offers a fully featured spreadsheet application that natively supports advanced data handling, the Name Manager, and VBA macros (available in the professional version). You can replicate the GET.CELL workaround or write powerful macros to seamlessly link background colors with text outputs.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook where you need to track cell colors.
2. Access the Name Manager: Go to the 'Formulas' tab in the top ribbon and select 'Name Manager' to input your GET.CELL parameters.
3. Save as Macro-Enabled: Easily save your file in the .xlsm format to ensure your new color-detecting formulas remain active for future use.

Full compatibility with Microsoft Excel macro-enabled workbooks (.xlsm)Built-in Name Manager allows easy implementation of legacy GET.CELL formulasAdvanced Conditional Formatting options to automate color rules based on text valuesLightweight, fast interface suitable for complex data categorization tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does GET.CELL return a number instead of the color's name?

The GET.CELL function configured with the parameter '63' extracts the 'Color Index' number (e.g., 33 for light blue, 3 for red) assigned to that specific shade in Excel's color palette, not the English word for the color. You must use an IF or CHOOSE function to translate that index number into a readable text label.

Does the GET.CELL formula update automatically when I change the cell color?

No, merely changing a cell's fill color does not trigger a formula recalculation in Excel. To update the text output after modifying a cell's background color, you must manually force the worksheet to recalculate by pressing the F9 key.

Can I use standard formulas to count cells based on their background color?

No, standard formulas like COUNTIF cannot detect cell formatting. To count cells by color, you would need to use a VBA macro, pair the GET.CELL method with a standard COUNTIF, or use the Filter by Color tool in conjunction with the SUBTOTAL function.

Excel & Data Problems