microsoft excel 2003 vba courses - absolute references

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » microsoft excel 2003 vba courses - Absolute references

microsoft excel 2003 vba courses - Absolute references

resolvedResolved · Low Priority · Version Standard

Jo has attended:
Excel Introduction course

Absolute references

can you explain why we must use absolute formulas in some instances?

RE: absolute references

Hi Jo

Thank you for your question.

The concept of an absolute reference can be confusing, but it is useful to know how to change a cell into an absolute reference if you need to.

The idea behind this is that if you put together a formula, for example, =A1*B1 (multiples what is in cell A1 by what is in cell B1) the cells in the formula will change when it is copied to another cell in the spreadsheet.

If you need to make Excel keep multiplying by the value in cell B1 when you copied the formula to another cell in the spreadsheet, then you would need to make cell B1 an absolute reference so the formula would look like this =A1*$B$1

This means that if the formula is copied to another cell in the spreadsheet, cell B1 will remain constant or fixed in the formula - it will not change when the formula is copied to another cell.

I hope this helps.

Amanda


 

Excel tip:

Naming and Using Constants

Constants make calculations easier so worksheets are more easily understood. Constant values also need to be given relevant and memorably names. It is also easier to change the value of a constant.

For example:
Instead of entering 17.5% in each cell when you generate a VAT amount you could name a Constant "VAT" and assigning a "0.175" value to it. To do this:

From the 'Insert' menu select 'Name', then select 'Define'.

Enter the constant

View all Excel hints and tips


Server loaded in 0.08 secs.