<

I Don’t See Any Errors – Why Isn’t It Working?!

You can do almost anything in Excel, but you still have to follow the rules.

You just finished spending a lot of time building your spreadsheet to make your life easier – and it doesn’t work!  So now, instead of spending time doing productive work, you have to spend time trying to figure out why you aren’t getting the results you want.

One of the best features of Excel is its flexibility.  There is virtually no limit to the number of ways you can build a spreadsheet, to customize it to your particular needs.  That’s why Excel is such a great, powerful tool to use in managing your business, figuring out your budget or finances, or keeping track of personal items or business inventory.

You cannot just do whatever you want and get the results you expect.

However, you only need to master a few basic but essential concepts.  If you do, you will spend much less time working on Excel and more time on the business activities that matter.

Common Spreadsheet Error #1

Your Data Is Not Normalized

Build your spreadsheet the right way, it will work correctly.

Brick walls, if properly constructed, are very sturdy and will stand for many, many years. The wall has a vertical column with rows of bricks following a specific pattern.  Failing to build the wall this way means it won’t be long before the entire wall collapses.

What does this have to do with spreadsheet errors?  Just like a brick wall, an Excel spreadsheet has to be constructed correctly if you want it to function correctly.

Excel spreadsheets are organized in rows and columns for a reason.  This structure – called a flat database – is the best way to organize your data for quick and efficient analysis.

What You Need to Know

A database is a collection of Fields and Records.

A Field is a column in an Excel spreadsheet.

A Record is a row in an Excel spreadsheet.

A field contains one unit of information in a record.  For example, in a record of a customer’s data, there will likely be fields for such items as first name, last name, address, city, state, zip code.

A record contains all relevant information for one unique item in the database.  Examples include sales transactions, customer information, and inventory specifications.

Here’s an example of a flat database.  All of the information is fictional.

Notice how there is no duplication of data.  Each field contains only one item of information.  Every record is related to only one person.

 

What You Need to Know

•  Your data is not normalized if you have duplicate data.

•  Each record must be unique.

 

LET’S LOOK AT AN EXAMPLE

A good example is the use of dates.  If you want to have a record of sales for each month, most people set up one column for each month.  The thinking is that each month is a separate field, so it needs its own column.  So they end up with something like what you see below.

 

This format is incorrect.  The data is not normalized correctly because we only have one record that shows sales for all months.

What we actually want is 12 separate records.  We need one record for each month.  Each record should show only the total sales for that month.

Where is the error?  In the field name.  The field name should be “Month”, not the name of each individual month.

What is the correct way to organize our data?  Well, in our example, we have two pieces of information:  the month of the sale and the total of sales for the month.  So we only need two fields (columns) to record our data – Month and Sales.  The proper way to normalize the data is shown below.

Now the data is normalized correctly.  We have what we need, which is a list of 12 unique records, one for each month, with total sales for the month.

Always make sure you normalize your data correctly.  When you do, you will find that creating and updating reports is faster and consistently more accurate.

Common Spreadsheet Error #2

You Did Not Use the Correct Type of Cell Reference

Is Excel looking in the correct location for the data it needs?

One of Excel’s best productivity features is the ability to copy data from one cell and paste it to other cells in the spreadsheet.  This feature is very useful if you have a formula you want to use in more than one cell.  Instead of typing the formula over and over, you can just copy it from one cell to another.

Doing so ensures that your formula is exactly the same in every cell – you won’t get an error because you made a typo.

However, you have to know the difference between a relative and an absolute cell reference.  If you don’t, you will have errors in your spreadsheet.

What You Need to Know

Relative cell reference – changes if you copy or move a formula to another cell.

Absolute cell reference – does not change if you copy or move a formula to another cell.

The dollar sign ($) is used to denote an absolute cell reference.

• Relative Cell Reference – A1

• Absolute Cell Reference – $A$1

Relative and absolute cell references can be a difficult concepts to understand at first.   So don’t get frustrated if you don’t understand it right away.  Once you get it, though, you’ll be in the upper percentile of skilled Excel users.

