Naming Ranges in Formulas Excel 2010

PICTURES HAVE NOT BEEN ADDED, I have sent an e-mail to Vazi with photos.

Excel provides the option to assign a name to a cell or range of cells. When using a descriptive name for a cell or range of cells in Excel 2010, it make the formulas easier to understand and remember; it is easier to remember a Range Name rather than a cell reference in a formula.

A Named Range gives you the opportunity to identify cells with a word rather than a cell reference. For example, if you select a range of cell (such as B2 to B4) and assign a descriptive name such as Monthly_Sales, the cell name can be used in the formula such as =SUM(Monthly_Sales), rather than =SUM(B2:B4). The cell references or formulas are hidden behind the Monthly_Sales range name.

Creating a Named Range

To create a Name Range, highlight cells B2 to B4, click on Formula tab, click on Define Name option, Click OK. You will see that the Name in the dialogue box is the heading Monthly Sales.

After you clicked OK, you will be brought back to your spreadsheet and you will notice that nothing has changed. Although, there are no changes in the appearance of the spreadsheet, you have already defined a name and can now use it.

Applying a Named Range

Click on cell B5, Click Define Name>Apply Names, Click the name which you desire then press OK. When you clicked OK, Excel took off the cell references and changed them to the names which choose (Monthly Sales). E.G. The formula for cell B5 now reads: =SUM(Monthly_Sales). The cell references cannot be seen, but Excel has saved the formula. Repeat the process with column C and cell C5, your spreadsheet will look like this:

How to Use Name Ranges in Formulas:

It is now possible to use the two Named Ranges to subtract Commissions from your Monthly Sales. Click and highlight cell B5, Click on the Formula Menu and then click on the Define Name>Define Name

- Write ‘Monthly_Results’ in the ‘Name’ box and press OK

- The Named Range will appear like this in the name box:

- Do the same for cell D5, but name the range ‘Commissions_Results’

- You now have new Names defined for Monthly Sales and Monthly Commissions and are able to use them in formulas

- In cell A7 add a new label called: ‘Net Sales’

- Click on cell B7 and enter the formula =B5-C5 (this means Net Sales= Monthly Sales – Monthly Commissions)

- With the formula now in cell B7, you are ready to apply the new Name cells which you have created

- Click on B7 and click Define Name>Apply Name in the Formula tab

- When the Apply Names dialogue box appears, click on Monthly_Results and Commissions_Results, click OK

- Excel has now changed your cell references and instead of the formula ‘=B5-C5 ’ in the formula bar; ‘Monthly_Results-Monthly_Commissions’ will appear:

How to Edit a Named Range:

- After creating Name Ranges in Excel, you are able to manage and edit them through ‘Name Manager’ options, this is located in the Formulas Tab:

- The ‘Name Manager’ has a list of all the names you defined in your current spreadsheet:

- The ‘Name Manger’ allows you to delete, modify, edit or filter the Named Ranges you have previously created. Additionally, you can produce new name ranges.

- To Add a New Named Range, click where you wish to create a Named Range on your spreadsheet E.G. cell C7

- Next, open the ‘Name Manager’ box (located in the Formula tab) and click on the ‘New’ button:

- Now enter the text you desire into the name box and your Named Range has been created

- It is also possible to use the ‘Collapse Button’ to add cell location, the ‘New Name’ box will be moved so you can view the spreadsheet, use the mouse to choose the cell location and press enter:

Collapse Button’

- To delete a Named Range, highlight the existing Range which you wish to delete and press the delete button

- A confirmation button will appear, select yes if you are sure:

- To edit a Named Range, click on the existing Named Range you wish you wish to edit and press the ‘Edit’ button:

- An box will appear, click OK

Post Tagged with , ,