1.7 Relative References/Named Formulas

The named ranges you have seen up to this point have always referred back to a cell or range of cells in the workbook. Interestingly, names can also refer to static values or formulas, and those values don’t have to be stored in a cell in the workbook.

Consider an example where you have several calculations that will need to be done with sales tax. While you could create a cell in one of your worksheets and store the value, potentially, that is not needed and just clutters up the workbook. However, you can create a name for sales tax and simply hard-code the current sales tax (e.g., 6%) into that name, as shown in the following image. You can use =6% or =.06; both will work.

Then, this name, Sales_tax, can be used to calculate the values where needed. If the Sales_tax ever changes, you can edit the name and update the value.

You can also create Named Formulas in a similar way. Instead of hard-coding a value, you can enter a formula into the “Refers to” box. This can become very powerful as you use formulas that dynamically update. To illustrate, suppose you wanted a name that, when used, would display the name of the current month of the year. The formula to do this is fairly straightforward, but you will learn more about it in the Dates and Times lesson. You could use the following formula:

=TEXT(TODAY(),"mmmm")

This formula first gets the current date using the TODAY function and then uses the TEXT function to format the date using the pattern passed as the second parameter. The mmmm value indicates the date should be formatted to show the full name of the month (e.g., January, February).

We can create a new name called ThisMonthName, and then we can use that anywhere in our spreadsheet to get the current name of the month.

Names can also be used for relative referencing. That is, you can refer to cell values that are above, below, to the left, or to the right of the current cell. Perhaps we want to create a named formula that uses relative referencing to get the value of the cell to the right of it. To do this, we must first consider what cell we are currently in and then write the formula in relation to the current cell.

If we are in cell B6 and want to get the cell immediately to the right, we would create a new name and use =C6 as the formula in the “Refers to” box. Go ahead and define a name called CellToRight that refers to the value in the cell immediately to the right. Did it work? You can watch the video to understand how this works.

After you have defined your name, try moving to a different cell, say, C19, and go to the name manager and look at the name. What do you notice? The formula in the “Refers to” box now refers to D19. Why? Because you are using relative referencing!

Notice that by default the formula includes the name of the current worksheet. So, in our example, it will always refer to the Relative References worksheet. Notice that it also put single quotes around the sheet name because there was a space in our worksheet name. To get this to work on any worksheet, you need to remove the worksheet name but keep the exclamation mark. Essentially, that tells Excel to refer to the current sheet. If you only put a reference, when you save the name, Excel will insert the name of the current worksheet on the front of the cell reference. So, if you want it to work anywhere on the current sheet, use an ! and your cell reference.