Excel Formulas Not Working Part I: Understanding Relative, Mixed and Absolute Referencing

by Brad Edgar | Aug 9, 2014 | EXCEL BEGINNER, EXCEL INTERMEDIATE, EXCEL QUESTION, LEARN EXCEL FORMULAS

I know what you’re thinking.  The title of this post is a sad attempt at stringing together an English phrase that actually makes sense (i.e. Excel Formulas Not Working).  To that I say, you’re absolutely right!  Nevertheless, it seems that the majority of people out there are searching for this exact statement on Google.  For that reason, I am going to leave it and I’m also going to try to put together a comprehensive series of blog posts that can answer this question – or this statement rather. We’ve all come across the dreaded situation.  We put together a massive spreadsheet and start looking at the results of our calculations and formulas only to realize that the results are incorrect.  Now you’re thinking, well what is the problem?  In this first of several posts we are going to look at what Tech Republic says is the most important thing to teach your Excel users and that is absolute and relative cell referencing.  This post will give you a solid idea of why your formulas may be failing or may just be not giving you the results you expected and at the same time cover the basics of absolute and relative cell referencing so you can understand what the heck you’re doing.

Relative, Mixed and Absolute Cell Referencing: The Reason Why Your Excel Formula Didn’t Auto Fill or Copy As You Expected

I know sounds brutally boring, right? It kind of is really; but if you understand how this works, you’ll save yourself a lot of time trying to figure out why your formulas aren’t producing the results you were expecting.  This is one of the first things you want to learn about Excel before you start using formulas as it will save you tons of time in the future.  Alright let’s get into the less than exciting details and then look at the fun stuff.

What Exactly Does Cell Referencing Even Mean?

Literally and in the most laymen terms cell referencing means to reference a cell from your current cell. This allows you to make the contents of a cell dynamic meaning if someone updates cell A1 and you are referring to that cell in cell C5, cell C5 will update to reflect the changes made to A1.  Let’s look at an example and check out the step by step process.

Alright, now that we’ve covered cell referencing in general, let’s look at the different types.  We’ve got relative, mixed and absolute cell referencing.

Relative Cell Referencing

By default your formulas use relative cell referencing (it’s important that you understand that this is the default setup in Excel and that there will be times where you will need to make your formula not relative).  What this means is that if you copy your formulas to other cells the references will simply be offset exactly by the same number of rows and columns that you moved your formula (Microsoft did a great job at explaining this here).

Mixed Cell Referencing

When we are talking about mixed cell referencing, here we are referring to both relative and absolute referencing in one formula/cell.  This means that a part of the formula will always be referencing a specific range or a cell when copied or moved (absolute referencing) and part of the formula will be referencing a dynamic or a changing range or cell (relative referencing).  As an example, as you copy and paste your formula,  you may want to always refer to column A but at the same time want to make sure that you change rows as you paste your formula to new cells.  In order to do that, you are going to place a dollar sign in front of the column character that you are dealing with – $A1.  Let’s have a look.

 Absolute Cell Referencing

Absolute cell referencing is the complete opposite of relative cell referencing.  When we develop a formula and add the $ symbol before both the column and row reference, the formula is said to be absolute.  This means that no matter what, when we copy the formula from one cell to the next, we are always going to reference the exact same cells and ranges that were initially outlined in the originating cell.  For example cell F3=$D$3*$E$3 – if we copy this to cell G4, G4 will equal the exact same thing (=$D$3*$E$3).  This ultimately means that our result will be the exact same for F3 and G4.  A good example of when to use this is if we have a  storage fee per cubic feet in a cell and we want to calculate the storage fees based on the total cubic feet used of each item (I’m just sticking with inventory theme here to keep things consistent).  Let’s take a look at this example.

Practical Examples: Situations and Solutions to Excel Formulas Not Working as a Result of Cell Referencing

To really try and drive this concept home, we are going to go over a few different examples so that we can see what happens when we don’t apply the proper referencing syntax.  This will hopefully teach you enough so that you no longer have to be worried about your formulas not working as a result of cell referencing.

Don’t be Lazy – When Not to Use Relative Referencing or When to Pick up the Slack and Start Using those $ $igns

There comes a time as an Excel user where you need to step away from just using the standard Excel default cell referencing.  When the time comes you’ll need to make sure to use those dollar signs.  As you can see in our example below, we forgot to make the reference to cell M2 absolute.  As a result, because we copied our cell one row down (the new cell column remained the same as the previous), we moved our reference one row down as well to M3.

The Solution:  Before you copy the contents of H3 over to new cells, be sure to make the storage fee an absolute reference.  In this case our formula in H3 should then be as follows (H3=G3*$M$2).  You should know this by now but if you are still asking why does G3 not have any dollar signs, it’s because we know that as we move to H4, we also want to move the reference to G4 (as the row changes in the cell that contains the formula, we want the row to change on the reference for the cubic feet column/field).  Technically, we should make G3 have an absolute column of $G but because we are not copying the formula to another column we can get away with this.

You’re Not Making Any Money With Those Dollar Signs – When Not to Use Absolute Referencing

Alright so it’s one thing to forget to use the absolute reference (using dollar signs) but it’s also important to know when not to use them.  In this scenario we are going to look at when and why it’s important not to make your cell references absolute.  In the image below, you’ll notice that when we make our formula in F4 absolute, and we drag that formula to F11, every cell from F5 to F11 calculates $D$3*$E$3 instead of changing rows to multiply the Qty On Hand by the Unit Price for each record.

The Solution:  Again, it’s important to look at what you are looking for in terms of a result and this will determine what you need to do for the referencing.  In our case, we’ve made both of the cell references in F3 absolute (again the dollar sign before a row/column reference makes it absolute) and we’ve copied that formula by double clicking the small box in the bottom right hand corner of the cell (TIP: this will fill your current column down as far as data exists to the right or left columns).  Because both the column and row references were absolute, our formula remained the exact same multiplying D3 by E3 for each of the cells.  To fix this, we need to remove the absolute referencing by selecting each individual reference in F3 and hitting F4 until no dollar signs exist in our equation.  This will of course make our formula relative (REMINDER: relative referencing is the default) which will allow us to copy this formula down and ensure that we multiply QTY ON HAND by UNIT PRICE for each row/record in our data set.  It’s important to note that we don’t need to make the column reference absolute here because we are not copying the formula into a cell that is outside our current column.  This is because relative to our original cell, we have not moved over to the left or right a column – the next part of this section should explain this in detail with a screen shot.

We Can’t Always Operate on a Hybrid – When You Need to and When You Don’t Need to Use Mixed Referencing

In this example I’m going to show you a situation where you need to use mixed referencing to ensure you get the result that you are looking for – this is the same example as above but bare with me.  This is kind of an uncommon situation but let’s pretend like we had two columns for Total On Hand Value (OHV2 being the second column).  In this column, we want to ensure that we have the same result as we have in F4 which is by the way (a relative reference to D4*E4).  If we were to copy the relative formula from F3 (D3*E3) to G4, the formula in G4 would be E4*F4 which would not give us the total on hand value as we expected (this is because we moved 1 row down and 1 column right – as opposed to just 1 row down which was the case in cell F4).  Let’s look at how this works:

The Solution:  To make sure we get this right (so G4 is the result of D4 multiplied by E4), we need to use a mixed reference in F3 so that we make our columns absolute (rows will remain relative) which will then allow us to copy the formula to G4 and get the result we are looking for (G4=$D4*$E4).  Below provides the example of the correct answer/result.

Make sure you have a look at the table below our data set.  This outlines each reference method iteration that we can have in F3 and how it will effect the result if it is copied to G4.  The table also explains how the formula works (formula instruction column) for each iteration.  You may want to keep this screen shot as a quick reference to how cell referencing works.

Summary and Closing Thoughts on Excel Formulas Not Working and Cell Referencing

I think that about covers all of the bases to make sure that when you are using formulas that you are also using the correct referencing method.  The biggest thing to keep in mind is how you would like your formula to be copied over and what cells you’ll want your formula to reference in the cell that you are pasting to.  If you understand and apply these concepts, you’ll be sure to rule out your Excel formula not working as a result of incorrect cell referencing.

If you enjoyed this post and found it useful, please do share with your peeps!  Also, I don’t often get many comments but I’d love to hear your thoughts on what other problems you experience when it comes to formulas not working so PLEASE DO LEAVE A COMMENT!!!

I’m looking forward to adding other articles to this Excel Formula Not Working series and I hope you are too.

Until the next time!