The first step is to understand what we mean by a “cell reference”.  Simply put, a cell reference is the cell to which a cell or formula refers.  In Picture 4 below, the cell reference is in cell D4 (shaded green) and it refers to cell E5 (shaded yellow).

Understand that Excel views this picture differently than you do.  You, the user, see the cell reference in cell D4 as an arrow, pointing you to cell E5.  But Excel sees cell D4 as a map, with directions on how Excel gets to cell E5 from cell D4.  Put another way:

You see the cell reference as a location.

 

Excel sees the cell reference as the path to a location.

LET’S LOOK AT SOME EXAMPLES

Here’s how it works.   Refer to the picture below as you review each step.

  1. In cell A1 (the active cell), you enter the cell reference “=E6.”
  2. Excel sees it takes two steps to move to cell E6.
  3. First, Excel must move right 4 columns relative to cell A1.
  4. Second, Excel must move down 5 rows, relative to cell A1.

So, to Excel, “=E6” means “move right 4 columns, then move down 5 rows”.

 

Make sure you understand what is happening in the picture above before you continue!  Once you master this concept, everything that follows will be much easier to understand.

So what happens if we copy the formula in the active cell, A1, to another cell?  What will Excel show as the cell reference in the new location?  The answer is in the picture below.

Remember that Excel sees the cell reference as a set of directions.  In our example, Excel interpreted the formula in the active cell, A1, to mean “move right 4 columns, then move down 5 rows”.

It doesn’t matter where you copy the formula from the active cell, A1.  Excel will always remember it as a set of directions to move right 4 columns, then down 5 rows.  So if you copy the formula from A1 to C8, Excel will look right 4 columns, relative to cell C8, then down 5 rows relative to cell C8, and end up at cell G13.

Do you see how it is the same?

    • Cell E6 is 4 rows right, 5 rows down relative to cell A1.
    • Cell G13 is 4 rows right, 5 rows down relative to cell G13.

This is an example of a relative cell reference.  This is because all of Excel’s moves from one cell to another are relative to the active cell.

A relative cell reference changes when you copy or move a formula from one cell to another.  So if the cell reference is different after you copy it, you know you have a relative cell reference.

Another way to tell you have a relative cell reference is the absence of dollar signs in the cell reference.  Excel uses the dollar sign to indicate an absolute cell reference.  Remember what is in the What You Need to Know box above:

    • A1 is a relative cell reference.
    • $A$1 is an absolute cell reference.

Let’s take a look at how you might use relative cell addresses in a real spreadsheet.  Look at the picture below.

How does Excel read the formulas in row 6 (highlighted in yellow)?  Add the following:

  1. The value three rows up relative to row 6 Plus
  2. The value two rows up relative to row 6 Plus
  3. The value one row up relative to row 6.

Do you see how it doesn’t matter what column the formula is in?  Excel will always interpet the formula the same way.  Now take a look at the picture below.

If you’ve understood everything so far, you’re ready to learn about absolute cell references.  You’ll find that, having mastered the concept of relative cell addresses, absolute cell addresses will be easy to learn.

The most important thing to remember about absolute cell references is that they do NOT change when you move or copy a formula from one cell to another.  Recall that relative cell references DO change when you move or copy a formula from one cell to another.

So let’s look at some examples to demonstrate the concept.  Let’s start with the picture we looked at earlier to demonstrate the relative cell reference concept.

Here’s what happens if we change the cell reference in cell A1 from a relative to an absolute cell reference: 

You see in picture above how an absolute cell reference does not change when you copy or move the formula from one cell to another.  To better understand why, let’s see how Excel thinks about it.

With both the absolute and relative cell reference, Excel knows the path from one cell to another – 4 columns right, 5 rows down.  When there is a relative cell reference, Excel thinks “the path starts in the cell to which the formula is copied.”  When there is an absolute cell reference, Excel thinks “the path starts in the cell from which I copy the formula.”

Let’s look at one more example.  First, let’s look again at the picture that shows what happens when a formula with a relative cell reference is copied from one cell to another cell:

