## Vlookup Errors – The Third Way

I’ve just published a post detailing a method of removing the errors returned by vlookup or hlookup utilising conditional formatting.

I had previously been using the double vlookup method to replace errors with blanks or zeros but then discovered that these excessive lookup functions had a tendency to bloat my spreadsheets.

While the conditional format method works, I can’t say that I particularly like it so I’ve been on the hunt for another method.

My first double lookup error hiding method looked like this:

=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))

and here’s method no 3 that feels somewhat neater:

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

That’s a working formula but it is a bit confusing, so here’s a simpler example the syntax:

=IF(COUNTIF(A1:A10,”Some Value”),VLOOKUP(“Some Value”,A1:B10,2,FALSE),0)

The COUNTIF function is a logical function and will return TRUE or FALSE depending on whether “Some Value” is found in the range A1:A10. The IF function follows the format =IF(logical test, value if true, value if false). So if the COUNTIF function is TRUE the VLOOKUP will run, if it is FALSE the value 0 is returned.

## 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

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:

{=SUM(IF(ISNUMBER(H12:H418),(H12:H418)))}

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

## Adding an Image to the Header or Footer of Every Sheet

From Excel 2002 onwards you can enter an image, probably a logo, into the header or footer of your worksheet. This code enables you to create a macro that will automate the addition of the logo to every sheet in your workbook.

 1234567891011121314151617181920 Sub MultipleHeader() 'created 10/01/2008 by Angela Wolff Application.ScreenUpdating = False 'stops the flashing    Dim WS_Count As Integer    Dim I As Integer    ' Set WS_Count equal to the number of worksheets in the active workbook.    WS_Count = ActiveWorkbook.Worksheets.Count        ' Begin loop.    For I = 1 To WS_Count       ActiveWorkbook.Worksheets(I).Activate       ActiveSheet.PageSetup.RightHeaderPicture.Filename = _         "C:\Document\hhnt_007528.jpg"  'obviously change this to the image and path of your desire.     ActiveSheet.PageSetup.PrintArea = ""     With ActiveSheet.PageSetup         .RightHeader = "&G"     End With    Next I End Sub

NB. The With End With code is only really required if you are going to change a number of parameters at once.

## Scroll Bar Chart

This is a very neat advanced Microsoft Excel technique that enables the spreadsheet user to alter the visible range displayed. This can be achieved by defining the start point within the spreadsheet data series and also the number of data points to display in the Excel spreadsheet chart.

All the examples are available to examine in the Microsoft Excel example spreadsheet: Scroll Bar Chart

In this example I have a full years data displayed in the worksheet range \$A1:\$C53.

We need to create the two slide bars on the Excel worksheet.

Open the forms toolbar within Excel and select the scroll bar option and then drag out the form of a horizontal bar. You can move this around to finalise both size and positioning.

Right click over the selected scroll bar and choose Format Control.
In the box called Cell link write \$L\$19. This is the cell in which the incremental value of the slide bar will be recorded.
Repeat the process by drawing another scroll bar and assign its cell link to \$L\$21.

If you now move the arrows either side of the two scroll bars you will see that the values in the corresponding cell link will change. These will form the values for the height of our displayed values and the starting point respectively, and as such they will appear in our offset function defining the y values. To make these offset formulas easier to read I suggest we name the cell links.

To add a name to a specific cell or area – from the Insert menu, choose Name and then Define or use the keyboard shortcut CTRL F3. In the “Names in workbook” box type in a descriptive name for your cell, in this case I have used ZoomValue.
In the “Refers to” box we simply type \$L\$19.

Similarly for ScrollValue the name is defined as:

We need to set a dynamic range for the x and y values. So, taking the y values as our base we can use the offset function to define the values to be plotted.

The OFFSET function is in the following format:

ref,rows,cols,height,width.

Choosing \$C\$2 as our reference cell, we now require a measure of our starting point (given by our SCROLL slide bar), the cols section = 0, the height relates to the number of rows of data to be charted and is returned by our ZOOM slide bar and the width = 1.

So define the named range =

OFFSETSheet1!\$C\$2,ScrollValue,0,ZoomValue,1)

The x values or labels can simply be referenced from the y values:

When you create the graph you need to the dynamic ranges for the x and y values, so in this example the formulas would be:

x axis values = Sheet1!XValues
y axis values = Sheet1!YValues

This is your dynamic chart completed. Try the arrows on both scroll bars to see how they alter the display.

You can hide your data table so long as you remember to change the chart options so that hidden cells are plotted and the cell links can be formatted so they too are not visible.

See the Microsoft Excel spreadsheet example Scroll Bar Chart.

## Dynamic Chart

This Microsoft Excel tutorial, shows an example of how to create a dynamic chart by moving the charting range as data is added to the spreadsheet.
Here we start with monthly data for Retailer A, showing Petcare sales complete up to July.

A standard Excel line chart of this data would be shown thus:

To reduce the displayed area to the existing data within the actual column, while allowing it to expand automatically as more months figures are added, we use defined names.

Insert menu, choose Name and then Define or use the keyboard shortcut CTRL F3.

In the “Names in workbook” box type in a descriptive name for your chart data, in this case I have used ChartActual.
In the “Refers to” box we use the offset function to define the data within column C.

The offset function is written in the following format:

=OFFSET(Ref,rows,column,height,width)

The COUNTA function is the vital part that makes the range responsive to the number of cell entries in the Actual column. This function returns the number of non-blank cells in a range. It is written in the height section of the OFFSET function and therefore determines the number of rows that will make up the name ChartActual.

In this example the reference is \$C\$1 which corresponds to the “Actuals” title, the resulting area starts 1 row down and 0 columns across. The number of rows corresponds to the number of entries in column C less 1 for the title entry and the total column width is 1.

The same procedure is now used to define names for the ChartLabels and ChartTargets.

The OFFSET function is again used, this time referencing the newly defined ChartActuals. So ChartTarget starts on the same row as ChartActuals but is 1 column to the left of it. When the height and width component of the OFFSET function is not stated the height and width will be the same as the reference.

The final stage involves changing the graphs data source.
Right click within the graph and choose Source Data.

Select Actual in the Series box and in the Values box type in the newly defined name, in this case Sheet1!ChartActuals.
Perform the same action for Target and use Sheet1!ChartLabels to define the Category (X) axis labels.
Click OK and the chart should now look like this but is in addition fully responsive to new entries.

## Sumproduct

All the worksheets (and some extras) shown in the microsoft excel tutorial below are included in the example spreadsheet, available to download Sumproduct.xls

This is quite a special excel spreadsheet function but the online help available from microsoft is unfortunately pretty poor.

It works as an advanced form of the SUMIF formula where you can sum a range of values based on a number of different criteria.

Here is the simple formula layout or syntax:

=SUMPRODUCT((Condition 1)*(The range to sum))

or slightly more complex:

=SUMPRODUCT((Condition 1)*(Condition 2)*(The range to sum)

the “*” symbol between conditions represents an AND a “+” symbol would represent an OR.

Here is a simple example, of a schools course grades displayed in the following spreadsheet table,

You will probably want to summarise this to show the total number of students passing the course in each grade.

This makes use of the simple SUMPRODUCT formula in cell F2:

=SUMPRODUCT((\$B\$2:\$B\$15=E2)*(\$C\$2:\$C\$15))

the use of absolute referencing here enables the formula to be copied all the way down the table.

Sum the results in column C where the values in column B = “A”

Moving on to a slightly more complex example, we could increase the summary table to show each students performance across the different grades.

This requires the addition of another condition – namely STUDENT equals.

The spreadsheet formula in cell I2, which can again be copied across is:

=SUMPRODUCT((\$B\$2:\$B\$15=I\$1)*(\$A\$2:\$A\$15=\$H2)*(\$C\$2:\$C\$15))

Sum the results in column C where the grades = “A” and where the student = “Adam”

To see these powerful excel functions in action and utilising the Condition 1 OR condition 2 terminology, download my example microsoft excel spreadsheet.

## Named Ranges and the SUMIF Function

Named ranges are useful in Excel as their use can make formulas much more understandable. You can name:
columns and rows
or individual cells that may act as constants, for example the VAT value

I’ve used the following named ranges in the SUMIF example below:

As usual with Excel, there are a number of ways to do the same thing. To name a range you can highlight the area and then either:

1. Insert>Name>Define and then choose an appropriate title
or
2. Go to the name box in the top left hand corner of the formula bar and type a name in the cell

To confirm the region that the named ranage refers to, use the drop down Name Box and select the range of interest.

The SUMIF function is in my list of all time useful functions, the excel help is very good, but here is my tutorial with real world finance examples. The SUMIF function adds the cells specified by a given criteria.

The formula syntax is:

SUMIF(range,criteria,sum_range)

where
“Range” is the range of cells you want evaluated.
“Criteria” is the criteria in the form of a number, expression, or text that defines which cells will be added. “Sum_range” are the actual cells to sum.

Here are some uses of the SUMIF function, utilising the data sown above.

=SUMIF(CC,7001,Actual)

where CC = 7001 return the sum of Actuals

2. To return the total annual budget for Hilliary Hospital SLA and Additions:
=SUMIF(JC,TRAH,AnBud)

where JC = TRAH return the sum of AnBud

## Sub Totals and Pivot Table Reports

Removing sub totals from pivot table reports

When you add detail to pivot table rows, Excel will automatically add a subtotal row. This can very quickly litter your pivot table with unnecessary detail.

I remove the unwanted subtotals by right clicking on one of the totals and selecting “Hide”.

Adding sub totals to pivot table reports

Having removed all my unwanted subtotals, I will typically decide to rearrange the ordering in my pivot table and find I am missing a vital subtotal. It is possible to reapply subtotaling though – so don’t panic.

Following on from the examples above, it may be preferable to have the Cost Centre description appearing first rather than the cost centre code (which means nothing to anyone outside of finance).

To add the subtotal against this description, right click in any of the description values, choose “Field Settings” and then make sure the radio box labeled “Automatic” is selected.

This enables you to add and remove subtotals at will.

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.

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:

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:

## 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.