Do you have an idea about what is hard code or how to hard code in Excel?
It simply refers to a notion of forming a certain value into a formula or cell other than referencing the cell with the value. If your data has some hard code values, they will not get changed when you modify other values.
Well, you can change the hard code values manually as it keeps the data reliable. Let’s see how difficult it is to change the hard code values:
Manually Enter Value into Formulas
The values will be added directly in the formula to hard code in this approach. Suppose your dataset has sales quantity, unit price, and sales by date. Now, you need to find the net sale of a specific fruit with the SUMIF function.
Let’s add the fruit names manually rather than referencing them to the cells with the names of fruits in them.
- First, select cell C14 and add the following formula:
=SUMIF(C5:C12,”Apple”,#5:E12)
- Press ENTER key.
- You will get the total number of apples sold in a certain time.
- If you replace the apple with another fruit, you will get zero value. In the case of a cell reference, the formula gives the quantity sold for that fruit instead of zero value.
How to Hard Code in Excel with Define Name Command?
In our next method, you will get to know how the Define Name command helps in hard code a value. Let’s add a name for a value with the Define Name command and you will notice it does not change by default because it is hardcoded.
In this method, we will use a dataset in which you can see fruit sales for the last 8 month is given. Sales in this data are supposed to increase by 5% and we will hard code the 5% with the Define Name command.
- Open the Formulas tab.
- Select the Define Name command located in the Defined Names group.
- A new window will pop up.
- Type Percent_Increase in the Name box
- Enter “=5%” in the Refer to box field.
- Press OK.
- You will notice the Percent_Increase contains the value 5%.
- Select the G5 cell and put in the following formula:
=F5+F5*Percent_Increase
Press ENTER key.
- You will get the expected sales for the next year on the next screen.
- Now, drag down the Autofill handle to the last cell.
Did you notice how easy it is to hard code the Percent_Increase with Define Names formula?
How to Hard Code in Excel by Adding Number?
Last but not the least, this method uses the Paste Special command to add a hard code number. The dataset you will use here has the sales report of some fruits for a certain year along with an expected increase in sales for the next year. 1% another increase will be added here with the Paste Special command.
- Select the C13 cell.
- Press CTRL + C from the keyboard.
- Choose the D5:D10 range.
- Open the Home tab.
- Choose the Paste option.
- Click on the Paste Special command from the drop-down menu.
- A new window will appear on the screen.
- Click on the Add option given under the Operation.
- Press OK.
You will notice the Percent Increase values appear with an additional 1% increase and the values in the Expected Sales column are updated as well.
Summary:
This is how you can easily hard code values in Excel by using several approaches. You can hard code numbers to each cell as well as update the formula in Excel. Hopefully, this post helps you in clearing multiple queries that come up in your mind.
