Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Hard Code in Excel (4 Easy Ways)

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.


Download Practice Workbook

You can download the practice workbook here.


4 Easy Ways to Hard Code in Excel

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.

manually entering value into formulas to show how to hard code in excel

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.

entering formula to show how to hard code in excel

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.


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.

using define name command to show how to hard code in excel

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%.

defining a name to a value to show how to hard code in excel

  • 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.


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.

applying paste special command to show how to hard code in excel

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.

choosing paste special command to show how to hard code in excel

  • 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.

using paste special command to show how to hard code in excel

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.

 adding values using paste special command to show how to hard code in excel

  • 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.


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. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo