By Ted French
Related Tutorial: Excel Formulas Overview
A name can be given to a range to make it easier to identify this cell or cells when creating formulas or charts.
Normally, we use cell references - such as B8 or C5 - when creating formulas in Excel.
Cell references point to the location of the data to be used in the formula rather than entering the data itself in the formula.
One advantage of this approach is that if you later change your data the formula automatically updates to show the new result.
A second advantage is that, in certain instances, it makes it possible to copy formulas from one location to another in a worksheet.
If data is laid out in rows, such as the data in rows 8 to 11 in the image above, copying formulas from one row to the next usually works because the cell references in the formula automatically change to match each row number that the formula is copied to.
However, if a formula contains a constant - such as the deduction rate in cell C5 in the image above - copying the formula won't work because we can't allow the cell reference C5 to change when the formula is copied. Our formula must always refer to the deduction rate in cell C5.
A named range then, is an easy way to avoid the problem of changing cell references in formulas that are being copied to other cells. If we assign a range name to cell C5 then the range name is used in the formula rather than the cell reference.
Even when copied to other cells, the named range will always refer to cell C5.