When creating Microsoft Excel formulas, you can generate calculations using specific values—also known as hard-coding—or referencing other cells in the workbook. In this guide, I’ll explain why hard-coding values can cause issues, and how using cell references is often the better option.
Issues Caused by Hard-Coding Values in Excel Formulas
Let’s say you’re working out the cost of products in your store when a 20% tax is added. To do this, in column C, you have multiplied the values in column B by 1.2, as seen in the formula bar at the top of the Excel window:
=B2*1.2
However, now imagine that, the following year, the tax on all items is reduced to 15%. As a result, you would have to go back into the cells and change all the formulas in column C so that the values in column B are multiplied by 1.15.
Yes, you could change the formula in cell C2, and automatically duplicate it in the remaining cells by double-clicking or clicking and dragging the fill handle in the bottom right corner of the cell. However, this approach takes more time than necessary and could easily result in copying or autofilling mistakes.
Related
How to Fill Excel Cells Automatically with Flash Fill and Auto Fill
Microsoft Excel can automatically fill cells based on patterns.
What’s more, hard-coding values in Excel formulas makes spotting errors more difficult, as you have to select a cell to see the make-up of a calculation in the formula bar. Alternatively, you could press Ctrl+` (grave accent) to view all formulas in the spreadsheet, but—again—this is an unnecessary step to take and could add confusion.
Finally, if you share your Excel workbook with others, hard-coded values in formulas lack any sort of context that explains how you came to that figure.
Why Referencing Variable Cells Is a Better Option
To overcome the drawbacks of hard-coding values in Microsoft Excel formulas, you could use cell references.
Using the example above, rather than multiplying each value in column B by 1.2 within your formula, you can reference a cell that contains the variable—in this case, cell E2—instead:
=B2*$E$2
It’s important to note that the reference to cell E2 is absolute, represented by the dollar symbols ($) before the column and row. If you don’t do this, when you duplicate the formula to the remaining cells in column C, the cell reference will adjust relative to the position of the active cell. For example, the formula in cell C3 would reference cell E3, the formula in cell C4 would reference cell E4, and so on.
When crafting your formula, press F4 after typing the cell reference to turn it from a relative reference into an absolute reference.
Related
How to Use Relative, Absolute, and Mixed References in Excel
Save time and reference the correct cells when creating formulas in Excel.
Now, when the tax on the items changes to 15%, you can simply change the value in cell E2 to 1.15, and all formulas that reference that cell will update instantly.
What’s more, since the cell containing the variable is always on show, it’s easy to review for accuracy. Also, because cell E1 contains the word “Tax,” anyone accessing the spreadsheet for the first time can see the full context of how the total values in column C are calculated.
Disclaimer: There are cases when hard-coding values in formulas can be more appropriate. For example, you might hard-code a weekly total to be divided by seven to generate a daily total, since the number of days in a week never changes. Also, formulas with cell references use slightly more memory than those with hard-coded values, so if you do this often, you may see a decline—albeit negligible—in your workbook’s performance.
Using cell references in formulas instead of hard-coded values is particularly useful if the calculation parameters are likely to change in the future. In this example, the IFS function is used in column C to determine whether each student passed or failed the end-of-semester exam, depending on whether they reached the pass mark variable in cell E2:
=IFS([@Score]=$E$2,"PASS")
Related
How to Use the IFS Function in Microsoft Excel
Save time and eliminate the risk of errors using IFS instead of nested formulas.
For the following semester, you could simply duplicate the sheet and change the pass mark variable, all without having to worry about adjusting the formula to generate the correct outcome for that data.
Pro Tip: Name the Variable
So far, I’ve talked about referencing cells in Excel formulas instead of hard-coding values. One way to secure your formulas even further is to give the cell that contains the variable a name, which is especially useful if you plan to use it throughout your workbook.
Let’s say you have multiple worksheets in a workbook, each containing various products from different stores, and you need to display the total cost of each item, including tax.
You’ll notice in the screenshot above that there’s also a worksheet called “Tax.” This sheet contains the current tax rate and the value that costs need to be multiplied by to calculate the overall cost.
Rather than having to remember the location of the variable within your workbook each time you want to calculate the overall cost of a product, you can give this cell a name. To do this, select the relevant cell, and in the name box in the top-left corner of your screen, type a memorable cell label. In my case, I’ve gone with “TAX.” Then, press Enter to complete the naming process.
Now, regardless of where you are in your workbook, you can easily reference this variable in your formula:
=B2*TAX
Whenever the tax rate changes, you only have to adjust it once in your Tax worksheet to update all your costs. What’s more, since references to named ranges are absolute in nature, you don’t have to remember to add dollar signs to your formulas!
Referencing cells instead of hard-coding values in formulas isn’t the only way to save time in Microsoft Excel. For example, you could add your most-used commands to the Quick Access Toolbar, or learn some of Excel’s most useful shortcuts to save you from jumping between your keyboard and mouse.
