Image Image Image Image Image Image Image Image Image Image

Being Brunel |

Scroll to top

Top

23 Comments

Inline Sub/Super- Script in Excel, and More…

Inline Sub/Super- Script in Excel, and More…

I have already mused on how often engineers use Excel to design; it’s a disproportionate amount, considering that engineering calculations isn’t really what Excel was designed for. This is most noticeable when it comes to engineering notation. Anyone who has attempted to implement a Eurocode design in Excel has probably already despaired at the lack of a sub-script button, or excessive character maps. This has been true for the last 11 versions, and isn’t set to change for the 12th.

Once loaded you can enjoy the world of sexy engineering calculation formatting!

I have written an Excel add-in called EngCel which does, however, make engineering in Excel a little more bearable:

Download EngCel

Super/Sub Script

EngCel provides in-line superscript and subscript shortcuts for excel using the fairly standard ^ and _ notations, respectively:

Examples of sub/super -script with EngCel

Examples of sub/super -script with EngCel

In all three of the above examples the text on the left shows what was typed into the cell, and the text on the right demonstrates what it will be converted to with the add-in installed. For example typing “|kN/mm^2” will appear as “kN/mm²“.

The syntax is fairly simple, starting a cell with the pipe (“|“) symbol, (that’s shift+\ on a QWERTY keyboard), indicates that the text contains in-line formatting. The character immediately after an exponent (“^“) will be in super-script, and the character after an underscore (“_“) will be in sub-script.

Sometimes, however, you will want to have multiple characters super/sub -scripted. Anything within curly-brackets (“{” and “}“) is considered a single character. The above examples show how putting “ck” and “-2” within the brackets has made all the enclosed characters super-script.

Symbols

Another gripe I have with the Excel formula bar is that symbols can’t be quickly inserted without going back and forward to the character map. EngCel solves this by adding the following in-line syntax:

Examples of symbols with EngCel

Examples of symbols with EngCel

By writing the name of a Greek character after a backslash (“\“) the actual letter will appear, as shown above. If the name is in lower-case, the letter will be in lower-case; if the name is in proper-case the letter with be a capital. This is demonstrated in the above beta/Omega example. There are also a number of mathematical symbols available, however listing them all would be a bit much for this overview; just try typing the name, chances are it’ll be there (e.g. \plusminus or \middot).

EngCel also allows in-line over-lining. Unlike the other syntax the negation (“¬“) symbol, (that’s shift + the key next to the “1” on the top line of a QWERTY keyboard, i.e. “`“), comes after the letter. This matches how an over-line is referred to in engineering calculations, e.g. x bar; which is also the example shown above.

Remember that all in-line formatting needs a | at the start so that EngCel knows to parse it.

Mixins

Finally engineering calculations have a lot of common and repeated elements. These are refereed to as ‘mixins’ in the EngCel code:

Examples of mixins with EngCel

Examples of mixins with EngCel

In these examples the left cell shows what was typed into the cell, the middle column is a demonstrative number, and the ‘PASS’ and ‘CHECK’ cells show what a cell, in that position, with an EngCel mixin would become. Mixins can be thought of as templates.

To use a mixin type the name of the mixin after the at (“@“) symbol. As using a mixin replaces the entire contents of the cell, a mixin will only work if it is the first thing after the pipe.

At the time of writing only two mixins are supported (both shown above): @pass and @ok. Both convert the cell into a simple utilisation check of the cell two columns to the left. If the utilisation is less or equal to 100% the cell becomes green and says “PASS” or “OK” respectively. If it is greater than 100% the cell becomes red and states “FAIL” or “CHECK”. The pass/fail indicator is commonplace in engineering calculations.

As I come across more elements repetitive enough to justify coding a mixin for EngCel, it will be updated; however the code is OpenSource- so feel free to contribute if you can.

Installing EngCel

Full details of how to install EngCel, and the complete in-line syntax available can be found on the GitHub EngCel Page. As it is an add-in it can be installed by any user on the system, you just need to follow the instructions from Microsoft relative to the version of Excel you have installed.

As a quick guide for 2010 users to load this add-in into Excel, click: File > Options > Add-Ins > Manage: Excel Add-Ins > Go > Browse > [Browse to add-in file] > OK > Tick: EngCel > OK. Why Microsoft has to make things so convoluted I’ll never know…

Once loaded you can enjoy the world of sexy engineering calculation formatting!

Download EngCel

Submit a Comment

Leave a Reply

Comments

  1. Ernest

    Thanks Thomas for replying to my MS answers question. This is exactly what I needed it for engineering notation in Excel!

  2. No worries, always happy to hear something I’ve written has actually helped.

    Spread the word!

  3. Kevin Redmond

    I’m having a compiling error when trying to use the carrot?

    • Kevin Redmond

      Specifically, it says “Method or data member not found.”

      When I press okay, it says the issue is at “If Target.LargeCount=1”

      I’m running on a Mac, so I don’t know if that changes anything.

      • Hi Kevin,

        First the solution:

        Replace the following code block:

        ‘cope with pre and post 2007 syntax
        On Error Resume Next
        IsSingle = False
        If Target.LargeCount = 1 Then
        If Target.Count = 1 Then
        IsSingle = True
        End If
        End If
        On Error GoTo 0

        With this:

        ‘cope with pre and post 2007 syntax
        ‘monkey patch from being brunel
        IsSingle = False
        If Target.Count = 1 Then
        IsSingle = True
        End If

      • Now an explaination:

        I’m not sure exactly why you’re running into this issue, I’m guessing that the version of Office for Mac you’re running doesn’t cope with the ‘.largecount’ method; but it _should_ have been caught by the error handling on the prior line.

        Try making the patch above- if it works, let me know, and I’ll try and get a fix into the next update.

        Keep me updated!

        • Kevin Redmond

          This patch totally worked, thanks a bunch!

        • No worries- glad I could get it to work 🙂

  4. Sam Coleman

    Will be very useful indeed.

    Tip to toggle it on and off during an Excel session:
    Bring up the Macro List (Alt + F8) and type wkbEngCel.Toggle_Pipe

    • Sam Coleman

      I should say wkbEngCel.Toogle_Pipe (spelling mistake in the 25/10/2012 version of the code).

    • Glad you’re finding it useful; I did consider putting a toggle, but it’s not often people use the | character, so I thought that’d be toggling enough!

      As for spelling mistakes; my only defence is that I’m an engineer!

      Sometime later this month I’ll be doing an update for EngCel with a few cumulative fixes; maybe I’ll even run it through a spell check; but I’m doubtful…

  5. Ed Dablin

    Very impressive. I’ve written something a lot more clunky with user forms but think I shall be superseeding it with yours =).

  6. Ed Dablin

    Tom suggested addition would be |@help which brings up a message box with a list of all commands/mixins

    • Sounds a good idea, although it might be a bit involved- I’ll see what I can cook-up for the next update.

  7. Tom

    This is extremely useful! I don’t know how I coped without it before, thanks!

  8. Richard

    I can’t seem to get it to work, followed all the instructions above, am i missing something? runing on Windows Excel 2016

    Very nice article btw.

Trackbacks

  1. […] the last piece of code I shared, preDict comes with an installation script. Just download preDict and run ‘setup.bat’. […]

  2. […] I hate Excel. I use it everyday, mostly for things it’s not designed for; like calculations, tables, data-mining and being operated by engineers. At the start of 2012 I started at project to build a ‘simple to use’ framework to write engineering calculations. Unfortunately I ended up getting side-tracked setting up Being Brunel and posing for photos (for the NCE- not just for pleasure). […]

  3. […] things I wrote was a subscript/superscript macro. However, I have now superseded it with Tom’s EngCel which is a lot less clunky; mine involved user […]

  4. […] For all of you who don’t know, EngCel was written to extend Excel into something engineers could use to actually write calculations. At its core it’s an easy-to-use in-line syntax for super-script, sub-script, Greek and mathematical characters and template replacement (called mixins). I’m only going to talk about the new features today, so if you want to remind yourself/learn how it works check out the original EngCel post. […]

  5. […] It should be noted that Visual Studio is pretty much the only Microsoft package I have anything nice to say […]

  6. […] Engcell – dodatek pozwalający szybko wpisywać indeksy dolne/górne, litery greckie itp. […]

  7. […] for the task is truly phenomenal. And whilst I might continue to despair at our industry’s constant abuse of spreadsheets, I can’t deny that they’re obviously […]