Excel Color Index Formula

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will help you understand how to use the Excel color index formula to simplify the work of professionals. In everyday life, color plays a significant role. The use of color in commercial and professional environments can greatly improve the quality of work. When it comes to assigning Excel color index formulas to color cells, charts, objects, or visual data presentations, Excel gives users or experts the ability to produce aesthetically appealing and informative output.

overview image of Excel color index formula

What Is Color Index in Excel?

One of the standout features of Excel Color Index is its extensive library of predefined color codes. Excel Color Index offers a wide range of predefined color codes, providing users with 56 unique color options.

First, to find the color index in Excel, simply right-click on any cell, select Format Cells from the Format Cells dialog box, and navigate to the Fill tab. Here, you will find the default, predefined color palette.

color palette in Excel

For additional color choices, select the “more color” option. In the custom tab of the colors window, you can find the RGB and HEX values for any color.

finding color index value from Excel

In Microsoft Excel, there is no specific built-in function or formula available to directly obtain the color index of a cell. However, users can find the color index by utilizing Visual Basic for Applications (VBA) code. By using the VBA code, you can access and retrieve the color index of a cell or range, allowing you to work with colors effectively in Excel.

How to Use Formula with VBA User-Defined Function to Find Color Index in Excel

It is very easy to use VBA code to obtain and find the color index in Excel. Using the same process, we can obtain the hex code, RGB value, and index numbers of colors in Excel.

1. Finding Hex Color Code

To access the Visual Basic for Applications (VBA) editor in Excel, start by going to the Developer tab.
Following that, select the Visual Basic option, which will open a new window for VBA.

navigate through the developer tab to open the visual basic

  • Next, navigate to the Insert menu and choose Module.

adding module in VBA window

  • At this point, a new white page will appear on the left side of the window, where you can type or copy the following custom code.
Function GetHEXcode(FCell As Range) As String
Dim xColor As String
xColor = CStr(FCell.Interior.Color)
xColor = Right("000000" & Hex(xColor), 6)
GetHEXcode = Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2)
End Function

using code to build custom functions in Excel

Code Breakdown:

  • GetHEXcode(FCell As Range) defines a function named GetHEXcode that takes a cell range (FCell) and returns a string.
  • Dim xColor declares a string variable named xColor.
  • xColor = Right(“000000” & Hex(FCell.Interior.Color), 6) this code converts the interior color of the cell to a hexadecimal representation and assigns it to xColor. Leading zeros are added to ensure a 6-character length.
  • Meanwhile, the next line rearranges the characters in xColor to obtain the final hexadecimal color code and assigns it to GetHEXcode.
  • In the Excel sheet, enter the following formula in cell D5.

using a custom formula to get the hex code for the color in Excel

  • Thus the formula calculates the hex color code for the fill color of cell B5, which is dark red, and returns the value C00000.

using fill handle feature of Excel for quick output

By using the fill handle feature in Excel, we can easily extend the formula to calculate the hexadecimal color code for the remaining cells in the dataset. This allows us to obtain the hex value for all the colors in a quick and efficient manner.

2. Finding RGB Color Codes

To continue the process, we can access the VBA module again through the Developer tab.
We will use the following code in the module.

Function GetRGBvalue(cellRange As Range, ByVal ColorScheme As String) As Variant
    Dim ColorCode As Variant
    ColorCode = Cells(cellRange.Row, cellRange.Column).Interior.Color
    Select Case LCase(ColorScheme)
        Case "index"
            GetRGBvalue = cellRange.Interior.ColorIndex
        Case "rgb"
            GetRGBvalue = (ColorCode Mod 256) & ", " & _
            ((ColorCode \ 256) Mod 256) & ", " & (ColorCode \ 65536)
        Case Else
            GetRGBvalue = "Use either 'Index' or 'RGB' as second argument."
    End Select
End Function


using code to drive RGB value of color in Excel

Code Breakdown:

  • The GetRGBvalue function takes a cell range (cellRange) and a color format (ColorScheme) as inputs and returns a variant.
  • It declares a variant variable named ColorCode to store the interior color of the specified cell.
  • The ColorCode variable is assigned the interior color of the cell specified by cellRange.
  • The code uses a Select Case statement to check the value of the ColorScheme in lowercase.
  • If ColorScheme is “index“, the function assigns the interior color index of Rng to the GetRGBvalue variable.
  • If ColorScheme is “rgb“, the code calculates the RGB values (Red, Green, Blue) from ColorCode and assigns them as a concatenated string to GetRGBvalue.
  • If ColorScheme is neither “index” nor “rgb“, the function assigns an error message indicating the appropriate format to be used.
  • Now, in the cell range from D5 to D15, we will apply the following formula to get the RGB color values.

using custom function to get the RGB value

  • By applying the custom formula in cell D15, we were able to retrieve the RGB color value of the black cell (B15), which is 0,0,0. Undoubtedly, this formula allows you to obtain the RGB values of a specific cell color in Excel.
  • Once again by using the same formula and replacing “RGB” with “index“, it will return the index value of the color code.
  • Check the following image, where we used the following formula in cell D15 to get the index value of the color of cell B15.

using custom function to get the index color value in Excel

The function successfully returned the index value for the color black.

NOTE: The index value assigned to colors in Excel is determined by their position in the predefined color palette. Therefore, certain colors, such as red and dark red, light blue and pale blue, or dark green and green, may share the same index number due to their similar placement in the palette.

