Employing Cell References in Excel

There is an abundance of computer software applications used in homes and offices all over the world today. There are the word processing applications, the database applications, the presentation applications, and there are the spreadsheet applications - these are just a few among the many that make our jobs, and lives, more streamlined and manageable.

Opinions are a dime a dozen, but MS Excel is probably the most important and beneficial of all spreadsheet applications in the workplace. In fact, Excel dominates the spreadsheet product industry with an estimated market share of 90%.

It is for this reason that currently-employed and prospective workers are required to learn and stay abreast of Excel developments in order to be an asset in the work environment. Not only is Excel used for everyday functional tasks, such as basic spreadsheets and databases, but it is becoming increasingly utilised by employers for decision support too.

Perhaps you have been using Excel for some time now and you feel comfortable admitting you know the ins and outs of writing basic formulas, and you know how to copy those formulas from one cell to another. But, what often occurs is that your perfect formula, once copied to destination cells, produces incorrect results. The reason for this occurring is the difference between 'relative' cell references and 'absolute' cell references.

Relative cell references

When you use a relative cell reference in a formula, you are telling Excel that you are referring to a cell by its position relative to the cell in which the formula is written. This is Excel's default, but it can lead to an incorrect result for the answer you are looking for as, if it is moved it will change in relation to the new location of the formula. A relative cell reference looks like this: D2.

Absolute cell references

An absolute cell reference tells Excel not to think of a cell reference relative to the cell in which the formula is written. Instead, it tells Excel to think of it as a cell address. No matter where the formula is copied, the cell reference remains the same. Absolute cell references are made with the dollar sign to hold a column or row reference constant. The predominant reason for using an absolute reference is when you have a handful of formulas that all refer to the same cell and you want them to remain constant. An absolute cell reference looks like this: $D$2.

Mixed cell references

One can also make use of mixed cell references. For example, when you create a table where the values are derived from a multiplication table, as mixed cell references are a combination of relative and absolute. They can be either relative row, absolute column or absolute row, relative column. A mixed cell reference looks like this: D$2 or $D2.

As you are aware, whether your industry sector is private or government, education or accounting, manufacturing or human resources, you are very likely to be required to use Excel, as just about every workplace has a demand for a spreadsheet application that can do so much more than just elementary calculations. An Excel training course will assist you in staying in top form on this software application.