VOices Blog Members Home Events English | Français Shopping Cart
Skip to main content
VOices

Blog > Using Cell References in Excel

Using Cell References in Excel

posted on Sep 8, 2017
excel%2bbasics-350px.jpg

Cell references are extremely important in Excel. They really make the difference between using Excel as an expensive calculator and using it as a spreadsheet. A cell reference uses a cell address in a formula instead of the number. You can change the number in the referenced cell, which will update the formula. Cell references are especially useful when you need to do repeated calculations on a series of data.

What does a cell reference look like?

Every cell has a unique identifier made up of a letter (or letters) and a number. The letter is the column; the number, the row. In the screen capture below, the cell reference for the orange cell is A1, and the green cell is B4.

2017_09_05_Cell_References_-_1.PNG

You can reference a single cell or a range of cells. When referencing a range of cells, the reference will have a colon in between the first and last cell references in the range. For example, the blue cell range above is referenced as D2:D6.

You can enter a cell reference in two ways.

  1. Type in the letter(s) and number of the cell being referenced
  2. Once you have typed the = sign to let Excel know you are entering a formula, you can click the cell you want to reference.

There are three types of cell references:

  1. Relative
  2. Absolute
  3. Partially absolute

In this post, we will deal with relative and absolute cell references.

Relative cell reference

Cell references are relative by default. This means that if you copy and paste a formula which contains relative cell references, Excel will change the actual cell reference each time you paste it. Here is an example. I have entered the formula which will multiply the cost of each item by the quantity. Below is what the formula looks like with the cell references.

2017_09_05_Cell_References_-_2.png

Now I’ll copy and paste this formula to the rest of the cells in column D.

2017_09_05_Cell_References_-_3.png

Notice that Excel has automatically changed the cell references to multiply numbers in the same way as on the first line. In this case, I asked it to multiply the number two cells to the left by the number one cell to the left.

Why would I reference a range of cells?

Cell ranges are used in a number of formulas, for example the =SUM() formula, which adds up a range of numbers.

2017_09_05_Cell_References_-_4.png

Absolute Cell Reference

When you use an absolute cell reference, then Excel will always refer to that exact cell reference when you copy and paste your formula. For example, let’s say we were planning to sell the items in the table above. The price is the amount we pay. We want to experiment with different profit amounts, to see what will work best.

We can set up an absolute cell reference, which means we can change the number in one cell to update the whole table. Cell C1 is set as an absolute reference by putting a dollar sign ($) in front of the letter and another in front of the number.

2017_09_05_Cell_References_-_5.PNG

Now look what happens when I copy and paste this formula.

2017_09_05_Cell_References_-_6.PNG

The cell reference for the cost (column D) has updated to the current row, but the absolute cell reference has stayed the same. ($C$1).

Now that the formulas have been set up, we can change the number in the “percentage profit” cell (C1) to see effect that will have on our overall total profit.

2017_09_05_Cell_References_-_7.PNG

Cell references are a really easy tool to start taking advantage of the power of Excel as a tool for data analysis. To learn more, come to our upcoming professional development seminar.

Click here to register  VO Night School  Thursday, September 28, 2017. 6:00 to 9:00 p.m.

Related Posts

Data is your friend!

Turning Data into Information – How to use Excel to make decisions

 

Filed under: Operations
new comment comment