In this article, we will show how to hard code in Excel. Hard Code means stating a specific value into a formula or cell rather than referencing the cell containing the value. The advantage of hard code is that the hard code value will not change if the values of other cells change. A user has to change the hard code value manually. This helps to keep the integrity of data.
How to Hard Code in Excel: 4 Easy Ways
In this article, we will show 4 ways how to hard code in Excel. Firstly, we will enter the values manually into the formulas without referencing any cell value. Secondly, we will use the Define Name command to hard code a value. Thirdly, we will apply the Paste Special command to hard code. Finally, we will use the Paste Special command again to add a hard coded number to cell values.
1. Manually Entering Value into Formulas
In this method, we will enter the values into the formula directly to hard code the value. Here, we have the unit price, sales quantity and sales of different fruits by date. We will try to find out the total sales of a particular fruit by using the SUMIF function. In the process, we will manually input the name of the fruit into the formula rather than passing in reference to the cell containing the fruit’s name.
Steps:
- To begin with, choose the C14 cell and enter the following formula,
=SUMIF(C5:C12,"Apple",E5:E12)- Then, hit Enter.
- As a result, we will get the total number of apples sold for that time period.
The formula will not show an error if we replace apples with another fruit. The formula will show zero. However, if we had given the cell reference then the formula would return the quantity sold for that fruit.
Read More: How to Keep a Cell Fixed in Excel Formula
2. Using Define Name Command
In this instance, we will use the Define Name command to hard code a value in Excel. Here, we will name a particular value by using the Define Name command and then use it in formulas. The value will not change automatically since it will be hard coded, and the user will have to change it manually. Here, we have the data of fruit sales for the last 8 months. And the sales are expected to increase by 5% in the next year. We will hard code the 5% by using the Define Name command.
Steps:
- Firstly, go to the Formulas tab.
- Secondly, choose the Define Name command from the Defined Names group.
- As a result, a prompt will be on the screen.
- In the prompt, first, write Percent_Increase in the Name box.
- Secondly, in the Refer to box, write “=5%”.
- Finally, click on OK.
- As a result, the Percent_Increase will contain the value 5%.
- Now, choose the G5 cell and insert the following,
=F5+F5*Percent_Increase
- Then, press Enter.
- As a result, we will get the expected sales for the next year.
- Finally, lower the cursor down to the last cell to autofill.
The Percent_Increae variable is hard coded because the value it contains, 5%, will not change automatically or with the changes of the dataset.
Read More: How to Make Different Types of Cell Reference in Excel
3. Applying Paste Special Command
Here, we will turn a formula into hard numbers by using the Paste Special command. In the process, the formula will be replaced by a number. The number is a constant and thus no one can change the formula. So, the formula will be hard coded. Here, we are calculating the number of apples sold for a particular period using the SUMIF function.
Steps:
- Firstly, select the C14 cell and press Ctrl + C.
- Secondly, go to the Home tab.
- Thirdly, choose the Paste option.
- Finally, from the drop-down option select Paste Special.
- Consequently, a prompt will appear on the screen.
- In the prompt, first, choose Values under the Paste option.
- Then, click on OK.
- As a result, we will have a value in the C14 cell instead of the formula.
Thus, the formula is hard coded.
4. Using Hard Code to Add Number
In this final method, we will use the Paste Special command again to add a hard coded number to a range of numbers and update the formula. Here, we have the sales report of some fruits for a particular year. We also have the expected increase in sales for the next year. However, another 1% increase will be added to each of the percent increase values due to some parameters of the business that are expected to be more positive than expected. We will add this hard coded 1% value to our existing dataset by using the Paste Special command.
Steps:
- At the beginning, select the C13 cell and press Ctrl+C.
- Then, choose the D5:D10 range.
- After that, go to the Home tab.
- From there, select the Paste option.
- Finally, from the drop-down list, choose the Paste Special command.
- Consequently, a prompt will appear on the screen.
- Now, select Add under the Operation option.
- Next, click on OK.
- As a result, all the Percent Increase values will have an extra 1% added to them and the values in the Expected Sales column will also be updated.
In this way, we will add a hard code number to each cell value and update the formula.
Download Practice Workbook
You can download the practice workbook here.
Conclusion
In this article, we have described how to hard code in Excel in 3 ways. These methods will help users to keep the integrity of their dataset secured.If you have any questions regarding this essay, feel free to let us know in the comments.
Further Readings
- How to Use Cell References in Excel Formula
- Relative and Absolute Cell Address in the Spreadsheet
- Difference Between Absolute and Relative Reference in Excel
- Cell Reference in Excel VBA
- Excel VBA: R1C1 Formula with Variable
<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel
