HAN.xlsx [Read-Only] - Excel at Microsoft Excel!
Home
Introduction
Excel
101
So, you're new to spreadsheet huh.. Check this out!
Excel
Formula
How to write an excel formula!
New Posts
Love
Meter
Who is Your Crush's Big Time Crush?
Unprotect
Sheet
Remove password from protected worksheets and workbooks!
Dictionary
Attack
Open password-protected excel file using a dictionary attack!
Projects
Gag
Love
Meter
Who is Your Crush's Big Time Crush?
Other
Gag
Coming soon!
Social Network
Excel
Facebook
Coming soon!
Excel
Twitter
Coming soon!
Games
Formulas
Financial Modeling
Developer
Passwords
Password Recovery
Unprotect
Sheet
Remove password from protected worksheets and workbooks!
Dictionary
Attack
Open password-protected excel file using a dictionary attack!
Brute-force
Attack
Learn how to open password protected Excel File! Coming soon!
About
HANxlsx
About
Me
A little intro..
Privacy
Policy
Privacy Policy!
Disclaimer
Disclaimer!

[back to top!]

Coming up next!

Chatting and updating status using excel spreadsheet like a boss!

Our next excel project is to create a facebook-like excel spreadsheet that works like facebook. You don't say.. :D

Stay tuned, people! More cool stuff is coming!

Tuesday, October 30, 2012

Referencing Cells

A cell reference is relative by its default. For instance, when you refer to cell B2 from cell D2, you are actually referring to a cell that is two columns to the left (D minus B), and in the same row (2). A formula that contains a relative cell reference CHANGES as you copy it from one cell to another. This is really important to for a newbie to know!

So, this is how to lock the column or the row or the column and the row using F4 button on your keyboard!

Let's say, if you copy the formula =A2+B2 from cell C2 to D2, the formula in D2 adjusts downward by one row and becomes =A3+B3. If you want to maintain the original cell reference in this example when you copy it, you make the cell reference absolute by preceding the columns (A and B) and row (2) with a dollar sign ($). Then, when you copy the formula (=$A$2+$B$2) from C2 to D2, the formula stays exactly the same. You can do this by highlighting the cell that you want to make it absolute followed by hitting F4 (the one on your keyboard) to enable the dollar sign ($). 
The first hit on F4 button makes an absolute reference to the cell, the second hit locks the column, and later hit locks the row. Remember to put your cursor to formula bar when you hit F4 button. Where is formula bar?? Please read this post.

In less frequent cases, you may want to make a cell reference "mixed" by preceding either the column or the row value with a dollar sign to "lock" either the column or the row (for example, $A2 or B$3). 

The following picture shows how a reference type updates if a formula containing the reference is copied two cells down and two cells to the right.