Colors

Introduction

Colors in Excel are defined by an RGB (red/green/blue) code of the form

RGB = Red + Green * 256 + Blue * 256²

where Red, Green, and Blue are integer values from 0 to 255 representing the intensity of that primary color.

On this webpage, we describe how to use colors in Excel.

Worksheet Functions

Real Statistics Functions: The Real Statistics Resource Pack contains the following functions that allow you to test whether cells in your spreadsheet have a specified font or background (i.e. fill) color or are bold.

FillRGB(R1) returns the RGB background color value of the cells in range R1 (if the background colors of the cells differ then zero is returned).

FontRGB(R1) returns the RGB font color value of the cells in range R1 (if the font colors of the cells differ then zero is returned).

RedRGB(n) returns the value (0-255) of the red component of the RGB value n

GreenRGB(n) returns the value (0-255) of the green component of the RGB value n

BlueRGB(n) returns the value (0-255) of the blue component of the RGB value n

RGBCode(text) returns the RGB value of the color named in text

FillColor(R1, approx) returns the fill color of R1 as text. If approx = 0 or FALSE (default) an exact match among the 66 named colors must be found; otherwise “unknown” is returned. If approx = -1 then the closest of the 66 named colors is returned. approx = 1 (or TRUE) is equivalent to approx = -1 except that when there is an exact match then the next closest of the 66 named colors is returned.

FontColor(R1, approx) returns the font color of R1 as text. approx is as for FillColor.

IsBold(R1) returns the value TRUE if all the cells in range R1 are bold and FALSE otherwise.

Observations

If FillRGB(“A1”) = 16734822 = 102 + 90*256 + 255*256^2, then RedRGB(FillRGB(“A1”)) = 102, GreenRGB(FillRGB(“A1”)) = 90 and BlueRGB(FillRGB(“A1”)) = 255.

The text argument in the RGBCode function (as well as the output from the FillColor and FontColor functions) can take any of the 66 values, “amber”, “amethyst”, etc., shown in Figure 1. This figure also shows the corresponding RGB code values.

Note that RGBCode(“red”) = 255, RGBCode(“yellow”) = 255 + 256*255 = 65535, RGB(“salmon”) = 250 + 256*128 + 256^2*114 = 7504122, etc.

Named Colors

Figure 1 provides a list of 66 named colors and their RGB codes.

Figure 1 – 66 named colors

These functions enable you to test whether a cell (or range) has a particular color (or is bold). For example, FillColor(A1) = “red” returns TRUE if cell A1 has a red background color. FillRGB(A1) = FillRGB(A2) returns TRUE if cells A1 and A2 contain the same background color (even if not one of the 66 named colors).

Further examples are shown in Figure 2.

Figure 2 – Color functions

Note that =FontColor(G6) in cell I6 returns the value “unknown” since the font in cell G6 is not one of the 66 named colors; however, =FontColor(G6), or equivalently =FontColor(G6,TRUE), in cell I7 returns the value “asparagus” since that is the closest font color among the 66 named colors.

Squared Distance

The squared distance between two colors is equal to

where r₁, g₁, b₁ are the RGB values of one color and r₂, g₂, b₂ are the RGB values of the other color. 

The formula = FillColor(A10) in cell C14 of Figure 2 returns the value “cherry” since the background color in cell A10 is an exact match for one of the 66 named colors, namely cherry. Based on the above definition, the next closest named color is “crimson”, as shown in cell C15.

Alternative Definitions

Two alternative approaches to the squared distance are

(r₁ – r₂)² + (g₁ – g₂)² + (b₁ – b₂)²

which is the standard Euclidean distance and

which is similar to the approach used to convert a color to greyscale (see Greyscale).

Worksheet Functions

Real Statistics Function: The Real Statistics Resource Pack provides the following worksheet functions to calculate the squared distance between two colors.

RGBDistSq(rgb1, rgb2, ttype) = the squared distance between the colors with RGB codes rgb1 and rgb2

ColorDistSq(color1, color2, ttype) = the squared distance between the names colors color1 and color2

When ttype = 0 (default), the first definition of squared distance described above is used. If ttype = 1 then the second approach is used, while if ttype = -1 then the third approach is used.

Example

Example 1: Figure 3 shows some examples of colors (in column F) and the closest color to them (in column I).

Figure 3 – Closest colors

For example, cell H2 contains the formula =FillColor(F2,1) and cell K2 contains the formula =RGBDistSq(E2,J2), which shows the squared distance between the amber and gold colors. This distance can also be calculated via the formula =ColorDistSq(A2,H2).

Data Analysis Tool

Real Statistics Data Analysis Tool: The Real Statistics Resource Pack contains the Color Assignment data analysis tool that allows you to fill a range with any of the 66 named colors.

For example, to fill cell G3 with the cyan color, press Ctrl-m, and choose the Color Assignment option. Fill in the dialog box that appears as shown in Figure 4 and press the OK button.

Figure 4 – Color Assignment dialog box

The result is shown in cell G3 in Figure 2. Note that after pressing the OK button, the dialog box in Figure 3 remains open and the Output Range automatically advances to the next cell in the same column (G4 for this example).  You can now select a background color for this cell. To terminate this data analysis tool you need to click on the Cancel button.

Note that for this example the Input Range field was left blank. If instead, you fill in the Input Range, then the output range (i.e. range whose upper-left-hand corner starts at the cell shown in the Output Range field) is filled in with the background colors whose RGB codes appear in the input range. The output range will have the same size and shape as the input range.

E.g. if you insert E2:E23 from Figure 1 in the Input Range, F2 in the Output Range and then click on the OK button, the output will be as shown in range F2:F23 of Figure 1. Note that in this case, the menu of colors in the dialog box displayed in Figure 4 is not used.

Greyscale

You can also use the Color Assignment data analysis tool to fill individual cells with greyscale colors. This is done by entering the greyscale code in the Greyscale field of the dialog box in Figure 4 and selecting “grey” from the color list (instead of “cyan”). 

For example, if you insert 210  in the Greyscale field then the color “grey210” would be displayed in cell G3.

If you leave the Greyscale field blank, then it defaults to 128.

See Greyscale for more information about using greyscale coding.

Examples Workbook

Click here to download the Excel workbook with the examples described on this webpage.

Reference

Microsoft Office (2018) Add or change background color of cells
https://support.microsoft.com/en-us/office/add-or-change-the-background-color-of-cells-ac10f131-b847-428f-b656-d65375fb815e

Dynamsoft (2019) Image processing 101 chapter 1.3: color space conversion
https://www.dynamsoft.com/blog/insights/image-processing/image-processing-101-color-space-conversion/

Wikipedia (2024) Grayscale
https://en.wikipedia.org/wiki/Grayscale