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.

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.

namemanager Excel 2010: Name Range

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

قالب وردپرس

About Alyse Kalish

Alyse Kalish
As an Associate Editor for The SalesJobInfo, Alyse is proud to prove that yes, English majors can change the world. She calls many places home, including Illinois where she grew up and the small town of Hamilton where she attended Colgate University, but she was born to be a New Yorker. In addition to being an avid writer, Alyse loves to dance, both professionally and while waiting for the subway.

Leave a Reply

Your email address will not be published. Required fields are marked *