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.
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.
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
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.
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.
This will show the desired result, i.e, sum of the marks obtained by the students.
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.
We will write a simple formula for evaluating percentage by specifying name range T_Marks.
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.