Naming in Excel

1. Naming in Excel

2. Excel Objects and Names • Every Excel object is contained in a workbook • A workbook is divided into worksheets (individual spreadsheets); it can also contain charts, macros (Excel VBA programs) and other objects • Each object has to be identifiable by a name • We’ll start by looking at cells

3. Built-in Names • In Excel, columns are named with letters such as A, B, etc; if you need more than 26 columns then you get names like AA, AB, and so on • Rows are numbered • The default way of referring to a cell is by its column and row, as B12, for example

4. Cell B12 is highlighted

5. Cell Names in Formulas • The most common reason to use cell names is in creating formulas • Cell names can be relative (or partially so) or absolute • Use a relative name when you want copies of the formula to adjust depending on what row or column they’re in • Use an absolute name when you want to always refer to the same cell in every copy of the formula

6. Formula Using a Relative Name

7. Writing a Relative Name • The name A1 is relative: this is the default choice • This formula says to add 3 to A1. Since it is in cell B1, that is where the answer will show up.

8. After pushing the enter key…

9. Copy the formula and paste it down the column

10. Each copy refers to its own row

11. Built-in Functions • Excel comes with lots of very useful functions built in • The next slide shows an example using the function AVERAGE • Note the use of A1:A8 to name the range of cells in column A from position 1 to 8 inclusive

12. A10 is the Average of A1 to A8

13. Subtract the Average from A1: Note the $ used to create an absolute name

14. Pasting down the column: The relative reference changes, but not the absolute

15. Copying a Copy… In the next example, I used a relative formula to add three to each element in A, and store the result in B. I then copied the same formula into column C. Note that it uses the values in B; the idea of the relative reference is to use the column to the left of the current column

16. Formula for Column B

17. Same formula copied to C

18. Fixing just the column • Suppose I want the formula to change to the current row, but keep using column A. • I can do that by just putting a $ in front of the A: instead of $A$1, use $A1

19. Column Absolute, Row Relative

20. Meaningful Names • I can also give a meaningful (user-defined) name to a cell • User-defined names are always absolute • I’ll name the cell A10 as colAvg, then use that name in a formula (note I actually used colAavg due to a typo)

21. Type the name and press enter

22. The formula using the name

23. The Name Manager (Windows) • If you use a lot of names you might forget exactly what some are or what they refer to • If you go to the Formula tab in Excel, and click on the Name Manager, it will show you all your names, with their definitions and current values • You do this a different way on the Mac; we’ll look at that afterwards

24. Name Manager Example

25. Sorting out Names • There is a cell named A1 on every worksheet in the workbook. • Within a worksheet, the name is unique and there is no problem • To refer to cell A1 on another sheet, say Sheet2, use the name of the sheet as in Sheet2!A1

26. An Analogy • You can think of cell names on a worksheet as being like names in a family • In a regular family each person usually has a unique name that everyone uses • But if we consider a class in school, there are likely to be several people with the same given name. In that case the teacher uses family names as well • The sheet name is like the family name: it makes it clear exactly which A1 we mean, as in Sheet1!A1. But within a sheet, like within a family, there is no need for the family name

27. Scope • So the scope of a name is the part of the workbook where the name has a unique meaning and can be used without modification • The scope of a name (like colAvg) that we give to an object is the whole workbook; you do not need to use the sheet name when referring to it on another sheet, and the name can only be defined one time in a workbook

28. Naming a Range • Highlight the cells you want to name • Go to the Formulas tab and find the Define Names panel; click on Define Name • Type the name you want to use in the window that comes up

29. Click on Define Name

30. Put the name here and click OK

31. Use it in a formula

32. New View of Name Manager Notice how the defined names are absolute references

33. Finding Definitions on the Mac • In the Windows version (Excel 2010) there is a Name Manager • Finding definitions in the Mac version (Excel 2011) is a little trickier • We’ll start by defining a couple of named ranges, and then show how to find the names

34. I just named cell A1 “root” Cell A1 is selected, and I typed the name “root” in the name box at the upper left, and hit return

35. Create a list of powers of root… The formula in cell B1 is “=root” The formula in B2 is “=B1*root” This formula is copied down the column to create the list of powers of root

36. Create a named range “powers” I selected the range and typed the name “powers” in the name box

37. To find the list of definitions… • Using the Insert menu, follow the entries Insert -> Name -> Define • (see the next slide)

38. The resulting definition list… I selected “powers” in the list box and it is showing me the definition of powers: (note the use of absolute addresses) =Sheet1!$B$1:$B$11

39. Why use Defined Names? • It can make your spreadsheets much more readable and less error-prone • This is especially true if you use lots of formulas