logo
g Text Version
Beauty & Self
Books & Music
Career
Computers
Education
Family
Food & Wine
Health & Fitness
Hobbies & Crafts
Home & Garden
Money
News & Politics
Relationships
Religion & Spirituality
Sports
Travel & Culture
TV & Movies

dailyclick
Bored? Games!
Nutrition
Postcards
Take a Quiz
Rate My Photo

new
Romance Movies
Creativity
Family Travel
Southwest USA
Irish Culture
Home Finance
Comedy Movies


dailyclick
All times in EST

Low Carb: 8:00 PM

Full Schedule
g
g Desktop Publishing / Ebooks Site

BellaOnline's Desktop Publishing / Ebooks Editor

g

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


print
Printer Friendly
bookmark
Bookmark
tell friend
Tell a Friend
forum
Forum
email
Email Editor


Content copyright © 2014 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


g features
Becoming a Fiction Editor

Making a Background Transparent Using Photoshop

Merging Letters in Word 2007

Archives | Site Map

forum
Forum
email
Contact

Past Issues
memberscenter


vote
Poetry
Daily
Weekly
Monthly
Less than Monthly



BellaOnline on Facebook
g


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


BellaOnline Editor