Now take a look at what happens when you copy a formula with an absolute cell reference from one cell to another cell.

So check your spreadsheet to make sure you are using the correct cell reference – relative or absolute.  When you use the proper type of cell reference, you will avoid multiple errors in your spreadsheets.

Common Spreadsheet Error #3

You Hardcoded Your Formulas

Whenever you can, use cell references, not numbers, in your formulas.

There are basically two ways to write a formula.

  1. You can type in a value.
  2. You can type in a cell reference.

When you type in a value, this is called hardcoding.

To help you better understand this concept, look at the picture below.

Cells B3, B4, and B5 are hardcoded because the user manually entered a numerical or text value.  Cell B6 is also considered to be hardcoded, even though the formula contains a relative cell reference because the user included a specific numerical value.

What You Need to Know

If you hardcode a formula, the formula will return the same answer if you copy the formula to another cell.

 

Here is an example of how an error can occur when you copy a hardcoded formula from one cell to another:

Here is an example of what happens when you copy a formula that is not hardcoded:

If you have a large spreadsheet with hardcoded formulas, your problems get much worse.  For example, what happens if you want to change your assumptions?

Here is a very simple example:

You can see if you hardcode your formulas, you have to change the formula in all cells that refer to that one assumption.  Can you imagine how much trouble that will be?  How long that will take?

The bigger your spreadsheet, the more cells you have to edit.

  • The more cells you have to edit, the greater the chance you will not edit all cells that need to be changed.
  • The result?  You’ll have two types of errors.
    • First, you’ll have cells with the wrong value because you copied a hardcoded cell.
    • Second, you’ll have cells with the wrong value because you missed these cells and did not even change the value.

Some general rules to follow:

  • Avoid or minimize your use of hardcoded formulas.
  • Be realistic – sometimes your only choice is to use a hardcoded formula.
  • If you must hardcode a formula, use the hardcoded formula only once.
    • Do not copy the hardcoded formula into another cell.  Instead, use a formula with an absolute cell reference to refer to the cell that contain the hardcoded formula.

Perhaps the most common and acceptable use of hardcoded formulas is for the assumptions worksheet in a financial model.  The best practice to follow is to put all of your hardcoded formulas on a separate spreadsheet and label that page as your Assumptions page.  Alternatively, you can put your hardcoded formulas (assumptions) in one section of your worksheet.

Whichever method you use, when you have to change your assumptions, you will only have to do it one time, in one place.  Here is an example of how you can format your spreadsheet using a special section for assumptions.

 

Some final comments:

  • Avoid hardcoding even if you are certain that the value in a cell will never change. Best practice recommends that you still use a formula with a cell reference instead of hardcoding a text or numeric value.
  • Hardcoding only seems to be quicker and easier than using a formula when entering data. If you are only doing a small, one time, simple spreadsheet and don’t have much time to complete it, hardcoding may be a better option.  But always assume this is the exception, not the rule.
  • Hardcoding makes your spreadsheet less flexible. Why make your spreadsheet less flexible, when one of Excel’s best features is its versatility, which enables you to build a spreadsheet that meets your needs
  • With large, complex spreadsheets, hardcoding will always be less efficient and more time consuming whenever you change assumptions. In addition, there is a greater risk that you will not change all of the cells that are affected.

Common Spreadsheet Error #4

You Used Copy/Paste

Capture every cell you need to copy.  Learn to use Paste/Special.

Copy and paste is one of Excel’s best and most used features.  It’s an incredible time-saver, allowing you to replicate text, numbers, and formulas to multiple cells with one click of the mouse.  Thanks to copy and paste, Excel enables you to be faster, more efficient, and productive in creating spreadsheets.

However, if you don’t use copy and paste correctly, you will quickly fill your spreadsheet with all kinds of errors.  Each mistake will have to be located and corrected; with the result, you will be slower, less efficient, and less productive in creating your spreadsheets.

We have already seen how copy and paste can lead to errors if you don’t understand absolute and relative cell addresses.  Incorrect use of copy and paste can also cause you to place the wrong text and numbers in cells.  Here’s the rule of thumb for copy and paste:

