Page 240 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

In this data set, if you have to refer to the range that has the Date, you will have to use A2:A11 in formulas.
Similarly, for Sales Rep and Sales, you will have to use B2:B11 and C2:C11.
While it’s alright when you only have a couple of data points, in case you have huge complex data sets, using cell
references to refer to data could be time-consuming.
Excel Named Ranges makes it easy to refer to data sets in Excel.
You can create a named range in Excel for each data category, and then use that name instead of the cell
references. For example, dates can be named ‘Date’, Sales Rep data can be named ‘SalesRep’ and sales data
can be named ‘Sales’.

You can also create a name for a single cell. For example, if you have the sales commission percentage in a cell,
you can name that cell as ‘Commission’.

Benefits of Creating Named Ranges in Excel
Here are the benefits of using named ranges in Excel.
Use Names instead of Cell References
When you create Named Ranges in Excel, you can use these names instead of the cell references.
For example, you can use =SUM(SALES) instead of =SUM(C2:C11) for the above data set.

Have a look at ṭhe formulas listed below. Instead of using cell references, I have used the Named Ranges.
• Number of sales with value more than 500: =COUNTIF(Sales,”>500″)
• Sum of all the sales done by Tom: =SUMIF(SalesRep,”Tom”,Sales)
• Commission earned by Joe (sales by Joe multiplied by commission percentage):
=SUMIF(SalesRep,”Joe”,Sales)*Commission

You would agree that these formulas are easy to create and easy to understand (especially when you share it with
someone else or revisit it yourself.
No Need to Go Back to the Dataset to Select Cells

Another significant benefit of using Named Ranges in Excel is that you don’t need to go back and select the cell
ranges.
Named Ranges Make Formulas Dynamic
By using Named Ranges in Excel, you can make Excel formulas dynamic.

For example, in the case of sales commission, instead of using the value 2.5%, you can use the Named Range.
Now, if your company later decides to increase the commission to 3%, you can simply update the Named Range,
and all the calculations would automatically update to reflect the new commission.

227

CITS : IT&ITES - Computer Software Application - Lesson 63 - 77