Think of hard coding as the opposite of cell referencing. While I’d usually suggest you choose cell referencing over hard coding, in cases like when dealing with volatile functions, you’re better off hard coding formulas into static values.
In other cases, the format you’re following could limit you from inserting too many rows or columns. In such a case, you might have to resort to hard-coded values rather than create a new alignment to pass as a reference.
While hard coding an entire value is pretty straightforward in the datasheet, you will have to keep certain syntaxes in mind when hardcoding inside a formula.
Hard Code Inside a Formula
Most formulas take in references as an argument. However, you could also insert hard-coded values instead of references inside the formula.
I feel like it would be appropriate to hard code certain values like the criteria inside a formula inside referencing cells. This is because these types of data are mostly fixed and any changes to the referenced cell can ruin the entire calculation.
Let’s hardcode the criteria for the COUNTIF function as an example. To recapitulate, here’s how the COUNTIF function is written in a formula:
=COUNTIF(range, criteria)We will be using the COUNTIF function to count the number of students who want Apples. On an empty cell, we entered the following formula using the COUNTIF function:
=COUNTIF(B2:B11,“Apple”)
In this formula, we hardcoded our criterion, Apple. If you see I put Apple inside double quotes. If I didn’t, Excel would recognize it as a reference rather than the value itself and return me the #NAME? error.
Hardcode Formula
I usually hardcode volatile functions such as RAND and RANDBETWEEN in the spreadsheet. Such functions refresh every time you enter a new formula and return a new value and significantly slowing your sheet down in the process.
Hardcoding a formula is pretty easy and involves changing the cell that contains a formula to static values. Here is what you can do to hardcode your formula in Excel:
- Select your data range.
- On your keyboard, use the shortcut Ctrl + C to perform the copy command.
- Then, right-click on the same location and enter V.
This will change your data from being a result of a formula to a static, hard-coded value.
Use Name Manager to Insert a Hard Code Value
You can assign a value to a name using the Name Manager, essentially hard coding the value in the name.
I suggest you use this method because if in case you do have to change the value, you can simply change the value of the name and save yourself from the hassle of manual calculation.
- Head to the Formulas tab.
- Select Name Manager from the Defined Name section.
- Click on the New button.
- Set a new Name.
- Select the fly-out next to Scope and choose an option.
- Next to Refers to, enter a value.
- Click OK.
Disadvantages of Hard Coding Excel
As Excel is mostly used for task automation, I doubt if it comes to you as a surprise that hard coding is not suggested in Excel. Hard coding data means that the data becomes inflexible. As opposed to data referencing, you cannot change a value and automatically change the value in all calculations.
Therefore, only hard code values that you know for certain will stay static. Such values can be tax percentages. For other changing values such as discount percentages, we suggest you stick to cell referencing so you don’t have to manually change values for each calculation when there’s a switch in data.