We use cookies to give you the best experience on our website. If you continue without changing your settings, we’ll assume that you are happy to receive all cookies on this website. However, you can change your cookie setting at any time. x
Home > Blog

the 56 standard colours in Excel - ColorIndex values or pallet

Whilst you can use conditional formatting and cell fill etc. to give you almost any colour in excel there is a "special" set of 56 colours which Excel uses in custom formatting and other places. These are referred to by numbers 1-56. It is interesting and a little confusing that Excel will also allow the first 8 of these to be referenced by name, (Black, White, Red, Green, Blue, Yellow, Magenta and Cyan). This is why you can see colour names in number formats such as £#,##0.00;[Red]-£#,##0.00 which shows negative finance numbers are red.
The complete list of all 56 colors (note the US version of colours) is shown below.
list of 56 standard colours in Excel
This list was generated using the code below. To use this simply open the VBA editor window (<ALT><F11>) and add a module to your workbook and paste in the code. You will need to save the file as a macro-enabled workbook.

 

Sub display_colours()

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Dim i As Long

Dim str0 As String, str As String

Dim x As Integer

 

On Error GoTo Finalise

'titles

Cells(1, 1).Value = "Color"

Cells(1, 2).Value = "HEX"

Cells(1, 3).Value = "Red"

Cells(1, 4).Value = "Green"

Cells(1, 5).Value = "Blue"

Cells(1, 6).Value = "RGB"

Cells(1, 1).EntireRow.Font.Bold = True

 

For i = 1 To 56

Cells(i + 1, 1).Interior.ColorIndex = i

Cells(i + 1, 1).Value = "[Color " & i & "]"

Select Case Cells(i + 1, 1).Interior.ColorIndex

Case 1, 3, 5, 9, 10, 11, 12, 13, 14, 16, 18, 21, 23, 25, 29, 30, _

31, 32, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56

Cells(i + 1, 1).Font.ColorIndex = 2

Case Else

Cells(i + 1, 1).Font.ColorIndex = 1

End Select

str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.Color), 6)

Cells(i + 1, 2) = "#" & str0

Cells(i + 1, 3).Formula = "=Hex2dec(""" & Right(str0, 2) & """)"

Cells(i + 1, 4).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)"

Cells(i + 1, 5).Formula = "=Hex2dec(""" & Left(str0, 2) & """)"

Cells(i + 1, 6).Value = "RGB(" & Cells(i + 1, 3) & "," & Cells(i + 1, 4) & "," & Cells(i + 1, 5) & ")"

Next i

 

'autofit the columns

For x = 1 To ActiveSheet.UsedRange.Columns.Count

Columns(x).EntireColumn.AutoFit

Next x

 

Finalise:

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

End Sub
Miles Goodchild

Miles Goodchild has been a Program and Programme Planner, PMO lead and Project Manger since 1998.  He enjoys using MS Office, especially Project, Excel and Visio to make life easier and simpler.  In the course of this he created SummaryPro.  In this blog he shares some of the tips and techniques he has learnt over the years in the hope that they will be useful to you.