This is Chapter 3 of my Ebook "5 Reasons Why Your Spreadsheet is Misbehaving". Click here to go to a page on my website where you can download the Ebook at no charge.
Use cell references, not numbers in your formulas.
There are basically two ways to enter data into a cell on a worksheet. One way is to enter the data manually. In other words, you type a number or text into a cell. The second way is to use a formula containing (relative or absolute) cell references to pull data from another cell.
Whenever you type a number or text into a cell, this is called hardcoding. Hardcoding also occurs when you create a formula that has text or a number instead of a cell reference. Refer to the picture below.
Examples of Hardcoded cells
In the picture above, 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
When you hardcode a cell the result will not change when you copy or move data to another cell.
This is true even if you change the values in the cells referred to by the hardcoded cell.
If your cell is partially hardcoded, your formula's calculation will be wrong when you copy or move data to another cell
Let’s look at some examples!
Here’s what happens when you copy a hardcoded cell.
An error results when the value of a hardcoded cell is copied
Here’s what happens when you copy a cell that is not hardcoded.
No error results when the value of a non- hardcoded cell is copied
The problem gets much worse if you have a large spreadsheet with many formulas. What happens if you want to change your assumptions?
If you hardcode your cells, you will have to change all the cells that contain the same value or formula. Can you imagine how much time that would take?
Here’s a simple example.
Example of how all cells with formulas will have to be changed when you change your assumptions.
The bigger your spreadsheet, the more cells you have to edit. With more cells to edit, it is more likely it is that you won’t modify 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.
Best Practice: Enter data 1 time in 1 cell.
Related cells use formulas to point to the input cell.
It’s always better to put your assumptions in one place in your spreadsheet and to label that section as your assumptions. This way, you know that it’s an assumption. More importantly, you will only have to change your assumption once, and in only one place, whenever you want to use a different variable.
The picture below shows a better way to format your spreadsheet using a special section for assumptions.
How to use formulas with cell references and a special Assumptions section
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 customized 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.