Rule #1:  Use a cell reference instead of copy and paste.

Using a cell reference instead of copy and paste can significantly reduce the chance of error in your spreadsheet.  Why?  First, you have a much simpler formula in the cell with the cell reference.  Second, you don’t have to worry if the original formula is properly set with absolute or relative cell addresses.  This is because you are pointing to a cell, not copying a formula.  The picture below illustrates how this works.

 

See how much easier it is to use a cell reference instead of copy and paste?  Using a cell reference means you only have to look at the referenced cell (B7).  By contrast, with copy and paste you have to confirm that the correct cells are being added, and that the cell references are absolute or relative.  Imagine if your formula involved more than just adding two numbers!

 

Rule #2:  Select all cells before you copy, paste to the correct cell.

The picture below shows what happens when you don’t select all of the cells you need to copy.  When you paste the incomplete selection to another section of your worksheet, you do not transfer all of the data you need.  Without the required data in the proper space, any calculations based on this data will be wrong.

The picture below shows what happens when you paste your data to the wrong cells in the spreadsheet.

Rule #3:  Do not merge cells.  Instead, use Center Across Selection.

Another common copy and paste problem occurs when you work with merged cells.  Excel won’t let you copy from an unmerged cell to a merged cell.  If you try to do so, Excel displays a message box that looks like this:

Correcting this error is a tedious process.  You have to find the cells that are merged, then unmerge them.  Can you imagine what a terrible chore this would be if you had to correct an entire column, and not just one cell?

Using Center Across Selection gives the same result as Merge Across, but is much more flexible.  It is also  better for preserving the structure of your worksheet.

Center Across Selection is found on Format Cells dialog box, which you call up from the Format section of the Home Ribbon.   First, select Format from the Cells section of the Home ribbon.

You will now see the Format Cells dialog box.  Choose the Alignment tab.  Under Text Alignment, click the arrow under the Horizontal option.  Choose Center Across Selection from the dropdown list.

Rule #4:  Use Paste Special

Sometimes your copy and paste error occurs because you copy everything in a cell.  Even though a cell appears to have only a number, text or formula, in fact there are many elements present in a cell:  formulas, values, formats, comments, data validation, and borders.

At times you may only want to copy one element from one cell to another cell.  Here are some examples:

  • Your cell contains a formula, but you only want to copy the results of the formula.
  • Your cell has bold type and yellow shading and you only want to copy the format.
  • Your cell has comments and you want to copy the comments, not the contents.
  • Your cell contains a formula and you want to copy the formula, not the format.
  • You have a row of data and you want to copy it elsewhere as a column.

Excel has a nifty feature that enables you to do all of these things and more.  It is called Paste Special.  You find this option on the Paste option in the Clipboard section of the Home ribbon.  

Select Paste Special

The Paste Special dialog box appears with all options available to you:

