Making a Cell Absolute

Resolved · High Priority · Version 2003

Francis has attended:
Excel Advanced course

by - delegate Francis [2 posts] (2008 May 27 Tue, 17:08) Reply

I need to make a particular cell absolute before I can use the Nestedif function effectively - please can you tell me the steps again. Thanks.

Version	Date	Location	Places available	Book	Next place rate:
Excel VBA 2 day course
Version	Date	Location	Places available	Book	Card	Invoice
2007	2008 Nov 25 Tue + 26 Wed	Bayswater	6	book now	£455	£515
2003	2008 Nov 26 Wed + 27 Thu	Bloomsbury	2	book now	£495	£495
2003	2008 Dec 1 Mon + 2 Tue	Bloomsbury	2	book now	£495	£495
2003	2008 Dec 4 Thu + 5 Fri	Bloomsbury	5	book now	£450	£485
2007	2008 Dec 11 Thu + 12 Fri	Bayswater	5	book now	£475	£515
2007	2009 Jan 8 Thu + 9 Fri	Bloomsbury	6	book now	£450	£475
Full Schedule: See all 37 Excel VBA course dates. Bookings currently available until 26th November 2009.

RE: Making a Cell Absolute

by - trainer Amanda diamond contributer [1208 posts] (2008 May 29 Thu, 14:31) Reply

Hi Francis

Thank you for your question; and welcome to the forum.

You can make a cell into an absolute reference by doing either of the following:

1. If you are in the process of putting the formula together, then once you have clicked on the cell to enter it into the formula, press the function F4 key immediately afterwards. This will put $ signs in front of the column letter and row number of the cell for you.

2. If you have already put the formula together, you can select the cell that contains the formula, then click next to the cell you wish to make into an absolute reference in the formula bar at the top of the screen. Then press the function F4 key to introduce the $ signs.

I hope this helps.
Amanda

· 5 Microsoft Excel Charts for Creating Stunning Business Documents
· Creating Smarter, Customized Invoices for your Small Business with Microsoft Excel
· Microsoft Excel Training: Not Just for Newbies Anymore
· Crazy Things People Have Done With Excel
· Basic Excel Training

Please browse our web site to find out more about
excel+training and other Microsoft training courses.

Excel tip:

Stop Formula Returning A "#DIV/0" Error

If a formula returns a #DIV/0 error message there is a way to avoid such results.

For example the formula =A1/B1 will return a #DIV/0 if B1 is empty or a zero.

If you protect your formulas with the ISERROR function, the formula will then look like this:

=IF(ISERROR(A1/B1),0,A1/B1)

In plain English: should the result of A1 divided by B1 be an error change the result to 0 else show the result of A1/B1.

View all Excel hints and tips