In 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
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
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.
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.
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. Please see notes below.
- 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.
- IMPORTANT NOTE 1: Excel 2007 (at least detected so far) may be unconfortable with putting this add-in in a folder other than its own. Please copy this file inside the Microsoft’s Complmenets folder, located in %userprofile%\AppData\Roaming\Microsoft\Complements, and then actívate it in the Add-ins dialog as explained (it won’t be necessary to browse for it as it should directly appear among the listed add-ins).
- IMPORTANT NOTE 2: Windows will probably flag this complement as blocked as it was downloaded from the internet. This may cause some issues depending on your particular Office installation. Right-click on this file, choose Properties, and in the General tab at the bottom click on Unblock.
- IMPORTANT NOTE 3: If properly installed, as soon as you type “=DeltaE” inside a cell, a list of several DeltaE functions should appear. For some unknown reason (at least for me), this list does not appear in Excel 2007, but the functions work corretly. You just need to remember their syntax.
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.
Please download ColorTools clicking here.