Discussion on:

Download our sample Excel ledger to learn about absolute cell references

I was recently looking for this exact information. The closest this I could find in the documentation was labeling the cell and the referencing the label in the formula. This works great if you want the cell to be absolute throughout all copies ofthe formula, but it is a little hard to find the original reference.

Thanks.

Every time I start to take advantage of
information at TechRepublic it for a PC.

When will you start doing programs that are
friendly to both Mac and

AOL?, "it for a PC."?, "Mac and ...."? I think you're in the wrong place, Zemandemon. TechRepublic doesn't "do programs". It is a site for IT professionals, not casual Mac users. You could just as well complain about the lack of COBOL and PASCALprogramming info on this site.

Been using these babies for years, very handy. Simplest example is a multicolumn invoice or quote form with hardware in one set of columns and software in another. In both sets of columns, a simple quantity x unit price = line subtotal formula applies, but the customer's discount applies to every line. So I put that discount at the top and make references to it absolute. I develop the formula for the lines once and copy/paste for the other 50 lines. Say D4 = A4*B4 and the discount is in B2. I might use something like A4*B4*$B$2
In another example, the two groups of columns have difference discount rates. Here, I'd make the row reference absolute but not the column. Say the discount rates are in B2 and G2. I'd use something like A4*B4*B$2 so I could mass copy it to both halves of the page.

I found Mr. Johnsons article on absolute cell referencing VERY useful. Not, however, because of absolute cell referencing, I already was familiar with that technique and have used it frequently. What was cool was the aside about R1C1-style referencing, more importantly, how to turn it on. Somehow when my organization started installing Office 97, the install configurations were set to default to NO R1C1 referencing. This condition has persisted through to our introduction of Office 2000 as well. So there were all these old spreadsheets that didn't work unless they were modified to work around the R1C1 referencing. Now that I know Tools>Options>General>R1C1 I will be a hero! What is strange to me is: Why is their an option on R1C1?Given that there is an option, why would the default be no R1C1? Given all of that, why is the error message that you get when you attempt to use R1C1 when it is turned off so vague? (It simply observes that you have a syntax error in a formula)Thanks Bob,
Ron

When you enter a basic formula the cell reference defaults to relative format.

Back in the corners of my dim memory I remember Excel 5 provided a keyboard shortcut which allowed you to cycle your cell reference thru relative -> absolute -> row absolute -> column absolute and back to relative.

This was used when your cursor was situated somewhere within the cell ref in the formula bar. I cannot for the life of me find it in Excel 97. Does anyone know if it still exists?

It was much faster for amending complex, newly created formulas than fat-fingering your way thru the formula bar with the cursor keys and the occasionally elusive "$" key....

I remember that too. In Excel 97, press the F4 key. It will cycle through the references in the edit mode. I also noticed that if you have more than one reference in the formula, the F4 key only works with the reference where the cursor is.

Hopesthis helps.

What Mr. Johnson refers to as an absolute reference is actually a mixed reference, or a mixture of a relative reference and an absolute reference. An absolute reference would tatke the form $D$7.

I would like you to publish a demo on Pivot tables oulining its benefits.

It's strange why people use Excel (or any spreadsheet) for years and don't know such a basic thing as absolute reference.

Relative and Absoluter references are the very basic thing of spreadsheets. It exists throughout the whole history of spreadsheets since very first version of 1-2-3 in 1970s.

I was teaching Excel in 1995-96 and I was always starting the course from this important topic. It is vital for any calculation.

So why NOW people don't know about it?
Why the documentation does not clearly explain that in the first place?
Are Microsoft documentation teams SO ignorant in spreadheet basics?

I found the explanation of absolute cell references very confusing. You needed an objective at the beginning of the description to help the reader understand why absolute referencing was being used.

I had taken a course and have been using absolute referencing for some time so was able to figure out what you were trying to say.

Bob's article about absolute cell references in Excel is an excellent instruction tool. Thanks for the deeper understanding. And keep it coming!

As always, great information.

Thanks for that Bob the example provides a very practicle application.

Looks interesting but when I click on the Click here to download the Excel spreadsheet, it says no match. Anyone know where I can find the file? Thanks

The link to download the example file is dead!