Define Name for Worksheet Scope in Microsoft Excel for Mac V15.38

Question

I am trying to create named references that have a Worksheet scope in Microsoft Excel for Mac V15.38.

I want to set up a template sheet that gets duplicated multiple times (example: a template sheet for monthly sales that is used to create the new sheet for each month), so I want to scope the name so that it is only for that worksheet, so that the name does not have to be changed each time the template is duplicated to a new sheet.

I am trying to find out how to do this in Excel for Mac V15.38, however I can't seem to work out how to do it. I have looked and the Excel Help and Office Support page Define and use names in formulas, but it seems that even though this is supported in Excel 2016, Excel for Mac V15.38 is only scoping Names for the Workbook level.

Answer 1

(Note that this is for Office 2016 16.16.8, so it's possible it works differently than 15.38, although it also works in Office 2011 and 2019.)

The key is to prefix the name with the worksheet name, separated from the name with an exclamation point. Instead of just, say, Total, use Sales!Total, where Sales is the name of the worksheet (if the worksheet name contains special characters, like spaces, surround it with single quotes: 'Yearly Sales'!Total).

This works in both the Define Name dialog (via the menu Insert > Name > Define Name or Define Name on the Formulas ribbon) and the Name box (the box on the far left of the formula bar).

The Define Name dialog only shows global names and names specific to the current worksheet. Unfortunately Microsoft saw fit to remove the column showing the scope that was present in Office 2011, so there's no easy way that I know of of actually telling a worksheet-specific name from a global name, but they do work as expected.