g Text Version
Beauty & Self
Books & Music
Food & Wine
Health & Fitness
Hobbies & Crafts
Home & Garden
News & Politics
Religion & Spirituality
Travel & Culture
TV & Movies

Bored? Games!
Take a Quiz
Rate My Photo

Natural Living
Folklore and Mythology
Distance Learning

All times in EST

Clairvoyance: 08:00 PM

Full Schedule
g Desktop Publishing / Ebooks Site

BellaOnline's Desktop Publishing / Ebooks Editor


Excel-Types of Formula References

Guest Author - Chris Curtis

When you first begin using Excel, you give little thought as to how Excel adjusts the cell references when you copy a formula. You just know that it works, until you copy a formula that returns a result different from what you expected. Excel understands three types of cell referencing: relative, absolute, and mixed.

Relative referencing – Excel’s default for copying formulas. When you copy the formula, the row and column references in the formula automatically adjust to properly reference the correct cells in relationship to the copied formula’s new location. For Example: Suppose you have the following formula in Cell B1 (=A1). When you copy the formula in Cell B1 to Cell E7, Excel counts 3 columns and adds it to the column reference (A + 3 letters = D) and counts 6 rows and adds it to the row reference (1 + 6 = 7). The new Cell reference correctly becomes D7.

Absolute referencing – when you want the referenced cells to always be the same cell no matter where the formula might be copied. For example, suppose you had a constant tax rate that is applied to various purchases. Cell A1 contains the tax rate. In column A starting in cell A3, you have a list of sale amounts and you want to see the amount of the tax amount in column B. You create a formula in cell B3(=A3*A1). You know if you copy that formula down column that the A1 will change but you don't want that to happen because the tax rate is in cell A1. Excel uses the $ to designate an absolute reference. It tells Excel to ALWAYS use that cell location ($A$1)no matter where the formula is copied. The proper reference for your formula in cell B3 is (=A3*$A$1) thus when copied down column be the A1 referencing will remain absolute. Excel copies it exactly the same because it interprets the $ to mean you want an absolute reference to that cell.

Mixed References – when you want either the column or the row reference to be fixed. Excel interprets the reference $A1 as an absolute column reference combined with a relative row reference. Meaning, when copied, the column reference would not change but the row reference would change. Alternatively for a relative column reference combined with an absolute row reference the correct notation would be shown as A$1. In this case the column reference would change but the row reference would not change.

F4 Key
The F4 key on your keyboard will toggle the choices for cell referencing. With the cursor blinking next to the cell reference in your equation, press the F4 key once to get an absolute Column and Absolute Row reference ($A$1); press the F4 key twice to get a relative Column and absolute Row reference (A$1); press the F4 key three times to get an absolute Column and relative Row reference ($A1); press the F4 key four times to return to a relative Column and relative Row reference.

Add Excel%2DTypes+of+Formula+References to Twitter Add Excel%2DTypes+of+Formula+References to Facebook Add Excel%2DTypes+of+Formula+References to MySpace Add Excel%2DTypes+of+Formula+References to Del.icio.us Digg Excel%2DTypes+of+Formula+References Add Excel%2DTypes+of+Formula+References to Yahoo My Web Add Excel%2DTypes+of+Formula+References to Google Bookmarks Add Excel%2DTypes+of+Formula+References to Stumbleupon Add Excel%2DTypes+of+Formula+References to Reddit

RSS | Related Articles | Editor's Picks Articles | Top Ten Articles | Previous Features | Site Map

For FREE email updates, subscribe to the Desktop Publishing / Ebooks Newsletter

Past Issues

Printer Friendly
tell friend
Tell a Friend
Email Editor

Content copyright © 2015 by Chris Curtis. All rights reserved.
This content was written by Chris Curtis. If you wish to use this content in any manner, you need written permission. Contact Laura Nunn for details.


g features
Becoming a Fiction Editor

Making a Background Transparent Using Photoshop

Merging Letters in Word 2007

Archives | Site Map


Past Issues

Less than Monthly

BellaOnline on Facebook

| About BellaOnline | Privacy Policy | Advertising | Become an Editor |
Website copyright © 2016 Minerva WebWorks LLC. All rights reserved.

BellaOnline Editor