Convert Hex to RGB in Excel: Formula and BGR Color Value

Excel Packs Color Backwards, and Nobody Says So

Almost every wrong answer to convert hex to RGB in Excel formula questions is wrong for one reason. A hex code reads red, green, blue from left to right. The integer Excel keeps in Interior.Color is packed the other way round: blue in the high byte, green in the middle, red in the low byte. In Excel, Interior.Color is a BGR number rather than an RGB one, so building it in the order the hex code suggests makes the first and last channels trade places.

Put plainly: to convert a hex color to an Excel color code, you multiply the blue channel by 65,536, not the red one. That single reversal is the entire difference between a palette that arrives intact and one that arrives inverted.

Take the Office accent blue, #4472C4:

The arithmetic for the last row is blue first: 196 × 65,536 = 12,845,056, plus 114 × 256 = 29,184, plus 68, giving 12,874,308. Compute it the intuitive way — 68 × 65,536 + 114 × 256 + 196 — and you get 4,485,828, which Excel paints as a muddy olive. If your colors keep landing on the wrong side of the color wheel, that swap is the whole bug.

An Excel VBA RGB color code — RGB(68, 114, 196) written out in source — hides this from you, because the function performs the packing itself. The trouble starts the moment you skip the function and assign a number directly, which is exactly what you do when the value comes from a cell.

Doing It With Worksheet Functions

HEX2DEC and MID are all you need, and neither requires the Analysis ToolPak add-in any more — both have been built in since 2007. With a bare six-digit code in A1:

Red    =HEX2DEC(MID(A1,1,2))
Green  =HEX2DEC(MID(A1,3,2))
Blue   =HEX2DEC(MID(A1,5,2))

If the hash is still attached, strip it first with SUBSTITUTE(A1,"#",""). To go straight to the packed value in one cell, keep the byte order in mind:

=HEX2DEC(MID(A1,5,2))*65536
 + HEX2DEC(MID(A1,3,2))*256
 + HEX2DEC(MID(A1,1,2))

Going Back: Excel RGB to Hex Formula

The return trip is DEC2HEX with an explicit width, and the width argument is not optional in practice. Without it, a channel below 16 produces a single digit and your six-character code silently becomes five.

=DEC2HEX(R,2) & DEC2HEX(G,2) & DEC2HEX(B,2)

Unpacking a stored long back into channels reverses the packing, so red comes out of the remainder and blue out of the quotient:

Red    =MOD(A1,256)
Green  =MOD(INT(A1/256),256)
Blue   =INT(A1/65536)

Excel ColorIndex vs Interior.Color

Two properties, two different eras, and mixing them produces colors nobody chose. ColorIndex points at a numbered slot in the workbook palette — 56 slots, fixed, inherited from Excel 5 and kept for backward compatibility. Interior.Color addresses the full 16.7 million.

• Assigning to ColorIndex snaps. Your carefully chosen brand color lands on whichever of the 56 is nearest, which is often visibly not it.
• Reading ColorIndex misleads. On a cell filled from a theme, the index returned no longer corresponds to what is on screen.
• Two magic numbers. −4142 means no fill and −4105 means automatic. Neither is a color, and both break arithmetic if you treat them as one.

Unless you are maintaining something written before 2007, read and write Interior.Color and let ColorIndex alone.

Reading the Color Already Sitting in a Cell

The opposite question comes up almost as often, and the answer is worse: there is no worksheet function that returns a cell's fill. CELL reports width, format code and address, but never color. GET.CELL(63, ...) does return the interior color, and is the trick most forum threads land on — but it is a macro-sheet function from Excel 4, usable only through a defined name, and a workbook containing one has to be saved as .xlsm.

It also does not recalculate when a fill changes, because changing a color is not an event Excel considers worth recalculating for. You end up pressing Ctrl+Alt+F9 to refresh a column of colors, which is a fair sign the approach is fighting the product. If you need fill values as data, read them with an Office Script or export the sheet and read the XML — the fills are stored plainly inside it.

The Office Theme Palette, Converted

The six theme accents are the colors that appear in Excel questions more than any others, because they are what the default chart series and table styles use. Here they are in all three forms, so you can paste one straight into a macro without doing the arithmetic:

Accent 4 is the one worth staring at. #FFC000 is a saturated amber with no blue in it at all, and its packed value is 49,407 — a suspiciously small number for a bright color. That is the byte order made visible: with the blue channel at zero, the whole high word is empty. A theme fill set by the picker also tracks the theme, so swapping the workbook theme moves it; a value written as a number does not.

Using a Hex Color for Conditional Formatting in Excel

A frequent and reasonable expectation: put a hex code in a column and have the cells next to it take that color. Excel will not do it, because a formula returns a value and never touches formatting. The options are:

1. A rule per color. Home → Conditional Formatting → New Rule, with the fill chosen from More Colors. Practical for five colors, miserable for fifty.
2. A macro loop. Read each cell, convert, assign the packed long — and inherit every macro restriction your organisation applies.
3. An Office Script. getFill().setColor("4472C4") takes the hex string as given. No packing, no byte order, and it runs in Excel on the web.

How Google Sheets Handles the Same Job

Google Sheets stores a hex color string and never a packed integer, which is worth knowing if your team works across both. The friction this page describes is almost entirely an Excel-and-VBA artefact:

Converting a Column Without Fifty Formulas

Brand palettes arrive as a list, and writing three helper columns of HEX2DEC for forty rows is a poor use of an afternoon. Paste the hex column into the batch panel above and choose what you want back — the packed longs on their own to drop into a macro array, the channels split across three columns, or all five values at once.

When you paste a hex column into Excel on its own, nothing converts it — you get text. The output here is tab separated instead, so a single paste spreads across adjacent columns without the Text to Columns dance. Rows that are not valid colors are labelled in place rather than dropped, which keeps every line aligned with the source list. Everything here is arithmetic performed in your own browser; no palette you paste is transmitted anywhere.