Spreadsheet Efficiency: VLookup and Conditional Formatting to Remove Errors

I’ve started working at a new hospital and it seems to be plagued by unwieldy spreadsheets that have a tendency to crawl through saves and then sometimes refuse to open. They are riddled with links and lookups to external spreadsheets and the whole thing feels fairly precarious.

I’ve been reading a bit about good spreadsheet design with a view to whittling down the complexity and discovered that lookup functions are particularly demanding on the system.

Now I’m quite a sinner when it comes to the use of the double lookup function to remove errors such as #N/A and #DIV/0!

Here’s an example of such a formula that returns a blank if the first lookup function results in an error:

=IF(ISERROR(VLOOKUP(L$7&$C13,'[WTE Apportionment Tables.xls]WTE APP TABLES SUB F’!$B:$K,9,0)),””,VLOOKUP(L$7&$C13,'[WTE Apportionment Tables.xls]WTE APP TABLES SUB F’!$B:$K,9,0))

It works fine, but it doubles up the number of lookups that Excel needs to run through and for a large spreadsheet this can really slow down the performance.

It seems that conditional formatting is a suitable alternative but one that I’ve always ignored because I didn’t know how to structure the condition to work for errors.

I do now though and here’s how:


1. Select the range you wish to apply the format to.
2. Goto Format – Conditional Formatting
3. Select Formula Is in the drop down box
4. Type the formula =iserror(ref) where ref is the reference to the first cell selected in the range
5. Hit Format and select the font colour to be white
6. OK – finished the job

Conditional Format 2

My current spreadsheet hasn’t grown that large yet but nevertheless it had 7200 of the double vlookups in 18 columns, by removing the unnecessary error catching lookup I dropped the file size by 22%.

As the errors weren’t actually removed, I had to use an array formula to get the totals for the column but that was included in the space saving above.

The array formula for summing a range with error values:


NB. The curly brackets are achieved by entering the formula using CONTROL, SHIFT, ENTER.

Colourful Spreadsheets

If you want to produce Excel reports with impact, you have to consider formatting as well as data content. One of the flaws of Excel is the
rather garish colour palette provided as standard, which seems to favour the production of colour clashing disasters.

Although it is possible to choose custom colours when formatting regions of your workbook it does require quite a lot of effort. Its a
shame you can’t select themes, as you can with other microsoft products eg Publisher, and have the colour palette change accordingly.

Excel palette

All is not lost though as you can create and modify your own palettes:
Open a spreadsheet and head for TOOLS > OPTION and select the COLOUR tab. You should now be presented with the standard colour scheme. You can click on each of the coloured squares and modify them, MODIFY > CUSTOM and enter the specific RGB values.

You’ll start yawning pretty quickly as it is a fairly tiresome task, mind you, once it is done you can save the completed workbook as a template and select your desired palette every time you start a new spreadsheet. If you really can’t be bothered, you are in luck as I’ve uploaded a blank workbook with the above colour scheme already sorted out. You can download it and apply the colour scheme to your own reports:
Custom Colour Palette Excel Spreadsheet

When a spreadsheet with the desired colour scheme is open you can copy it to any other open workbook, from TOOLS > OPTIONS > COLOUR just choose the spreadsheet with the desired colour scheme using the “Copy colours from” dialog box.

I’ve prepared a couple of additional colour palettes and thought I may as well share them online:

Here’s a bluer version:
and a greenish version:
Just click the image to download the relevant spreadsheet.

Values Stored as Text

One of my work colleagues is driving me nuts! On an almost daily basis he drags me over to his desk to correct the exact same problem, he thinks his VLOOKUP isn’t working properly but it is always the case, that in one sheet, his data is stored as text.

I can see the problem from across the room as his spreadsheet is littered with little green arrows informing him “value stored as text”!!!!! The exclamation marks are mine but by golly they are needed. I go across, convert his dodgy column to values and Bobs your Uncle, the VLOOKUP starts spewing out the answer.

This is going to keep rearing its ugly head for him, it seems to be an occupational hazard that data from certain sources will be interpreted as text by Excel. My problem is how to find the simplest technique of converting all these figures back to values so that he can start doing it himself. These are all the tricks I use:

1. Insert an additional (temporary) column to the right of the column containing the values formatted as text. Enter the formula =VALUE(cell ref). Copy this down to the bottom so you have a new column containing the text as values. Copy the new column and paste over the dodgy column using Paste Special As Values. Delete the temporary column. This is my method but my colleague isn’t loving it.

2. Enter the value 1 in an empty cell, select and copy. Now select the range of cells to convert to numbers. Choose Paste Special from the Edit menu, check the Multiply radio buttonand click on OK. This automatically forces Excel to replace the contents of the cell with the numerical equivalent of the text that was previously there. Quite a neat trick I think, perhaps I will try him with this one.

3. Another method is to use the Text to Columns option in the Data menu. This throws up the sort of dialog box used when importing text or csv type data. It works but you have to be careful if your column to be converted has any extraneous spaces or tabs which may result in data to the right of the affected column to be overwritten.

4. Final tip. The liitle green triangles in the top left of the cell, inform you of the format error. They also offer the option of “Convert to Number”. You could go through 1 by 1 clicking this option or if you are a bit nifty you can highlight the affected figures, hover over the error message and then select the “Convert to Number” option. This is definitely the easiest option but I often find it a bit fiddly to get the message to appear.

Value stored as text