1.8 Multisheet Names

Named formulas can also be created that refer to cells across multiple worksheets in the same workbook. This can be especially powerful because the named range refers to multiple values simultaneously. These named ranges can then be combined with aggregate functions (e.g., sum, average) to perform mathematical operations on the underlying values.

For example, for each month you might track the expenses across employees in your organization with the expense categories on separate rows and employees in separate columns. Each tab might contain the expenses for one month. So, you might have a tab for January, February, March, etc. For the month of January, it might look something like the following:

A multisheet named range can be created to refer to the same expense category for a given employee. If relative references are used, then a single name formula can refer to the same cell value on all the sheets by simply altering the formula to refer to the beginning and ending sheets.

As a reminder, the formula for a named range might look something like this:

=January!$D$6

For a multisheet formula, you simply adjust the sheet reference to include a starting sheet and an ending sheet and separate them using a colon.

=January:December!D6

In the above code block, you may have also noticed that the cell reference is relative. That will allow us to use that same formula in additional cells and columns. Of course, since the cell reference is not absolute, it matters what cell we were in when we defined that name. Typically, you will be in the same cell that you want to reference when you define the name. That way, when you use the named formula, the cell will reference the same cell on every sheet between the January worksheet and the December worksheet. Typically, multi-sheet formulas are used with aggregate functions on a separate worksheet, as in the example in the accompanying video. However, you can get creative in how these are applied.

Using names, including named ranges and named formulas, is powerful. Using names makes your workbook and formulas easier to understand and more intuitive. Let’s face it: humans are not very good at keeping track of combinations of letters and numbers. Names make formulas much easier to read, and using some of the concepts we covered in this lesson is sure to simplify the worksheets that you regularly use.