Copyright

Cell References in Excel: Relative, Absolute & Mixed

Cell References in Excel: Relative, Absolute & Mixed
Coming up next: Defining Order of Operations in Excel

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:01 Relative, Absolute and Mixed
  • 0:31 Understanding Cell References
  • 1:25 Relative
  • 3:00 Absolute
  • 5:24 Mixed
  • 6:27 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

Cell references are often used when creating Excel worksheets and writing formulas. This lesson will discuss the different types of cell references, such as absolute and relative. We will also look at examples of each one.

Relative, Absolute and Mixed

A key element of a formula is the cell reference, and there are three types:

  • Relative
  • Absolute
  • Mixed

The type of cell reference is important when writing a formula, since each behaves differently when copied or moved to another cell. This lesson will first discuss what is meant by a cell reference. Next, you will learn the differences between the three types and look at examples of using each one.

Understanding Cell References

Cell reference means the cell to which another cell refers. For instance, if in B2 you have =B3, cell B2 is referring to cell B3. The cell reference in this example is B3. Confused? Let me explain.

If we look at a formula such as =sum(B2:B10), the cell reference is B2:B10. The formula is telling Excel to sum or add all of the numbers starting in cell B2 through cell B10. Sometimes the cell reference may be referred to as the cell range because most references in a formula refer to multiple cells within a range. Okay, now that we understand cell references, let's take a detailed look at each type.

Relative

When you enter a cell reference or range into a formula, by default, the reference is relative. And, no, I am not referring to a member of the family. Remember in the beginning of the lesson when I told you that each type behaves differently when copied to other cells? Relative cell references, when copied to another cell or across multiple cells, will change based on where you copy them. Let's use an example.

We have a sample worksheet with income, expenses and profit numbers for five different stores. Column B is titled Income, column C is titled Expenses, and column D is titled Profit. The formula in D3 for Store 1 calculates the profit. The formula is =sum(B3 - C3). This is a very basic formula that takes the income and subtracts the expenses.

Imagine you want to copy the formula in D3, down the column for the other stores. When you copy the formula, the cell references will automatically change to reference the new row, row four, five, six and so on. Instead of =sum(B3 - C3), you will get =sum(B4 - C4). Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.

Absolute

So, there may be times when you don't want the cell reference to change when copied to another cell or range of cells. This is where absolute cell references come in handy. How do you tell Excel that the cell reference needs to be absolute? Well, that part is easy - just add a dollar sign before the letter and number. For instance, =sum($B$3 - $C$3). Unlike relative references, absolute references do not change when copied. You can use an absolute reference to keep a row or column constant. Let's use it in an example.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support