What Is “Hard Coding” in Microsoft Excel?

Hard coding sounds for illustration a analyzable programming concept, but it’s a basal conception successful Microsoft Excel. Let’s study what it is and erstwhile you should usage it.

The conception is acquainted to those well-versed successful a programming connection — conscionable switch cells pinch variables that clasp circumstantial values, and nan functional meaning remains nan same.

A Crash Course successful Cell References

To understand difficult coding, we request to cognize how Excel compartment references work because that’s wherever it each starts.

Every compartment successful Excel tin beryllium referenced by combining its alphabetical file number and its numerical statement number. This lets you bespeak circumstantial cells pinch notations for illustration A3, D5, etc.

Instead of individual cells, you tin reference full ranges pinch a colon. Typing B4:B12, for example, gives you each nan cells successful that range. You tin moreover link sheets betwixt different sheets and workbooks pinch these relationships.

Almost each mathematical operations connected nan cells are performed utilizing their compartment references, which is much convenient than looking up and entering nan existent values. There is different reason, but much connected that successful nan adjacent section.

Why Is It Recommended to Use Cell References successful Excel?

The premise of utilizing Excel is nan convenience of updating values owed to its formulas. Say you person a spreadsheet calculating a company’s full expenses successful a period and are utilizing that full successful galore different graphs and calculations.

With pen-and-paper, you will person to recalculate nan full and each nan different calculations that were utilizing its value, which is simply a tedious and error-prone process. Not to mention adjacent intolerable if nan spreadsheet has thousands of entries successful it.

But successful Excel, arsenic agelong arsenic you usage nan correct compartment references, you don’t person to interest astir fixing calculations. Change immoderate worth you request to, and each nan calculations referencing that compartment will automatically update their results (though you mightiness person to usage nan Calculate Sheet button).

What Is Hard Coding? When Should You Use It?

Hard coding successful Excel is erstwhile you ditch compartment references successful favour of absolute values. This intends nary matter what changes return spot pinch nan cells aliases their contents, nan hard-coded values stay nan same.

Generally, it is not a bully thought to usage hard-coded values. Poorly planned retired difficult coding is really analyzable spreadsheets get surgery since it is simply a symptom to hunt down nan hard-coded values and alteration them should nan request arise.

But, location are definite situations wherever difficult coding is practical. Things for illustration constants aliases humanities information are values that you want to support nan aforesaid based connected their references.

For specified numbers, we usage difficult coding successful Excel. Here are immoderate ways of doing it.

Methods of Hard Coding

Manually Entering Values (Not Recommended)

The easiest (and astir inadvisable) measurement of difficult coding is manually entering nan numbers you need. This intends not utilizing compartment references but simply typing successful nan values.

You should only usage this method for drawstring values since they are not meant to beryllium utilized successful calculations. And, of course, nan first clip you participate immoderate values successful your spreadsheet is besides technically difficult coding.

Just retrieve to usage references successful immoderate look utilizing these guidelines values.

Define a Constant (Using Name Manager)

The much elegant measurement of difficult coding is by defining changeless values. Excel provides nan Name Manager for doing conscionable this.

The thought is to springiness your hard-coded worth a sanction successful nan spreadsheet truthful that it is clear what nan domiciled of nan worth is, too giving you a cardinal location from which to alteration it. This is simply a batch for illustration really constants usability successful galore programming languages.

Though you mightiness beryllium wondering: what’s nan constituent of utilizing Define Name erstwhile you tin get akin results by entering nan worth into a dedicated compartment and referencing it? The only quality is that nan sanction much intelligibly signifies what nan worth represents.

You tin specify an full scope of values pinch this method, isolated from creating named constants that don’t beryllium connected nan spreadsheet.

1. To specify your ain constants, prime nan Formulas tab successful Excel and click connected nan Name Manager icon.

2. If immoderate cells are selected, nan Name Manager model will springiness nan values a sanction based connected nan discourse (usually nan statement aliases file label) and create a sanction for you. You tin besides click nan New fastener to specify your names and values from scratch.

3. Now you tin usage this information successful immoderate of your formulas utilizing nan defined name. It acts arsenic a much descriptive compartment reference.

With nan Paste Special Command

Usually, erstwhile you transcript a action of cells containing formulas, Excel automatically adjusts nan references to their caller location. But what if you are copying complete an aged dataset and only request their original values arsenic it is?

This is wherever nan Paste Special bid comes in. Paste Special tin selectively paste definite aspects of nan information you are copying alternatively of nan full thing.

The communal usage of this bid is to transcript complete conscionable nan formulas used, but you tin besides do nan reverse and paste conscionable nan values instead. This gives you nan results of nan original calculations successful a hard-coded shape truthful that immoderate changes to nan original numbers will not impact them.

1. First, prime nan cells you want to copy.

2. Right-click and prime nan Copy option, aliases conscionable deed Ctrl + C.

3. Now right-click connected your target compartment to paste nan contents. The caller menus person various paste-special icons arranged horizontally. Mouse complete them to find nan action for Values and click connected it.

4. The values of nan cells will beryllium pasted successful nan caller location. You tin prime nan compartment and cheque nan look tab to spot that it is simply a hard-coded value.

Is Hard Coding Worth It successful MS Excel?

Unless you cognize what you are doing, avoiding immoderate shape of difficult coding is simply a bully idea. Remembering to usage comparative or, moreover better, absolute compartment references and keeping your formulas cleanable will prevention you a batch of headaches down nan line.

That being said, a observant personification tin return advantage of immoderate circumstantial ways of difficult coding to make definite calculations much concise and elegant. Things specified arsenic named constants aliases operations performed done Paste Special tin present hard-coded values without making a messiness of your spreadsheet.

Manual difficult coding, connected nan different hand, should ever beryllium avoided unless you are entering guidelines information to usage further successful spreadsheet calculations.