15 Aug

Improving Excels spellchecker with highlighting

Excel does many things well, but it goes without saying many things could be vastly improved, or at the very least shown a bit of love by Microsoft.

One particular thing in Excel that that causes me some frustration is Excels inbuilt spellchecker.

Why? Because it’s infinitely more shit than the spellchecker in any of the other office products for a start.

This personal view is primarily born out of using Excel for the purposes of recording text based queries and responses as part of structural peer reviews. I use it as a log of sorts to log questions, responses and the like. The tabular nature of Excel lends itself to this application.

I’ve had logs that stretch to 40 odd A3 sized sheets full of queries when stuff goes a bit pear shaped and the designer has a ‘few’ (read ‘a lot of’) items to address, confirm, clarify, etc.

Often you’d ask some very long technical questions, all written into a single cell, or spanning several cells. Designer would respond with an equally long response if they bothered to read and more importantly comprehend what we were on about. Sometimes we’d get a single sentence response that sometimes suggested to me at least that this was going to be a hard road ahead. Sometimes they would answer 3/4’s of the query and ignore the rest, meaning we had to go back with further follow up questions.

Your typical queries in a peer review, one giant ‘wall of text’

Anyway, in this format I’d typically make a number of spelling and grammatical errors along the way because I’m an engineer, and not a master of the English language. Then the designer would also make some spelling errors in their replies. I guess engineers (especially structural engineers) are basically crap at grammar and spelling (well me anyway), give me numbers any day over words.

The one major downside that pisses me off with my Excel workflow in this respect is that there is no highlighting of any incorrect text as you type or when you use the built in spellchecker. You know that little reassuring squiggle under the words in Microsoft Word, telling you you’d screwed something up, but despite this Microsoft has got your back and will try save the day for you (thanks Bill). Now I can live with no real time feedback, I can see how that would be a pain, after all Excel is primarily designed as tool for numbers not words.

Well in Excel, Microsoft screwed you as far as words go, using the spellchecker there is no indication of the location where the error is in your ‘wall of text’. Scanning your ‘wall of text’ for the context in which Excels suggested change in spelling or grammar is to be undertaken is a right royal pain in the butt. Especially if the suggestion wants to correct something incorrectly, but the suggestion seems reasonable so you foolishly accept it. I’m a sucker for accidentally putting the last letter of a word on the beginning of another (typing at hyper-speed as fast as my two fingers can go), or finishing a sentence with a given word, only to start the next sentence with the same word and inadvertently forgetting to type the full stop or something.

Excels spell checker is rubbish at recognising these instances, for example take the following example, switching this first/last letter around. An error many of you have probably done before when typing. Microsoft Word is probably smart enough to auto-correct this as you type, Excel leaves it to you to sort out.

Think for example of the term top of concrete being typed as to pof concrete, Excel in its wisdom tells you somewhere in your ‘wall of text’ there’s a word pof that doesn’t make any sense and suggests you must have meant pouf as the top suggestion. If you blindly accept Excels best suggestion you now have to pouf concrete hidden away in your ‘wall of text’. This passes all other spellchecker attempts, but in reality makes no sense to anyone and makes you look like a dork when you send it out for review. While people may be able to decipher what you actually mean the little errors just look unprofessional.

If you just use the spellchecker without locating the exact terms in your ‘wall of text’ to review the context of the suggestions, then you can incorrectly correct something and be none the wiser if the suggestion sounded reasonable. In doing this you potentially lose the meaning of the point you were trying to make in the process. Obviously in this case I wouldn’t accept pouf, because it’s not a word I’d personally use in conjunction with concrete design, ‘your design is a bunch of pouf‘, I don’t think so. Now had it suggested poo instead… well maybe.

This lack of basic functionality to show the context infuriated me so much that I created a bit of an intermediate workaround in my workflow, and its this which I’m sharing with you all today.

The amount of time I’d spend looking for exactly where in the ‘wall of text’ I needed to amend ambiguous text which the spellchecker would find, just to ensure I was accepting a correct correction or amending it otherwise by hand drove me flaming nuts.

In a cell with several hundred words, trying to find where you had a repeated word messes with your mind, is it really a repeated word, or am I missing some punctuation between the two words, spelled one word incorrectly, etc. Where exactly did I write the the because I’ve read the ‘wall of text’ 15 f’ing times and still don’t see it. Maybe I meant then the… why can’t I find it…. arrrghhh!

