5.7 Working with ranges

A range can be defined as a group of contiguous cells. Or any rectangular shape of cells on a worksheet can be called as a range.

When you need to refer to a range in a formula, you can either identify the range by its cell references as you have been doing so far like D7:E7. You could have named the range and then use the name in the Formula.

Naming a range can save your time and efforts since its easier to remember a name than the beginning and the ending cell references for a range. Names also make formulas much easier to read and understand. So lets learn how to name a range.

5.7.1 Creating Names

The process to name is range is quite simple. All you have to do is to select the range and then use the name box on the formula box to define a name. You can also use the Name command on the Insert menu to create names.

5.7.1.1 Rules For Naming The Ranges

Naming a range implies that you provide some logical name to it so that you can refer to the range easily. The length of the name as be as long as you want, provided you don’t use any spaces or commas. However it is recommended that a range name should contain either one word or at the most 2-3 words. In general, use the names that you can easily remember and type.

5.7.1.2 Create range names

1.       Open a worksheet.

2.       select any range say B3:B7. The first cell reference appears in the name box. In our case it must be displaying B3.

3.       Click on the name box on the formula bar and the type RANGE1 and press Enter.

So, now your range is named as Range1.

5.7.1.3 Creating Default Range Names.

The range name you have created above was given by you but in case, if you want to refer these ranges by the headings that are on the top of the data, take up the steps listed below:

1.       Select the range A5:F12

2.       On the Insert Menu, pint to name and in the submenu click on Create…

3.       The Create Names dialog box opens. The options to use the heading in the top row is already selected.

4.       Click on the OK Button.

5.       Click the down arrow next to the name box to view the list of range names. You will see that each column becomes a range becomes a range named with the column title.

You can create range names with range titles by practising the same.

5.7.1.4 Saving Range Names.

The range names are saved as soon as you re-save the worksheet. Just click on the save button to save the worksheet.

5.7.2 Editing names

You can edit range names once you have created them.

1.       On the insert menu, point to Name and then click Define… The define Name Dialog Box opens and all of your names are listed in Names in workbook: List.

2.       Select the name you want to edit by click over it.

3.       In the Names in Workbook: text box, select the existing text which should be displaying the default name of the range. Delete it by pressing DEL key.

4.       Type the new name.

5.       Click on Add Button. Clicking on add button adds the modified name to the list of ranges.

6.       But you will see the old name is still on the list. Remember that when you change the a range name, you must delete the old name.

7.       Select the old name and click the Delete button in the dialog box.

8.       Finally click on the OK button.

5.7.3 Going to ranges

Now that the ranges have been named, you can move around in your worksheet more quickly. Instead of using scroll or arrow keys, select a range, you can use the name box on the formula bar to move to and select a range in one step.

5.7.3.1 Select Ranges Using The Name Box

1.       Click the down arrow next to the name box. A list of named ranges opens for you to select from.

2.       Click on the any of the range name. You will see the range corresponding to the range name gets selected.

3.       Click on another name and another range corresponding to the range name is selected now.

5.7.3.2 Select Ranges Using The F5 Key

Another way to move around in the worksheet is to use the function key F5. This key is also called as Go to  Key. The steps listed below will help you use this key effectively.

1.       Press the function key F5. The Go To dialog box appears, containing a list of all the cell and range names in the workbook.

2.       In the Go To list click on any name and then click OK.

3.       The corresponding range gets selected.

5.7.4 Using Names in Formulas

Excel allows you t use range names in place of cell references in Formulas. This implies that instead of listing the reference for the range you can you respective range name. Using names make your formulas much easier to understand.

5.7.4.1 Finding The Maximum And Minimum Values

You can use the Function Wizard as well as a Formula to find out the Minimum and Maximum values.

1.       Select the cell, where you want to display the maximum number.

2.       Click on the Function Wizard button on the Standard Toolbar. The Function Wizard opens up.

3.       In the Function Category: box select Statistical and then in the Function Name: box, scroll downwards and select MAX. This function finds the maximum value in the selected range.

4.       Click the OK button. The next step in the Function Wizard appears.

5.       Click in the Number1 box.

6.       Type the range name in the Function Wizard dialog box.

7.       Click on the OK button.

8.       The formula is completed and the value appears in the cell where you were when you invoked the Function Wizard.

 So we have found the maximum number among the range. Now lets find out the minimum number.

1.       Select any cell.

2.       In the cell type =min(Rangename). This finds the minimum value in the given range.

3.       Press Enter. The value appears in the selected cell.

Copyright © 2001 Selfonline-Education. All rights reserved.