A simple way to ensure that you’re referencing the specific data you want to reference is by assigning a name to the cell, or more typically to a range of cells, that you want to reference. For example, the WORKDAY function allows an argument providing specific non-work days, such as holidays. This is very convenient because not everyone celebrates the same holidays.
To simplify the process of creating formulas that refer to a specific range of data, and to make your formulas easier to read and create, you can refer to a cell or range of cells by a name that you define. For example, you might use the name Interest for a cell containing an interest rate, or you might use the name Holidays for a range of cells containing non-work days. In a formula, you refer to a named range by name. Thus you might end up with a formula like this:
=WORKDAY(StartDate,DaysOfWork,Holidays)
A formula using named ranges is simpler to understand than its standard equivalent, which could look like this:
=WORKDAY(B2,B$3,Data!B2:B16)
The range name is visible in the Name box at the left end of the Formula Bar, and in the various Names dialog boxes. If a cell is part of multiple named ranges, only the first name is shown in the Name box. The Name box displays the name of a multiple-cell named range only when all cells in the range are selected.
After defining a named range, you can change the range name or the cells included in the named range, or delete a range name definition, from the Define Name dialog box.
To define a selected cell or range of cells as a named range:
On the Insert menu, point to Name, and then click Define.
The Define Name dialog box opens. The content of the upper-left selected cell is displayed in the Names In Workbook box as a suggested range name. The selected cells are displayed in the Refers To box.
Verify or change the range name and cell range, and then click Add.