This hack is still using the Excel spellchecker via VBA + manually checking for some other things that you cannot check using the in-built spellchecker via VBA. We are using the results to highlight all the words the code thinks are wrong in an initial pass, changing them to a lovely inviting shade of pink. This gives us the context needed to give us some idea of where to look when we actually use the spellchecker. That way you stand a fighting chance to see where Excel is taking the suggested changes from in the ‘wall of text’ when the spellchecker is run. I can immediately see the context and not bugger my carefully crafted words into a bunch of pouf.

Basically this add-in turns this ‘wall of text’:-

Into this ‘wall of text’ showing words/terms etc that Excel thinks are incorrect (dependent on the highlighting options you select):-

For some reason I always seem to type base don, instead of based on. Another favourite is base dont he instead of based on the. For some reason I just seem to type these phrases incorrectly almost every time if I’m going at speed! So I’ve added in the ability to recognise some of my ‘favourite’ misspelled/mistyped phrases as well, and highlighting those.

These can be accessed via the VBE, if you select the ThisWorkbook module, under properties you can toggle the ‘IsAddin’ property to False you’ll get access to the sheet they are stored on. Don’t forget to reinstate the property to True once you are finished entering your custom phrases and save any changes. Basically this allows you to personalise things to some degree, and to delete my sample phrases if you don’t type like you have 2 fingers like me and don’t have the base don issues that I’m plagued with.

Set this property to False to alter the custom phrases to suit your commonly made typing mistakes
Add custom phrases to highlight to your hearts content….

So basically the code checks the following and highlights these general cases:-

  • Custom phrases
  • Repeated words
  • Misspelt words

The interface driving this awesomeness is quite simple and simply mimics the in-built Excel options, select the options you want to highlight on the left, then select the option you want to run on the right, you can find the icon to run it on the ribbon on the Home tab:-

Click this…
…for awesome

Now because it’s checking word by word, phrase by phrase, it can be a bit slow if you are doing the entire sheet/usedrange. Especially if you have 40 A3 sized pages of comments to process. Get up and walk round the office or something, basically look busy while it runs. Looking busy is the key to success in any modern engineering office. As soon as you remotely look like you’ve got nothing to do, some smart arse gives you more work!

I wrote this Addin quite some time ago with minimal code comments to tell me what is going on, so it took a while to work out exactly what I had done previously to make it good for prime time usage (comment your code people!)

So in recently looking back through the code I wrote, I realised I could speed things up substantially if I stopped checking if each and every word was correct, provided I’d previously checked that exact same word before there was no need to check it again.

So I implementing a poor mans cache of sorts, as one big long string of all the previously checked words. I then hit up this string to see if the current word had been checked previously, if it is present in the cache then there is no need to check the spelling again. It’s a pretty low tech solution, but holy smokes, that speeds things up enormously as checking the spelling of a word using Excel is quite computationally expensive.

My test spreadsheet of 14 A3 pages of queries previously went through in about ~49 seconds. With the cache of previously checked words implemented this reduced to ~12 seconds on a good day.

Thats a huge improvement, so maybe you can stay in your seat after all. I’m sure someone’s going to point out some other smarter way of doing that bit, but if it speeds things up further then by all means let me know.

Once you’ve finished, run the normal Excel spellchecker and/or make any corrections manually, then simply correct the format of the pink text back to whatever it was before and you’re good to go. Job done, #Profit.

You can find the add-in here on my Github page.

Update…

I recently started a new job and installed the Addin on my shiny new computer. Only to realise when I extracted/separated the code from a larger more complex Addin I’d written that I inadvertently left in some code that worked on my computer but wouldn’t have worked on anyone elses computer. Engineer 0 vs Sheep 1.

I’m guessing because no one reported the issue that no one downloaded it. Github tells me people have cloned it, but who knows. The only thing thats certain is that I’m a dumbarse.

Anyway I’ve updated the Addin to version 1.100 fixing the issue! Enjoy!

Let me know on Github if you’re having any issues or have ideas for extending any of the checks.

Leave a Reply

Your email address will not be published. Required fields are marked *