EngCel: Colours, Names and More
EngCel is a free add-in for Excel that I write and maintain. At its core EngCel it’s an easy-to-use in-line syntax for super-script, sub-script, Greek and mathematical characters and template replacement (called mixins). If you want to remind yourself/learn how it works check out the original EngCel post.
One of the most popular things about this blog (except the amazing drawings, obviously) is the EngCel add-in I shared a few months ago. As with all the software I write, however, it rarely remains static; over time adding new features as and when my frustrations with Excel spur me on. So this week I thought I’d update you all on some of the newest features, and perhaps cajole you into downloading the latest version.
One of the first things I look for in any development environment is syntax highlighting; and although I’ll happily acknowledge that engineering calculations in Excel isn’t exactly programming, however it’s close enough to be improved by some dynamic colouration (or mod_colour, as EngCel knows it).
With the new mod_colour switched on, any new formula will colour depending on its usage:
- Formulas that are neither used by, or use other cells will turn grey (unused value)
- Formulas that are used, but not used by, other cells will turn red (input value)
- Formulas that only reference a single cell, without calculation, will turn orange (refereed value)
- Formulas that rely and are relied upon by other cells will turn blue (function)
- Formulas that use other cells but are not used themselves will turn green (result value)
At a glance you’ll now be able to tell which cells aren’t being used, which are the principle inputs and which are the results of a calculation. To enjoy colourful formulae just use the mod_colour mixin |@colour (there’s a ‘u’, I’m English), which will toggle colouring formulae on and off across the application.
Unfortunately, however, it’s a limitation of Excel that you can’t undo the changes a macro makes to your spreadsheet; which essentially means that by switching on mod_colour you’ll lose your undo function. Personally I don’t find this much of a loss compared to the increased clarity gained, but as a result mod_colour is turned off by default.
As well as the pass/fail ok/check mixins EngCel was originally released with, there are now some new ones:
- |@!ok and |@!pass do the reverse of their namesake mixins, which is useful when you’re doing a factor of safety, rather than utilisation, calculation
- |@eng formats the cell as an engineering number; that is to say something which is grouped in exponents of three, e.g. x103, x106, x109.
- |@help generates a new sheet which provides an introduction to EngCel. as well as a character map showing you the syntax for the in-line symbols
At the moment EngCel uses hard-coded mixins; I’m still looking for a way to allow users to safely add their own ones- but at present the limitations of Excel are thwarting my every attempt.
Not too many people know that Excel has the ability to use ‘names’ instead of ‘references’ for cells; e.g. A1 could just as easily be called “first_cell“. For actual tables this doesn’t make too much sense, and therefore the naming method in Excel is a tad cumbersome. For engineering calculations, however, naming cells is a god-send; I don’t know anyone who would prefer to know that D3 = D2*D1, when dead_weight = mass * gravity is readily available.
EngCel now provides an extension to the in-line formatting syntax that allows you to name the cell to the right with the words in the cell to the left; confused? Don’t be. All you need do is write the name of the symbol in the first column, in the adjacent column use EngCel in-line notation to form a lovely looking symbol and follow it with ‘=>’ to name the cell in the next column along. For example, entering the following in Excel:
As this is pretty much the way every engineer I know writes calculations, it’s pretty easy to get used to and can make coming back to, or checking, a spreadsheet a breeze. Just note the Excel has its own conventions with naming, so stick to unique, character (no spaces) only variable names if you want an easy life.
So that’s it on EngCel latest features. I always keep the GitHub page for all of my software up-to-date, so if you want the latest and greatest features keep an eye on: https://github.com/thomasmichaelwallace/EngCel.