Skip to content

Color calculations in Excel

xla-150x150In my job I often have to do some color difference calculations in Excel. This application, naturally, has no specific functions to do that. Anyone can manually enter the traditional ΔE formulae to compute that difference (better known as ΔE CIE 1976) which is written as

DeltaE = RAIZ((L1-L2)^2+(a1-a2)^2+(b1-b2)^2)

where L1, a1 and b1 on one side, and L2, a2 and b2 on the other, are the respective Lab components of each color to compare. Usually each value are placed in his own cell; for instance, if these six numbers are placed in cells A1 to A6, to compute ΔE in cell A7 we should enter there

=SQRT((A1-A4)^2+(A2-A5)^2+(A3-A6)^2)

which is a tedious math expression to manually write. Wouldn’t it be better if Excel had a DELTAE function for this purpose?

Well, Excel allows us to add modules with extra functions known as Add-ins; taking advantage of this, I developed one of such modules to compute color differences with any of the following algorithms:

  • Delta E (CIE 1976)
  • Delta E (CIE 1994)
  • Delta E (CIE 2000)
  • Delta E (CMC)

The first three algorithms are succesive versions established by CIE; the last one is an independent algorithm from CMC (Color Measurement Comimitee) of the Society of Dyers and Colourist, England. Apart from the first, simpler formulae, which is based on the Lab color space, the rest are based in the LCh model of that space, so in these cases Lab values are always converted to LCh prior to calculation. The resulting formulas (somehow complex to show here) can be found in the excellent site of Bruce Lindbloom and also on Wikipedia.

Differently to CIE formulas, CMC ΔE is parametric; such formulae requires to define two parameters named l and c which are luminance and chroma weights. For color difference on an “acceptability” level (meaning a noticeable but not objectionable difference), usually l and c are respectively 2 and 1; for an “unnoticeable” level, those values are both 1.

This add-in, called ColorTools, adds to Excel the following functions:

  • DeltaE(L1, a1, b1, L2, a2, b2)
    ΔE calculation using 1976 CIE ΔE formulae.
  • DeltaE94(L1, a1, b1, L2, a2, b2)
    ΔE calculation using 1994 CIE ΔE formulae.
  • DeltaE94Textiles(L1, a1, b1, L2, a2, b2)NEW!
    ΔE calculation using 1994 CIE ΔE formulae with suitable parameters for textile industry, according to Bruce Lindbloom.
  • DeltaE2000(L1, a1, b1, L2, a2, b2)
    ΔE calculation using 2000 CIE ΔE formulae.
  • DeltaECMC(l, c, L1, a1, b1, L2, a2, b2)
    ΔE calculation using CMC ΔE formulae with parameters l and c.
  • DeltaECMCgraph(L1, a1, b1, L2, a2, b2)
    ΔE calculation using CMC ΔE formulae for graphic arts applications with acceptability parameters, same as using DeltaCMC with l = 2 and c = 1.
  • ColorToolsVersion()
    Returns this add-in’s version number. Current is 1.3.

These formulas have been verified using several numeric examples, comparing these results to those obtained from the color online calculator from Lindbloom’s Color Difference Calculator page.

Install

To use this add-in just download and unzip it in a folder of your choice, and then install in Excel depending on your version:

  • In Excel 2000/2003, go to Tools > Add-ins, click Browse… and select the unzipped file;
  • In Excel 2007, click on Office icon (top left), click Options, click Add-ins, then Go…, then Browse… and select the unzipped file.
  • In Excel 2010/2013/2016, click on File (“backstage” menu, top left), click Options, click Excel Add-ins, then Go…, then Browse… and select the unzipped file.
  • In any case, you can verify proper installation by opening again File > Options > Excel > Add-ins and checking ColorTools is displayed as an installed add-in.

Legal

You may use this add-in free of charge, as long as it remains unchanged and its origin mentioned. If you want to link this add-in from your site please use this URL.

Download

Please download ColorTools clicking here.