Using Named Ranges in Microsoft Excel

Named Ranges in Excel enable you to give one, or a group of cells a name other than the default B4 for a single cell, or B2:E20 for a range of cells. So that you can refer to them in formulas using that name.

You can view the complete tutorial here How to Use Named Ranges in Microsoft Excel .

When using Named Ranges there are also set of rules which you need to know like the scope where you can use the specified named range. If it's within only a single worksheet or the entire workbook.

Check this out Named Range Rules and be totally aware of the do's and don'ts when using this functions.

Managing Your Named Ranges

There'll come a time when you want to edit or delete a Named Range. To do this access the Name Manager on the Formulas tab of the ribbon. The Name Manager Dialog box will open.

From here you can Edit and Delete your Named Ranges, or even create new ones. Remember, once you delete a name you cannot undo that action.


Different uses for Named Ranges in Excel:

1) Formula References: Simplify the creation and retrospective interpretation of formulas by using Named Ranges in your formulas

2) Multiple Print Areas on one worksheet; Selecting non-contiguous print areas is quick and easy using Named Ranges. Note: Excel 2007 onward allows you to set multiple print areas on the Page Layout Tab of the Ribbon under Print Area.

3) Reduce worksheet clutter with Named Constants. Sounds complicated but it's not. A Named Constant is just a fancy name given to values you might use repeatedly in your formulas. For example, let's say you're planning to have a year-end inventory sale and wanted to give a 10% discount on selected products, you could give the value "10%" a name like "Discount" and then you could:

• Write your formulas like this =C1-(C1*Discount). This is more intuitive when you come to review the spreadsheet later, or for anyone else reviewing it.
• Update the value globally by editing it once in the Name Manager. This will then automatically update the formulas.
• Reduce clutter on your spreadsheet. Often a helper cell is set up with this key figure in to achieve these benefits, but if you've got many of these key figures your spreadsheets can become crowded.

To create a Named Constant See--How to Setup a Named Constant in Microsoft Excel.