3. Finding Decimal Values of Color Code

To get the decimal values of color codes in Excel, first, go to the Developer Tab > select Visual Basic.

Type or copy the following code to make a custom function.

Function GetDecimal(FCell As Range, Optional Opt As Integer = 0) As Long
'Updateby _ Exceldemy
Dim xColor As Long
Dim R As Long, G As Long, B As Long
xColor = FCell.Interior.Color
R = xColor Mod 256
G = (xColor \ 256) Mod 256
B = (xColor \ 65536) Mod 256
Select Case Opt
Case 1
GetDecimal = R
Case 2
GetDecimal = G
Case 3
GetDecimal = B
Case Else
GetDecimal = xColor
End Select
End Function

using code to make a custom function for deriving decimal values of colors

Code Breakdown:

  • The GetDecimal function takes a cell range (FCell) and an optional parameter (Opt) as inputs and returns a Long value.
  • Several Long variables (xColor, R, G, B) are declared to store color-related values.
  • The xColor variable is assigned the interior color of the specified cell (FCell).
  • The first thing to remember is that the R, G, and B variables are calculated by extracting the corresponding color values (Red, Green, Blue) from xColor using modulus and division operations.
  • In the Select Case statement, if Opt is 1, 2, or 3, the function assigns the corresponding color value (Red, Green, or Blue) to GetDecimal; otherwise, it assigns the original color value (xColor).
  • We will use the function in cell D5 to obtain the decimal value of the color in cell B5.

decimal value of color dark red

  • By using the fill handle feature in Excel, we can quickly obtain the decimal values for the entire dataset.

finding decimal values of color code using a custom function

How to Use GET.CELL Function in Excel Formula to Get Cell Color Index

The GET.CELL function is a built-in function that helps users get more information about a cell. The GET.CELL function in Excel offers a unique way to retrieve specific information about cells, including index color. To demonstrate its application, we will use the same dataset.

  • First, navigate to the Formulas tab and select Name Manager from the Defined Names group.
  • At this point, in the Name Manager window, click on New to create a new named range.

using name range to create a new name range

  • In sum, assign a name to the named range. For this example, let’s assume the name is “background“.
  • In the Refers to box, enter the following formula to utilize the GET.CELL function and retrieve the index color.

name range window

Formula Breakdown:
This formula instructs Excel to fetch the index color of the cell that is 2 columns to the left of the current cell using the GET.CELL function with the specified info_type (63 for index color)
  • Now we will type the following formula in cell D5.

retrieving color index

As a result, the formula retrieves the color index of cell B5, which is located two columns to the left of the current cell D5.

final output from using GET.CELL function

Finally, you can drag the fill handle to apply the formula to the desired cell range or dataset, allowing you to obtain the color index for each corresponding cell.

How to Get Font Color Index in Excel

To find the index value of the font color, follow these steps:

  • In this case, press the keyboard shortcut ALT+F11 to open the Visual Basic window.
  • Afterward, type or copy the following code into the window.
Function FontIndex(VarRange As Range) As Integer
FontIndex = VarRange.Font.ColorIndex
End Function

using code to extract font color index in Excel

Code Breakdown:
  • The code is a custom function called FontIndex that returns the index value of the font color for a given range of cells.
  • It uses the property VarRange.Font.ColorIndex to retrieve the font color index in particular.
  • We will utilize the custom function in the dataset to obtain the font color index in Excel. For instance, in cell D15, we applied the formula to extract the font color index of cell C15.

final output from a custom function

Things to Remember

  • Press ALT+F11 on your keyboard to open the VBA editor (Shortcut).
  • RGB color values are widely used in all aspects of life.
  • The color index remains unchanged even if the cell color is modified. Press the F9 key to recalculate the formula.

Frequently Asked Questions

1. What is the Color index number?
The color index number in Excel represents the predefined index assigned to each color, with support for up to 56 color indexes, numbered from 1 to 56.

2. Can I customize the color index in Excel?
No, the color index in Excel is predefined and cannot be customized. However, you can choose from the available colors in the Excel palette to assign specific color indexes to cells.

3. How do I read cell color in Excel?
Reading cell colors in Excel requires the use of VBA code or other programming methods, as Excel does not have a built-in function for directly retrieving cell colors.

4. Are color indexes consistent across different versions of Excel?
Yes, color indexes in Excel are generally consistent across different versions.
However, it’s worth noting that the available colors may vary. Additionally, custom color indexes may not be consistent if a workbook is opened in a different version of Excel.

5. Is there a way to convert the color index to RGB values?
Yes, you can convert the color index to RGB (red, green, and blue) values using various methods, such as VBA code. Provided that this conversion allows you to determine the specific RGB color values associated with a particular color index in Excel.

Download Practice Workbook
You can download the workbook, where we have provided a practice section on the right side of each worksheet. Try it yourself.


As Excel does not provide a built-in function or directly obtain the Excel color index formula, alternative methods such as VBA code or using named ranges can be utilized to extract information about cell colors.

What’s more is that this article covers various aspects, including hex code, RGB code, and index value, to assist you in finding your desired color code. If you have any further questions or inquiries, please feel free to comment below.

Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Ishrak Khan
Ishrak Khan

Ishrak Khan is a content developer and urban planner born in Dhaka, Bangladesh. He is closely working with ExcelDemy to make life simple and easy. He loves Football, Nico Robin, Deserts, and Twix. If you like the post, give him a heart on Instagram.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF