Methods to Minimize Spreadsheet Errors: Part 1 - Cell Linking

Locked lesson.

Upgrade

Quick reference

Methods to Minimize Spreadsheet Errors: Part 1 - Cell Linking

Understand three of the most common errors and explore how to use cell linking to minimize formula errors. 

When to use

Link to cells instead of hard-coding values whenever possible to minimize errors and make formulas easier to audit and update. 

Instructions

Worksheet Errors

There are many types of errors that we might come across when working in Excel. We might get an error when typing a formula or we might get a pop-up error when we've entered something incorrectly.

Three common errors are #VALUE, #NAME, and #NA. 

#VALUE

The #VALUE error usually occurs when we've selected a cell that doesn't make sense to the formula. For example, if we have a SUM calculation in a cell, =SUM(A1*B1) and A1 contains text, not a number, we will receive the #VALUE error. 

In more complicated formulas that refer to several cells, we can make auditing errors easier by using Trace Precedents. 

  • Click on the cell that contains the formula. 
  • From the Formulas tab, in the Formula Auditing group, click Trace Precedents. 

This will show which cells are being used in the formula making it easier to audit and fix. 

  • From the Formulas tab, in the Formula Auditing group, click Remove Arrows. 

#NAME

The #NAME error usually occurs when Excel does not recognize the function or range name used in the formula. The majority of the time this is down to a simple spelling error. For example, we may have accidentally typed =SOM instead of =SUM. 

#NA

The #NA error occurs when the formula cannot find the data we've tried to return. For example, if we are doing a VLOOKUP and using the pizza name to look up the price in the table but the pizza name doesn't exist, we will get an #NA error. 

Do Not Hard-Code Values

To minimize the number of errors in a spreadsheet, we shouldn't hard-code values into cells wherever possible. 

For example, this calculation, =SUM(A1*20%)+A1 contains a hard-coded value. 20% (sales tax) has been hard-coded into the formula. If the sales tax amount changes, we would need to find all occurrences of 20% in our workbook and update the formulas to reflect the new sales tax amount. 

This amount of manual work means errors are more likely. 

Instead, it's much better to put the sales tax amount in its own cell and link to the cell in the formula, =SUM(A1*J1)+A1. If we use cell linking and the sales tax amount changes, we only have to update the value in one place and all formulas that link to the cell will automatically update. 

Hints & tips

  • #NA errors can be left in the spreadsheet but do look messy. Our recommendation is to fix the error or add IFNA error handling to the formula. 
Login to download
  • 00:04 Throughout your Excel working career,
  • 00:06 you will invariably come across different types of Excel errors.
  • 00:10 Those errors might occur in a pop-up box that appears on the screen, or
  • 00:14 we might get an error once we've typed a formula into a cell.
  • 00:18 And there's a whole host of different errors that we might get.
  • 00:20 But let's just take a look at a couple of them.
  • 00:23 Because in this lesson,
  • 00:24 I want to explore how we can implement different techniques in Excel to minimize
  • 00:29 the amount of errors that we're not only getting in our spreadsheet, but also how
  • 00:34 we can minimize errors by controlling the data that goes into the spreadsheet.
  • 00:39 So, let's take a look at our first worksheet error types.
  • 00:43 Now, this is a pretty convoluted example.
  • 00:45 I've just made a couple of very obvious errors just to throw up these
  • 00:49 different error messages in those total columns.
  • 00:52 So, if we take a look at the first table again,
  • 00:55 this information is all related to Marco's Pizza place, and
  • 01:00 you can see here that in the total column I have a value error.
  • 01:04 And if I click on this column, and take a look at the formula, it's very easy for
  • 01:09 me to work out exactly what this formula is doing and why I'm getting this error.
  • 01:14 So, I can see here that I'm doing C7 multiplied by B7 to get my total.
  • 01:20 So what is cell C7?
  • 01:22 That's the quantity, 50.
  • 01:24 That's absolutely fine, but I'm multiplying it by B7,
  • 01:29 which has the name Classic Deluxe Pizza.
  • 01:32 Now, Excel can't multiply text by a number,
  • 01:35 which is why I'm getting this value error.
  • 01:37 So clearly here, when I've been putting this formula into the spreadsheet,
  • 01:41 I've accidentally selected the wrong column of data.
  • 01:44 Now, if I had a much more complex formula in here, and I needed to try and
  • 01:49 find out exactly which cells are being referred to in this formula,
  • 01:53 this is where I could use Excel's trace precedence utility.
  • 01:57 So, if we jump up to the formula's ribbon, and in the formula auditing group,
  • 02:02 notice the top one here, trace precedence, that will put an arrow on the screen,
  • 02:07 which basically shows us the cells that are contained within this formula.
  • 02:11 So, where we have this in the line that's showing us which cell is used in
  • 02:16 the formula.
  • 02:17 So, it's telling me B7 and C7 are used in this formula.
  • 02:22 So, it makes it a lot easier for me to see and
  • 02:24 effectively audit my formulas to work out why I'm getting this error.
  • 02:28 Now, mine's a fairly straightforward calculation, so
  • 02:31 I'm just going to remove the arrows and make the correct.
  • 02:34 So instead of B7, what I actually want is D7 in here because we want
  • 02:39 to multiply the quantity by the Price Per Unit.
  • 02:43 So, I'm going to make that manual change.
  • 02:44 Let's hit Enter, and then I can simply double-click to copy that down, and
  • 02:49 my error is fixed.
  • 02:50 Let's take a look at the next table, which has a name error.
  • 02:55 If I click on this cell and take a look-up in the formula bar,
  • 02:58 I can see that I've made a mistake in the actual name of the formula that I'm using.
  • 03:03 So instead of typing SUM, I've typed in SOM instead,
  • 03:06 which is why we're getting that name error.
  • 03:09 So again, a really simple fix, we just need to change it,
  • 03:14 and then we can double-click to copy that down.
  • 03:17 Now, notice here in both of those examples can you see what's happened where we have
  • 03:22 this total.
  • 03:23 When I've copied this formula down, it's actually removed the bottom border from
  • 03:27 these cells, and this is something you might find that happens to you a fair bit.
  • 03:32 Now, we don't need to go in and
  • 03:33 start reapplying formatting to the bottom of this cell.
  • 03:36 Let me show you another method.
  • 03:38 I'm going to Ctrl+Z a couple of times just to put this back to how it was previously.
  • 03:43 So, let's go in and correct our error again.
  • 03:47 I'm going to type in =SUM.
  • 03:48 Now, when I copy down, let's drag the fill handle down.
  • 03:53 Notice, we get a little tag at the bottom here with some autofill options.
  • 03:58 So I can click this, and I can say Fill without formatting.
  • 04:03 And what that's going to do is it's going to fill that number down, but
  • 04:06 it's not going to copy across with it the formatting of this specific cell,
  • 04:10 because this cell doesn't have any borders around it,
  • 04:13 which is why we weren't getting a bottom border.
  • 04:15 So, a good little trick because that can be really annoying.
  • 04:19 Now, another type of error that you might get is an N/A error.
  • 04:22 You can see at the bottom here, I've just created a data validation drop down
  • 04:27 list with pizza names from the table above.
  • 04:30 Now, if I was to select Thai Chicken Pizza, and
  • 04:32 then for some reason I deleted this out of the table,
  • 04:36 I'm going to get an N/A error because that item no longer exists.
  • 04:39 So, these are examples of errors that you might get in your spreadsheet, and
  • 04:44 we're going to take a look at some ways that we can avoid them.
  • 04:47 So, let's move across to our cell link worksheet.
  • 04:50 Now, one way that you can minimize errors, and also make it a lot easier for
  • 04:55 people to update your spreadsheet is to link to cells as opposed to
  • 05:00 hard coding values into cells.
  • 05:02 So, let me show you what I mean by that.
  • 05:04 Once again, I just have a short list of the different pizza names,
  • 05:08 the Price Per Unit, and I can see over here there is a Sales Tax that needs
  • 05:13 to be applied to each of these prices.
  • 05:15 So, I need to work out what the total plus the tax is of these particular pizzas.
  • 05:21 So, this would be a simple sum calculation.
  • 05:24 I'm going to type in SUM,
  • 05:26 I'm going to do Price Per Unit multiplied by the Sales Tax, which is 20%.
  • 05:31 And we just want to make sure that we plus the Price Per Unit,
  • 05:37 let's Ctrl+Enter and copy it down.
  • 05:40 It works, but I have a hard coded number in this formula.
  • 05:45 So, if the Sales Tax changes, it means I'm going to have to come into my
  • 05:50 spreadsheet and find all of the cells that contain the value,
  • 05:54 20%, and update it to whatever the new Sales Tax is.
  • 05:58 And that can be really time-consuming, and you're likely to miss a cell that contains
  • 06:03 that value, which will then throw off all of the numbers in your spreadsheet.
  • 06:08 So, a much better way is to have the Sales Tax in its own separate cell,
  • 06:13 and use cell linking, instead.
  • 06:16 So, if we were going to do that, this calculation would be SUM,
  • 06:21 Price Per Unit multiplied by the Sales Tax.
  • 06:25 And remember, we need to employ absolute referencing here because when we
  • 06:30 copy this formula down, we don't want this cell reference to also move down.
  • 06:35 Remember, that is the default behavior in Excel.
  • 06:39 So we need to say to Excel, always refer to cell G7 in this calculation.
  • 06:44 So, we need to make the cell reference absolute by pressing
  • 06:49 the F4 key once to put those dollar symbols in.
  • 06:52 I can then close off the bracket, we need to plus the Price Per Unit.
  • 06:58 If we don't do that, we're only going to get the total tax amount,
  • 07:03 not the total plus tax.
  • 07:04 Ctrl+Enter, double-click to copy down, and now this makes this formula
  • 07:10 a lot easier to update, because if that Sales Tax changes to 30%,
  • 07:15 all of my formulas are going to update automatically, and
  • 07:19 I'm not having to go in and risk missing anything.
  • 07:22 So don't hard code numbers, always link to cells wherever possible

Lesson notes are only available for subscribers.

Gift this course