Here’s a list describing the most commonly used Paste Special options.  I’d encourage you to experiment with the others as well, so you’ll be able to use it should the need arise.

  • Formulas– Pastes only the formulas.
    • Use this when your target cells are already correctly formatted.
  • Values – Pastes only the values from the copied cells. If your original cell has a formula, the target cell will have a value.
    • Use this when you want your numbers to stay the same, even if you change the original formula.
  • Formats – Pastes only the formats from the source cell to the target cell.
    • This is the best option if you want one range of cells to look the same as another range of cells you have formatted. It is much easier and quicker to use Paste Special | Formats than to highlight each cell in the new range to manually change the formats.
  • Operation – This allows you to apply an arithmetic operation to the copied cells, using the target cell.
      • This is most useful when you want to do a calculation using data in two columns of numbers. This is an alternative to using a formula.
      • A formula requires you to have a third column containing the formulas.
      • Paste Special | Operation enables you to have only two columns, each of which only has values.
  • Transpose – Flips the data, from a row to column, or from column to row.
    • You can use Transpose by itself or in combination with other Paste Special options.

    In summary, copy and paste is one of Excel’s best features.  Just be sure you use it properly to avoid errors in your spreadsheet.

    Common Spreadsheet Error #5

    Your Formulas Have Errors

    Try to avoid complex formulas.  Choose the correct function.

    If you make a mistake in your formula, Excel lets you know right away.  Most people are familiar with Excel’s error messages that pop up when you don’t enter the formula correctly.  These errors, typically the result of incorrect syntax, are very common.

    However, there are other common formula mistakes that people make all the time without realizing it.  This includes not following the Order of Precedence, writing formulas that are needlessly complex, and choosing the wrong function.

    Syntax Errors

     #DIV/0! – You are trying to divide by zero.  Dividing by zero is mathematically impossible, so Excel displays an error message.

     #N/A – You told Excel to look for data but either the data does not exist, or you told Excel to look for the data in the wrong place.  Take Note:  Unlike other syntax errors, with the #N/A error, your formula is probably written correctly.

     #NAME? – There are several reasons this can occur:

    • You misspelled something in your formula, typically the function name or a named range if you have used one.
    • You forgot to use double quotes when using text in the formula.
    • You forgot to include the range operator – you put A1C1 instead of A1:C1.

    #NUM! – Your formula produces a result – positive or negative – that is too large for Excel to show. 

     #REF! – Your formula points to a cell that does not exist.  This typically occurs when you write a formula that refers to a cell in a row or column, then delete that row or column.

     #VALUE! – Your text or numerical values are incorrect.  You may have entered or pointed to text values in a formula that only accepts numerical values.

     

    What You Need to Know

    Most Excel functions have both required and optional arguments.

    All optional arguments have a default value.

    NEVER assume the default value is the correct choice for your situation.

    ALWAYS explicitly specify the optional argument to avoid errors.

     

    Order of Precedence

    Excel follows a very specific set of rules when it calculates your formula.  Excel does not just start at the beginning and go from left to right, calculating as it goes.  Instead, Excel looks for certain symbols to determine the order in which Excel performs its calculations.

    This is the Order of Precedence:

     

    You can manipulate the Order of Precedence by using parentheses in your formulas.  The picture below has examples of formula calculations controlled by the Order of Precedence vs. being controlled by the use of parentheses.

    The first row has the original formula.  The subsequent rows evaluate the formula components in order, guided by either the Order of Precedence or the use of parentheses.  The items being evaluated in each step are in red type. 

    Notice how you get a completely different answer if you do not use parentheses. 

    So, to ensure you always get the right answer, you must either:

    1. Be certain that you understand the Order of Precedence OR
    2. Use parentheses to ensure that your formula calculates the way you want.

    Complex Formulas

     All too often people write formulas that are too long and complex.  Such formulas are hard to understand and even harder to troubleshoot if you get a wrong answer or an error message.

    What You Need to Know

    Simple.  Simple.  Simple.

    Your formula should not be so complex that the user cannot understand it at first glance.

    Resist the urge to put multiple calculations into one formula.

    Break up your formula into small parts into separate columns.

     

    Keep in mind that other people may need to look at your spreadsheet.  You want to make it as easy as possible for other users to understand what you did.  That is why using small formulas with helper columns is better than having one long, complex formula.

     What is a helper column?  It is a column that you use in a spreadsheet to store intermediate steps in your calculation.  Helper columns, with short formulas, help the user understand the thought process behind your calculations.

    The picture below shows a table that uses a needlessly complex formula in one cell.  Any user who tries to understand or trouble shoot this file will get frustrated very quickly.  It is hard to glance at this formula and understand what each part is doing, why it is included, and what is being referenced.

    By contrast, take a look at Picture 27 on the next page.  In this table, the user added several helper columns:

    • Column D – Total Sales
    • Column E – Sales Days per Month
    • Column H – Average Daily Sales
    • Column I – Sales Days per Year

      With the inclusion of the helper columns, it is easy to understand the process by which the final answer – annual sales – is calculated.  The formula in the last column is much shorter and easier to understand.  Each helper column has a short formula.  The only long formula, in E5, is easier to grasp because it is a standalone IF function, and not nested in a larger formula.

      Choose the Correct Function

      You must be careful when typing the function name in Excel.  There are many functions that have similar names but perform very different functions.  Here are some examples:

       

      • AVERAGE
        • Returns the average of a list of numbers.
      • AVERAGEA
        • Returns the average of a list of numbers.  Counts cells with the logical values of TRUE as the number 1 and cells with the logical value of FALSE as zero.
      • AVERAGEIF
        • Calculates the average of a range of cells that meet a specific criterion.
      • AVERAGEIFS
        • Calculates the average of a range of cells that meet multiple criteria.

       

      • COUNT
        • Returns the number of numerical values in a range of cells.
      • COUNTA
        • Returns the number of non-blank cells in a range of cells.
      • COUNTBLANK
        • Returns the number of blank cells in a range of cells.
      • COUNTIF
        • Counts the number of cells in a range that meet a specific criterion.
      • COUNTIFS
        • Counts the number of cells in a range that meet multiple criteria.

      Sometimes your copy and paste error occurs because you copy everything in a cell.  Even though a cell appears to have only a number, text or formula, in fact there are many elements present in a cell:  formulas, values, formats, comments, data validation, and borders.

      At times you may only want to copy one element from one cell to another cell.  Here are some examples:

      • Your cell contains a formula, but you only want to copy the results of the formula.
      • Your cell has bold type and yellow shading and you only want to copy the format.
      • Your cell has comments and you want to copy the comments, not the contents.
      • Your cell contains a formula and you want to copy the formula, not the format.
      • You have a row of data and you want to copy it elsewhere as a column.

      Excel has a nifty feature that enables you to do all of these things and more.  It is called Paste Special.  You find this option on the Paste option in the Clipboard section of the Home ribbon.  

      Select Paste Special

      The Paste Special dialog box appears with all options available to you:

      Here’s a list describing the most commonly used Paste Special options.  I’d encourage you to experiment with the others as well, so you’ll be able to use it should the need arise.

      • Formulas– Pastes only the formulas.
        • Use this when your target cells are already correctly formatted.
      • Values – Pastes only the values from the copied cells. If your original cell has a formula, the target cell will have a value.
        • Use this when you want your numbers to stay the same, even if you change the original formula.
      • Formats – Pastes only the formats from the source cell to the target cell.
        • This is the best option if you want one range of cells to look the same as another range of cells you have formatted. It is much easier and quicker to use Paste Special | Formats than to highlight each cell in the new range to manually change the formats.
      • Operation – This allows you to apply an arithmetic operation to the copied cells, using the target cell.
          • This is most useful when you want to do a calculation using data in two columns of numbers. This is an alternative to using a formula.
          • A formula requires you to have a third column containing the formulas.
          • Paste Special | Operation enables you to have only two columns, each of which only has values.
      • Transpose – Flips the data, from a row to column, or from column to row.
        • You can use Transpose by itself or in combination with other Paste Special options.

        In summary, copy and paste is one of Excel’s best features.  Just be sure you use it properly to avoid errors in your spreadsheet.

        Conclusion

        We’ve covered a lot of reasons why you can have errors in your Excel spreadsheet.  Remember the basics:

        1. Normalize your data.
        2. Remember the difference between absolute and relative cell references.
        3. Avoid hardcoding in your formulas.
        4. Be careful with paste and copy. Consider using cell references or Paste Special.
        5. Use the correct formula, specify all arguments, use helper columns and short formulas.

        However, we’ve only scratched the surface of what could go wrong.  Excel is a wonderful, complex and flexible tool.  Yet he same features that make it a valuable resource also can cause many of the problems.

        So if you run into a problem you can’t understand or solve, don’t hesitate to contact me so we can discuss your issue and determine the best solution.

        I enjoy working with Excel and helping people solve their problems. I hope this has been helpful to you.  Please contact me with questions or suggestions.  If you think this article will be useful to someone you know, I encourage you to pass it along.  Thank you.

         

         

        Bruce Kaufmann

        bruce@bkaufmann.com

        281.734.9823

        www.bkaufmann.com