What is hard coding in Excel?

Written by sue smith | 13/05/2017
What is hard coding in Excel?
The data in an Excel spreadsheet can be transformed into visual displays. (Spencer Platt/Getty Images News/Getty Images)

Excel spreadsheets allow you to store and manipulate data values. The data values in an Excel spreadsheet are organised into rows and columns of cells. Each cell contains a single data value. This value can be hard-coded into the spreadsheet or calculated using a function, which may involve referencing other cells in the spreadsheet. Functions and calculations in Excel can also include hard-coded values. There are a number of considerations when using hard-coding in Excel spreadsheet cells.


In an Excel spreadsheet cell, you can enter a function in which a value is calculated. Often, this function or calculation will involve references to other cells in the spreadsheet or hard-coded values. For example, in one cell you could store a number representing the income for an organisation, with another storing the percentage rate for VAT. In a third cell, you could calculate the VAT on the income value using a calculation on the first two cells. The value in the third calculated cell would be a formula, while the values in the other two cells may be hard-coded. A hard-coded cell is one in which the spreadsheet user has typed the value in directly. The value may be one of various different types, but when the term "hard-coded" is involved it will typically be numerical.


Most Excel spreadsheets contain a mix of hard-coded values and values calculated using functions and references to other cells. The user needs to choose whether to include a hard-coded value in a particular cell depending on the needs of the spreadsheet. A calculation or function in a cell may also include a hard-coded value. For example, rather than storing the VAT rate in a dedicated cell as a single hard-coded value, the cell in which the VAT calculation appears could itself include the VAT rate as a hard-coded value as in the following example: \=A1*0.2


The only real advantage to using hard-coding in a cell or as part of a function is the simplicity of entering the value. Although Excel facilitates certain functions with shortcuts and allows the user to specify a cell reference by clicking on it, some users find entering formulas and calculations troublesome. In general a spreadsheet will be easier to work with in the long term when hard-coding is kept to a minimum.


Hard-coding can make a spreadsheet less reliable and more likely to be error-prone. For example, if a user carries out the VAT calculation in multiple locations in the spreadsheet, hard-coding the VAT rate into the calculation each time, they will need to carry out multiple edits if the VAT rate changes. If the VAT rate is hard-coded once into a single cell in the spreadsheet, the other calculations can refer to this one cell, changing it in a single location and the calculations throughout the spreadsheet will update instantly. If the VAT rate is stored in cell B1, other cells can refer to it as part of their calculations as follows: \=A1*B1

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.