# Did You Hardcode Your Formulas?

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