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

absolute

ResolvedVersion 2003

Nicole has attended:
Excel Intermediate course
Outlook Advanced course
Excel Advanced course
PowerPoint Intermediate Advanced course

Absolute

How do you do absolute formulars?

RE: Absolute

Hi Nicole,

Thank you for your post, in answer to your question: For the sake of those who dont know, I will explain in a little more depth just what an Absolute reference rather than just saying select the Cell reference and press the F4 key.

There are different versions of an absolute reference, when you apply an absolute reference you can freeze the row, the column, or both by using a $ sign in front of either/or/and the row and column indicators, a quick way of applying the absolute signs ($) to a cell reference is to use the F4 (Function 4) key on the keyboard, this pressed repeatedly will cycle through the 4 options available.

There are two ways of looking at this operation:
1. To Absolute a single cell - select the cell reference in the formula and press the F4 key (Function 4) this will append the $ signs to the cell reference making it absolute. Now copying this formula using the Fill handle, will "pin" that cell reference in the formula to the absolute cell.
2. To apply absolute to a range of cells (a block) I think it is always easier to Name the range, to do this select the cells and then click in the Name Box (on the left hand end of the formula bar) type in a meaningful name and press the enter key.
This has named the cells and the name can be used in a formula ie =sum(Data2007) this will total all the cells in the named range Data2007, naming other ranges will allow you to create formulas of named ranges which will be absolute across the entire workbook.

I hope that has helped, if so please click the resolved link, regards Pete


Excel tip:

Checking if a calculation adheres to Order of Precedence

When writing formulas you must make sure that results will be calculated as you intended.

Excel adheres to the standard order of precedence for calculations. It calculates percentages, exponents, multiplication, and division in this order before calculating addition and subtraction.

For example, =7+5*3 results in an answer of 22, not 36.

To force a calculation to be completed before another calculations, place the section in parentheses: =(7+5)*3 will result in 36.

To check how excel is evaluating a formula, click on the cell and select the 'Tools' menu, select 'Formula Auditing' and click 'Evaluate Formula'

In the dialog box click on 'Evaluate' to watch as each part of the formula is successively calculated.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.