NAMING CELLS AND RANGES, Cells and ranges can be given a name so that when you need to refer to that cell or range of cells, you can refer to its name rather than an address. Example __ Inthe screenshot below, traditional cell addresses have been used for working out January's profit or loss. Using the wadkonal call addreseus to war out pratt orloss Be 8.813 7 a oe. i Tanuany (February 2 Income 3. Nowopapars Bw @ Magarin =] 5 5 Total Income a a Z 3 5. Expenditure 10 Rent maw 11 Wages 3,214 12 13 Total Expendiwe 223714 ia 15 [Total ProftiLows) [—B] 85 In the screenshot below, row 6 has been given the name Total_Income and Row 15 the name Total_Expenditure. When the formula for February was done, the syntax was Total_Income — Total_Expenditure. Using names may make it easier to understand what the formula is doing — it might make more sense to some, than saying B6 - B13. Using range names to work out the profit or lose 7 Fobwary 2 3 |Newsnopers mi 4 Magazines 288 5 5. Total income aio 7 a 3 Expenditwe 40 Rent 2000 71 Wages 124 2 43 [Total Expenditure | 323714 is [48 Total ProftiLoss) sa) 16 ‘SAdministatvelntrmation Techaalogy Customer Sewees Managementeaucaton and Training TeariGuvrent Trang Page 7 Modulos\Advanced ExcaiAavanced Excel formulas and funetone oeThere are a couple of ways to create a range name. They both require that you select the cell or range or before creating the name. Method 1: INSERT, NAME, DEFINE option (or CTRL + F3) 1. Goto the INSERT, NAME, DEFINE dialog box (or press the keyboard shortcut CTRL +F3), The DEFINE NAME dialog box will appear; 2. Give the cell or range you selected a name (quite often, as in the example screenshot below, the name suggested by Excel is more than adequate); 3. Click on ADD, then click on CLOSE NOTES: Range names must be one word — if you require 2 words, join them with an underscore as in the example below. The names can be a combination of numbers and letters, but must not start with a number. The default is to show the address as being ABSOLUTE. By clicking in the REFERS TO text box, this can be edited to what is required, Define Name As a default, names are workbook level i.e. the name you create is available throughout the whole workbook, If the worksheet has been named with a name that includes spaces, the worksheet name will have to be enclosed in single quotation marks. Method 2: Using the NAME box 1. Highlight the cell or range(s) that is going to be named; 2. Click in the NAME box (see the following screenshot). The contents of the name box will be highlighted; 3. Type the RANGE NAME you want and ENTER to accept the name. “SAministatvellnormaton Technology Gustomer Senvoss Mi igemeniEucaton and Talring TeariGurentTraning Page| Modulos\Advanced ExcaiAavanced Excel formulas and funetone oeSee Naming Ses S) oe Git dey et Foe lak ws sw ot fame Deeaa|eRy|: Dea Say x a =n -|m al Bre rae. Des. ia Ireomd fe =SU040 a z Tanary Ci the HABE ba the “ype nae atthe organ it cde wil bs ighlghied ‘onthe heyoan e322 Applying a Range Name in a Formula To use the RANGE NAME in a formula either: 1. Type the name — make sure you use the correct spelling or else the function will not recognise the name; or ‘When you are in the part of the formula that requires the RANGE NAME, press the keyboard shortcut F3. This brings up the PASTE NAME dialog box. Select the name and click on OK, oe Paste nome otal income Paste List When working with many names, you can have a list of these names and the cells or ranges they refer to placed in your workbook by using the & PASTE LIST option (located in the PASTE NAME dialog box - see screenshot above). Make sure you are in an empty cell and have 2 columns free and as many rows as there are names before using this option The first column will ist the RANGE NAMES, the second column shows the range address that the range name refers to. H 1 ‘Total Expenditure =Sheet!1§19:613 Total_incame —_=Sheet 66:95, ‘Sdministatvelnormation Techaalogy Customer Sewees ManagementEaucaton and Training TeariGuvrent Trang Page 9 Modulos\Advanced ExcaiAavanced Excel formulas and funetone oeUSING NAMES FOR CONSTANTS OR FORMULAS In addition to using Names to name cells or ranges, you can also name constants or formulas, these constants or formulas don't have to appear in a cell. This is a good way of storing a constant or a formula that may have to be used several times. ‘An example worksheet called NAMING FORMULAS has been created in the Advanced Excel Exercises workbook, should you wish to try out this feature, Naming a constant Imagine having to work out someone's holiday leave loading. You might have the leave loading in a cell (H6 in the example below), so that you can use this value in a formula. By having the value in a separate cell, if the leave loading rate ever changes you would only have to change the value in H6 and all formulas referring to this cell would update. The formula in this case is =(D5/52)*4*$H$6, i.e. the salary divided by 52 weeks, multiplied by 4 weeks and then multiplied by 17.5%. Be =oseare re 4 Mame Home Towm Airfare Salary _LLeadin [Sisnih, Fed Wsteune | 23000 See SUT {Leave Loading Janes, Tort ra muy swum ‘Amount 17 50% 7 Brown,em ——-Malaeume ‘2300027500 8 Trencon, Helen Adelaide | 40000624 [5 Patched Peter [Adelaide | «ton 45000 {0 wimrar, Valen Dara ru) 37855 11 Horie, Thomae _Perh e000 30m 12 Beers, Sach Hebe sam 7=n 13 Aoe,Deise——Deniqun 7200 0m TE Luck, Mine Syerey zoo 37958 15 Lawrence, anthery Bistane | 50400, sco 1B Lebeck, Chins | Canbora Bu Zen) 7 Seower Nei Bstane 5040038000 1a Crochet Gran Syerey 2m0o, 57555 5B Daniele, ean (Canbora 00 7am By using a name in the formula rather than a cell address, you save space in the ‘spreadsheet and make the formula more readable i.e. what is easier to read? =(D5/52)*4*$HS6; or (D5/52)*4*loading To give the leave loading of 17.5% (which is a constant) a name you: 1. Open the DEFINE NAME dialog box (INSERT, NAME, DEFINE or CTRL + F3) 2. Give the constant a name i.e. Leading 3. Inthe REFERS TO: section enter the amount of the constant (without an "=" sign, Excel will add this in for you). 4. Click on ADD, then CLOSE to close the DEFINE NAME dialog box ‘SvAdministatvelstormation Techaalogy/ Customer Sewees Managementeaucaten and Training TeariGuvrent Trang Page 10 Modulos\Advanced ExcaiAavanced Excel formulas and funetone oe5. When creating the formula, use F3 when it is time to refer to the loading, select the name Loading and ENTER to accept the setting (or click on OK), then continue creating the formula in the normal way. (As mentioned previously, you can type the name of the range but if spelled incorrectly, this formula will not work). Naming a Formula Take the above example one step further - instead of referring to the leave loading to do the last bit of multiplying, why not put the whole part of working out the leave loading into one name? i.e, Take the cell containing the salary and multiply it by loading. The formula would look like: =Loading To name a formula you: 1. Open the DEFINE NAME dialog box (INSERT, NAME, DEFINE or CTRL + F3) 2. Give the formula a name i.e. Loading 3. Inthe REFERS TO: section type an “=" sign to start off the formula open a left bracket “(* ‘+ inthe spreadsheet click on the cell containing the salary (i.e, DS in the example used previously) * NOTE: this cell address is ABSOLUTE (i. it says $D$5). Use the F4 key to make the reference relative i.e. D5. If you don't, when you copy the formula to all the other staff members, each formula will refer to Fred Smith's salary. © type /52)"4"17.5% * the formula should read =(NAMING FORMULAS'ID5/52)*4*17.5% (if you used the exercise provided). If you used a new sheet it will look something like =(Sheet1!D5/52)*4*17.5% 4. Click on ADD, then CLOSE to close the DEFINE NAME dialog box 5. When creating the formula make sure you are in appropriate cell, then either: * use F3 to select the name ‘LOADING’ and ENTER to accept the setting (or click on OK), ENTER again or click on the green ¥ to finish the formula; or ‘+ Type =loading then ENTER or click on the green ~ to accept. ‘Administratvellnormation Technology Customer Sences Managementeducaton and raining TeamiCurent Trang Page 11 Modulos\Advanced ExcaiAavanced Excel formulas and funetone oe