Quick Tip for Microsoft Excel 2010

How to use Absolute Cell References in Excel 2010

Using AutoFill to copy formulas is a great way to save time, but the cell references in the original formula do not always need to be adjusted. There are some situations where a cell reference needs to remain constant.

For example:

Look at the spreadsheet shown below – everyone’s salary is due to increase by 10%. The first formula, to find Shing Chen’s new salary has been created. His current salary (in Cell B4) has been multiplied by the value in Cell B1, 10%.

The quickest way to work out everyone else’s increase is to copy Shing Chen’s formula. However, if the formula is copied using AutoFill as it is, the row numbers will be incremented, including the cell reference for the 10% increase, and we’ll end up with some funny answers…

Here, AutoFill has caused the row numbers to be adjusted. But the formula we need requires Cell B1 to remain constant even when AutoFill is used.

Excel must absolutely always look at this cell. In other words, we need to make it an absolute cell reference.

[callout]To learn more about Absolute Cell References and to see how we can help your organisation improve their performance with Microsoft Excel give us a call 020 8203 1774[/callout]