Excel Cell References and How to Use Them in Your Worksheets and Formulas

Excel cell references are the lifeblood of an Excel spreadsheet. Cell references are the names that Excel uses to indicate the location of information you enter into a spreadsheet. You need to understand how cell references work, and how to access various cell references to harness the power of Excel. Cell references are used in formulas so it’s important to understand how to use cell references to create formulas that work for you.

Excel is used by millions of users worldwide and has become the standard for spreadsheet applications. If you are new to Excel, sign up for the Excel for beginner’s course today and learn to harness the power of Excel for all your spreadsheet needs.

Let’s examine how you can use cell references to refer to the various cells in your worksheet and how you can use different types of cell references in your formulas.

Basic Excel Cell References

Cells are named using the Column and Row names that refer to a particular cell. Column names range use letters:

Row names use numbers to reference a cell.

To refer to a cell in an Excel spreadsheet, you need to refer to the column name and row number. So for example to refer to the first cell in an Excel worksheet you need to use the cell reference A1. Here is a worksheet with the cell references for each cell as the value of the cell:

Creating a basic formula using a cell reference

The real power of cell references lies in using them to create formulas for your spreadsheets.

Take a look at the following spreadsheet:

We have created a spreadsheet of student grades. The formatting of the cells is for ease of use.

For a great introduction to Excel, sign up for Excel 2013: Introducing Excel from Udemy today.

To create a formula for the total grade, you can use the cell references to add the values in cell C3 and D3 together.

This is what the formula will look like using basic cell references:

Creating formulae for the other students, the resultant worksheet will look like this:

To create the above formulae, you can either enter the formula in each cell or you can copy and paste the references to the adjoining cells. When you copy and paste cell references in this way, Excel automatically adjusts the cell references to reflect the new column and row values. So in our example Excel copies +C3+D4 and pastes the cell formula as +C4+D4 in the next row and +C5+D5 in the following row. These cell references are called relative cell references because Excel adjusts them automatically.

To learn how to use Excel like a master, sign up for the Excel Essentials course now from Udemy.

There are times when you do not want Excel to adjust the cell reference. To force Excel to copy the cell reference without changing it, you need to use absolute cell references instead of relative cell references.

How to Use Absolute Cell References

To explain how absolute cell references work, we will use another example. Take a look at the spreadsheet below:

The above spreadsheet was designed to calculate commissions due to each salesperson. The formula to calculate the commission due to each salesperson is: Sales x Commission rate. Let’s enter the formula into the first cell to calculate the commission due for Jack.

The result is as follows:

Now let’s copy that formula to the other cells:

Excel has automatically increased the row and columns of the formula, but in this case we don’t want Excel to increase the value of C2 so we need to tell Excel that C2 is an absolute reference and not a relative reference. To specify a reference as an absolute cell reference, you need to include $ signs before the cell reference using the following format: $Column$Row.

If we add a dollar sign to the C2 cell reference above and copy the formula, this would be the result:

And if we copy this formula to all of the rows, the spreadsheet results would look like this:

For some great tips and tricks on Excel, read the Excel Tips and Tricks: Use Excel like a Pro article on the Udemy blog.

Now that you know how to create relative and absolute cell references, we will take a look at another way to reference cells in Excel.

Working with Named Cells or Ranges in Excel

Instead of referring to the absolute or relative cell range in Excel, you can choose to name cells or ranges of cells and then refer to the name in the formula. This is great way of creating formulas that make more sense than C2*D5 for example.

To name a cell, you need to select the cell you want to name and then select the formula tab from the menu.

Select “Define Name” and enter a name for the cell. We will call this cell CommissionRate. Names cannot contain blank spaces. 

Now we can enter a formula to calculate Jack’s commission using the cell name instead of the normal Excel cell reference.

To enter the formula click on D5 and then enter =C5*CommissionRate

When you name a cell or range of cells, Excel assumes that the named cell is an absolute reference so there is no need to add dollar signs to the reference when you copy the formula.

Named cells and ranges make it easy to work out what a formula does. You can name a cell or a whole range of cells to use in a formula.

Take a look at this last example:

To calculate the average commission earned, you could add up all the commissions due and then divide by 4. You could also name the range and then sum the range and divide by four.

To name a range of cells in Excel, select the range you want to use in the formula, select the formulas tab and then select define name and choose a name for your range.

You can now refer to that range in a formula instead of selecting the range each time with the added advantage that the range is now an absolute range reference.

Naming cells and ranges is just one of the reasons why people consider Excel the top spreadsheet application available today. To learn how to really harness the power of Excel, sign up for the Microsoft Excel 2010 Course Beginners/ Intermediate Training course from Udemy now.