Excel 2010: Name Range

Writing formulas are easy in Excel. Sometimes, you need to deal with big datasheets and complex formulas, it could create a chaos, especially when accessing data by specifying its cell location. Excel 2010 built-in feature Name Range will help you in this regard. By using it, you can specify the name of the datasheet portion and then use it in the formula more easily and without hassling of remembering cell locations.

Launch Excel 2010, and open a datasheet in which you want to apply Name Range.

For Example: We have a datasheet that contains student record, now we want to calculate the total marks obtained by all the students, percentage and class standing.

new name range

In spite of calculating each cell’s value by its location in Marks column, we will give it a special name for our convenience.

Go to File menu, click Options, and in left pane click Customize Ribbon, In right pane select All Commands from Choose commands from drop-down options and look for Name Manager command. Now at left pane, select Insert and hit New Group, new group will be added to Insert tab, change its name to Name Manager, and then click Add >> button to add Name Manger command to newly created group. Click OK to close Excel Options dialog.

name manager

Now Select the Marks column to specify a special name to it. Click Name Manager, a dialog will appear, click New button in Name Manager dialog to add a name range.

name manager new 1

You will reach a new dialog, it ask you to specify a name, choose a Scope of the name range (you can also select specific worksheet from here), and Comments to help other to understand the rationale behind choosing this name. You can see in the screenshot below, that Name is Marks and it refers to the selected portion of datasheet. Click OK to continue

new name

It will bring you back to the Name Manager dialog, from here you can make a new name range, edit the existing one, and delete the specific name range by clicking respective buttons. Marks is added in to Name Manager as shown in the screenshot. Click close to return.


name manager added

Now write a simple formula in cell adjacent to Total Marks. We will write Marks (name range selected earlier) instead of specifying which cells to add, hit Enter to view the result.

name range marks

This will show the desired result, i.e, sum of the marks obtained by the students.

total marks

By following the same process mentioned above, we will specify a name range for the Total Marks and Percentage.

We specify T_Marks for Total Marks and percentage for Percentage cell as shown in the screenshot below.

final name range

We will write a simple formula for evaluating percentage by specifying name range T_Marks.

percentage tl marks 1

For Class Standing we will write a formula that says, if percentage is greater than 70, then Class Standing would be in A category otherwise B.

final 1 1

Advertisement
  • Pingback: Excel 2010: Using Non-Contiguous Name Ranges

  • Pingback: Excel 2010: Merge Spreadsheets Using Consolidate Data

  • amy

    hello, i need a little help..

    ive setup for my data validation and drop down list. its working..but the problems is now when i open the same file from a different pc in a network, the reference from the name manager suddenly changed and the value becom #REF! .it cause my drop down does not appear..

    Additional Info,
    1.the worksheet contains pivot table
    2.and the list/source of drop down is in different folder..

    anyone can help me..pls

    • Meg

      the pivot table is fine. the source of the dropdown list needs to be on the same folder.

  • Llegge

    Thank you SO much for this acticle – you have no idea how long I’ve been trying to fix my sheets! Cheers!

  • alex

    Thank you so much!!!