Using Named Ranges in Microsoft Excel

Generally you use a cell or a cell range to perform calculations in Excel. So if you had a value of an employee salary in cell 'a2', where 'a' defines the column and '2' defines the row, and you had another salary amount in cell 'a3' and so on, you would calculate the sum of the salaries as '=sum (a1:a20) in cell 'a21' where all the cells from'a1' to 'a20' are filled with data of employees salaries. Is there an easier or more intuitive way of doing this? Yes! You could assign the salary data in cells 'a1' to cell 'a20' by selecting all those cells and giving a name in the 'name box', for example, as 'salary'. The 'name' box is the text box next to the formula box (fx) and has a drop down arrow next to it. Another way to assign a name is to select the relevant cells, click on insert and then click on 'Name' and finally 'define...' and you will see a new window pop up. Here you can define the name 'salary' in the text box under 'Names in Workbook'. Below you could also observe in the text box under the heading 'Refers to:' the exact cells to which this name 'salary' is assigned. Also you will notice that you can add more names to other cell ranges. In this window there is an option to delete names you don't wish to use any more.

Now how do you use the name or names to do calculations? Well, you could write in any appropriate cell of your choice '=sum(salary) and the total of the salaries in the referred cells would be displayed. Another interesting example with more details: Let's say you have 3 sheets in your workbook. Right click the first sheet (Sheet1) and from the menu select 'rename' and give the sheet the name 'Income'. Again right-click on the first sheet, select 'Tab Color...' from the menu and assign a color, let's say, blue to the worksheet tab. Do similar actions on worksheet 2 and 3 and rename to 'expenses' and 'profit/loss' respectively. Also assign tab-colors to the worksheets. Now assume that we enter 'income' every month.

So you would have 12 entries for 'income' in 12 different cells , say, 'a2' to 'a13' with cell 'a1' having the header 'INCOME'. Similarly, let's assume we enter the expenses every month in Sheet2 in cells 'a2' to 'a13' with 'EXPENSES' as the header in 'a1'. Note: You can, of course, choose any cells for your data. Now let's name cells ranges a1 to a13 in both worksheets 1 and 2 as 'Income' and 'Expenses' respectively. In the worksheet 3 which was was renamed to 'Profit/Loss' we can now enter a formula '=sum(Income)-sum(Expenses)' in any cell of our choice to calculate the profit or loss! Remember you can make your expense and income sheets as detailed as you like. As long as the cells containing the data are given a name you can perform the profit and loss calculations as usual using the 'named' ranges with an appropriate formula.

Hot Game !!!