Hi Excellers, it is time for some more formula fun on #formulafriday. Today I want to share with you my Top 3 Reasons Not To Use Hard Coding In Excel Formulas

If you want see see all of the blog posts in the #formulafriday series then you can click on this page right here.

If you want MORE Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.

So….What Is Hard Coding?

For those who do not know what hard coding is, it’s explanation is quite straightforward. It simply means using specific figures or amounts in an Excel formula rather using referenced or calculated values. The easiest example to demonstrate hard coding is using a simple Sales Tax calculation.

I have detailed an example below. If the Sales Tax amount is actually calculated within the formula it is hard coded. You can see below in the worksheet where Sales Tax is is part of every one of the month’s formulas. To update the amount of Sales Tax payable would be updating every formula. How inconvenient.

So, here are my top 3 reasons to NOT use hard coding in Excel.

Reason 1. You Can Easily Edit Your Formulas.

Let’s say that you need to change the Sales Tax applicable to the Sales for each month as in the above example. As the Sales Tax value is hard coded into each of the twelve formulas, we would need to edit each and every one of the formulas. If we either used a named range or reference the Sales Tax value in a specific cell, we can update the named range or the specific cell that contains the Sales Tax value which would then update all of our formulas at once.

A bit more detail about these two methods is below.

• Create a Named Range. For a quick example of how to use a named range in then select a cell in your Excel worksheet (in this example I have chosen A1, and typed the Sales Tax value in it), click into the Name Box to the right of the Formula Bar and type the name you want to refer to the range by. (I have named it SATAX). We can now amend the formula to calculate =C3*SATAX. If we need to change the Sales Tax formula we just need to amend the value once in cell A1. All the formulas will automatically update.
• Reference the Sales Tax Value cell. If you do not want to go as far and creating a named range or value as in the above steps, then you could just reference cell A1 in your formula which point to the Sales Tax value. Again if you want to change the sales Tax value all you need to is change the value in cell A1 and all of the twelve formulas will update. =C3*$A$1

Reason 2. Your Formulas Will Make More Sense.

Names make your formulas more understandable. If you take a look at the Sales Tax formula, it is obvious that we are calculating the Sales Tax. We do not even need to know where SATAX is stored, as we can navigate to the cell or cells of the named range really easily by selecting it from the drop down Name Box next to the Formula Bar. This is useful if we need to see more details about the formula, or need to update the value of Sales Tax.

Reason 3. Fast Simple Formula Creation.

Once your ranges are named or defined then you can use them over and over again in formulas. If you have multiple areas you need to calculate Sales Tax, then you can quickly build the formulas by using the names range SATAX or referencing the cell A1 in your formulas. This not only allows super fast formula creation it also helps with the logic of formula creation.

Do you hard code or use named ranges?