Introduction
When entering formulas in Excel you have the option to type values directly into the calculation (hardcoding) or reference other cells which have values in them (cell referencing).
Understanding the differences between these methods can significantly impact the flexibility, accuracy, and maintenance of your Excel spreadsheets.
Download the File
If you would like to follow along download the attachment below.
Hardcoding in Excel Formulas
Hardcoding is when you directly type a fixed value into an Excel formula. This value does not change unless you manually go into the formula and change it yourself.
An example of this would be if you wanted to multiply the number of products sold by their price.
If 10 products were sold and they cost 15 each we could enter the following formula:
Here, both 10 and 15 are hardcoded into the formula.
For simple formulas, one off calculations or if you need to calculate something quickly hardcoding will be fine, but as you start building more complex spreadsheets and calculations you will run into some issues when hardcoding:
Lack of Flexibility: If any value changes, you must manually update each formula where that value appears, increasing the risk of errors.
Poor Scalability: Hardcoding becomes cumbersome and error-prone in larger spreadsheets with numerous calculations.
Maintenance Issues: Over time, keeping track of where and how values are used can become challenging.
Readability Issues: Anyone looking at your formulas will need to click on the formula to see what values have been entered into it, rather than having the components of the formulas clearly visible to them in other cells.
Cell Referencing in Excel Formulas
An alternative to hardcoding is to use cell referencing. This involves using values entered in other cells and referencing those cells in your formulas.
This approach dynamically links the formula to the values in the referenced cells, which can change without needing to update the formula itself.
For the same calculation as above, using cell references might look like this:
This way of writing formulas has many benefits over hardcoding, such as
Flexibility: Changing the value in the referenced cells automatically updates all formulas that use those cells reference, ensuring consistency across your spreadsheet.
Scalability: You can easily manage and update complex calculations, as changing a single value will impact all related formulas.
Ease of Maintenance: Centralize data entry and updates, simplifying the process of managing large and complex spreadsheets.
Better Readability: Can make it easier for user to understand what has gone into a calculation without having to view the formula that has been entered.
Practical Examples
If we have products that we are going to sell and want to know how much the amount will be after tax of 20% has been added we can do this using both hardcoding and cell referencing to achieve the same result, but the cell referencing will offer greater flexibility and readability to anyone who uses this spreadsheet.
Hardcoded Formula:
To calculate the amount including tax using the hardcoding method we would reference the cell where the product amount is and then multiply it by the hardcoded tax amount.
If tax was 20% then for the printer the formula would be =B2*1.2, and we could then apply the same formula to the other products.
While this gives us the correct amount including tax, each product is multiplied by an individual tax rate. This can cause some issues if we want to change the tax rate or share these workings with someone else.
If the tax rate changed to 30% each individual formula would need to be edited manually e.g. from =B2*1.2 to =B2*1.3
As this would need to be done manually to all products there is a risk that some products may not have their tax rates updated leading to misleading and inconsistent calculations
Anyone reviewing this dataset would need to click on the cells in column C to know what tax amount they have been multiplied by
Cell Referenced Formula:
When using the cell reference method we can add the tax amount to another cell, which in this example is located in cell B1.
To calculate the tax amount for the printer we could now enter the following formula:
=B4*(1+B1)
This references the tax amount in cell B1, removing the need to type 1.2 in our formula, which is what was done in the hardcoding example.
To be able to drag this formula down across our remaining products we can make the tax amount in cell B1 an absolute reference by clicking on B1 in the formula bar and pressing the F4 key (if using a laptop or small keyboard you may need to press the Fn key as well as F4), this will put dollars around the cell reference locking it so that as we move our formula down we are always referring to the tax amount in B1.
(If you have not used absolute references before click here to learn more)
Now we have our values in column C referencing the tax amount in cell B1, we can change the value in B1 and all the calculations in column C will update ensuring consistency and accuracy across all products.
It is also clear to anyone viewing this dataset what the tax amount is.
Conclusion
The ability to dynamically change values or criteria in your formulas by typing them in a cell that the formula references means you don’t have to spend time locating and updating formulas manually.
Referencing cells means you can link many formulas to one reference, and when you change the reference all of your formulas will update.
For larger spreadsheets and more complicated formulas, whenever possible you should avoid hardcoding and have values entered in separate cells to drive your calculations.
For simple calculations in small spreadsheets hardcoding can be a quick way of getting a formula completed.
However, if you want to build more dynamic and advanced spreadsheets it is best to get in the habit of always having criteria ranges reference other cells whenever possible to add more flexibility into your formulas and allow your spreadsheets and calculations to scale up as your spreadsheet grows.
Comments