1. Tech

Your suggestion is on its way!

An email with a link to:

http://video.about.com/spreadsheets/Relative-and-Absolute-References-in-Excel.htm

was emailed to:

Thanks for sharing About.com with others!

Video:Cell References in Excel

with Bulk Item

Cell references are the foundation for performing powerful calculations in Microsoft Excel. Learn the difference between relative and absolute references before you get started working on your own spreadsheets.See Transcript

  • All Videos
  • All Tech Videos
  • Spreadsheets Videos

Transcript:Cell References in Excel

One of the most useful and powerful features of Microsoft Excel is the ability to reference other cells in a worksheet.  In this video, you'll learn about both absolute and relative cell references and how you can use them to make Excel work for you.  We'll be using Microsoft Excel 2010 on a Windows 7 PC.   

Getting Started

By referencing other cells, Excel can make calculations that will automatically change based on information elsewhere on the spreadsheet.  

Here we have a spreadsheet of sales for a small business.  Let's say we want to know the total sales.  Instead of manually adding the figures one by one,  we can use the sum formula.  Start by clicking in cell B7, and then click the sum button.  This tells Excel that we want to add other cells together and put the total in this cell.  

Next, choose which cells to add by clicking and dragging a box around them or control-clicking individual cells.  As you select your cells, notice how they are automatically added to the formula.  When you're finished, hit enter.  Excel has automatically added all of our cells together.   

Relative vs. Absolute References

Now that you've seen a cell reference in action, let's explore relative and absolute cell references.  The primary difference between these two types of cell references is that relative ones are allowed to change when copy-pasted, and absolute ones are not. 

Relative Reference in Action

Let's see a relative cell reference in action.  Earlier, we wrote what would be considered a relative cell reference.  Let's copy that reference formula, and paste it into cell C7.  Notice how the formula is now automatically referencing the C, rather than the B column.   

Absolute Reference in Action

What about an absolute cell reference?  Let's say you want to know how an additional two-hundred fifty dollars in sales of each product would affect profits. Click in cell B8, then click the sum button and control-click to select cells B7 and B10.  After selecting B10, press the F4 key.  Notice how Excel has added dollar signs to the B10 cell reference.  When you're finished, hit the enter key.  

Let's copy-paste this cell reference over to the C column.  Notice how this time, the reference to cell B10 has not changed, since we added the dollar signs.  This is an example of an absolute cell reference. Now it's your turn to put some cell references, relative and absolute, to the test!  

Thanks for watching.  For more great videos, please visit About.com.

About videos are made available on an "as is" basis, subject to the User Agreement.

©2014 About.com. All rights reserved.