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.
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.
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.
There are three types of cell references:
In this post, we will deal with relative and absolute cell references.
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.
Now I’ll copy and paste this formula to the rest of the cells in column D.
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.
Cell ranges are used in a number of formulas, for example the =SUM() formula, which adds up a range of numbers.
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.
Now look what happens when I copy and paste this formula.
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.
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 Wednesday, January 24, 2018. 9:00 a.m. to 12:00 p.m.
Click here to register Thursday, May 24, 2018. 9:00 a.m. to 12:00 p.m.
Click here to register Wednesday, September 19, 2018. 9:00 a.m. to 12:00 p.m.