CS 106 Computing Fundamentals II Chapter 4 “ Excel Basics for Mac ”

1. Herbert G. Mayer, PSU CS status 6/27/2013 Initial content copied verbatim from CS 106 material developed by CS professors: Cynthia Brown & Robert Martin CS 106Computing Fundamentals IIChapter 4“Excel Basics for Mac”

2. Syllabus • Excel Versions • Workbook • Excel Sheets • Controls • Adding Data • Excel Formulas • Absolute and Relative Names • Functions

3. Excel Versions • You will need Excel 2011 on a Mac. Earlier versions will not work for our purposes. • You can use Excel 2010 or 2007 on Windows. They are quite similar.

4. Choose the Workbook…

5. This is a typical new workbook.

6. Workbook made up of Spreadsheets Tabs let you switch between sheets

7. Rename a sheet by double clicking and typing a new name Here I renamed Sheet1 as tab1

8. Adding Sheets Just click here

9. Multiple Sheets • Many times you will only need one sheet • Multiple sheets can help you organize your work • For example, you might want a sheet for each time period, like a week, month, or quarter • Or, if you have multiple stores, you might want one for each store

10. Controls

11. RibbonsPlus Menus • The newest Microsoft Office programs under Mac have ribbons that run across the top of the window; they also have menus • Controls in ribbons are grouped into tabs and within the tabs are grouped into functional categories • On some items, a small downward triangle can be clicked to show options

12. The Home Tab Main ribbon tabs Qi Qi Group names Menus Quick access items

13. Using the Controls • Many controls you use frequently are on the home tab • Check out the other tabs and the menus to get a feeling for what is there • The built-in help is very good • In a separate short presentation, we show you how to make the Developer tab show up

14. Structure of a Spreadsheet

15. A sheet is a grid of cells Column names Row numbers

16. A Cell is named by Column and Row Cell A1 is selected and its name is shown in the name box; you select a cell by clicking it

17. Cell C1

18. Cell B2

19. Cell G17

20. Adding Data

21. Type Directly into Selected Cell

22. Types of Data • Excel can handle various kinds of numbers, strings of characters (text), and dates • Most of the ordinary formatting can be handled by the home tab controls

23. Number Formatting Group Use this group to format numbers as currency, percent, etc. To format a whole column or row, click on the column letter or row number

24. Tapping the Power of Excel • Suppose I want to fill the first column with numbers 1000, 2000, 3000 etc up to 20,000 • I could just type them but that would be silly • Instead, use an Excel formula to accomplish this task • To type a formula you must start with an = sign

25. A Formula: = A1+1000 When writing a formula, you don’t have to type the name of a cell you want to use. Just click on it and it will show up in the formula.

26. Now I push return…

27. Now the good part… • I’m going to copy the formula by selecting the cell it’s in and clicking the copy icon in the clipboard group at the left of the home ribbon • Then I will drag the cursor down the A column from row 3 to 20; Excel will highlight it • Then I will click the paste icon

28. Just before clicking Paste

29. Just after clicking Paste Note that cell A3 has formula A2+1000!

30. Relative Names • So when I copy formula =A1+1000 from cell A2 into cell A3, it becomes A2+1000 • When I copy it to cell A4, it becomes A3+1000, etc. • This is because the A1 in the original formula is a RELATIVE name. It is the cell directly above cell A2, where I am writing the formula • When I copy the formula, Excel replaces A1 with the name of the cell directly above the one where I’m putting the copy

31. Relative Names Illustration Here I put 50 in cell E1 and then copied the formula from cell A2 to cell E2. You can see that Excel changed the formula to use E1 instead of A1, because E1 is directly above E2

32. Absolute Names • Suppose I really want to use a particular cell, and don’t want Excel to change it in the formula when I copy the formula • Let’s say I want to put a number in cell A1, another number in A2, and then add them and put the answer in A3. Then for subsequent rows I want to add A1 each time. So if I start with 10 in A1 and 20 in A2, I should then get 30, 40, 50 etc. • Let’s look at what happens if I go with my first idea: to put =A1+A2 in A3 and then copy it

33. Instead of what I wanted, I got: I wanted the formula for A4 to be =A1+A3, but instead it is =A2+A3 Instead of keeping the A1, Excel used the two preceding cells, A2 and A3

34. $ Makes a Name Absolute • $ fixes the row and/or column of a cell name • So instead of =A1+A2, I’ll use =$A$1+A2 to fix both the row and column • Let’s see what happens…

35. This is what I wanted!

36. If I change Numbers in A1 & A2? Excel automaticallyrecomputes the rest of the column based on the formula! This is one of the great things about using Excel: it lets you try different numbers in “what if” scenarios.

37. Use Similar Formula in other Col? • I can make a formula where the row is fixed and the column is relative, or vice versa • In this case, instead of =$A$1+A2, I’ll use the formula =A$1+A2. So the column is relative, but I am always using the item in row 1. Let’s copy it to column G, put some numbers in row 1 and 2 of that column, and give it a try…

38. Here the Result The formula =A$1+A2 from cell A3 copied over to be =G$1+Gn, where n is the number of the preceding row

39. Actual Names • You can give any cell an actual user-defined name that will be absolute and usable on every sheet in the workbook and in all its formulas • Just click on the cell, type the name in the name box, and hit return • This can make your formulas more readable!

40. Functions • Functions are found in the Formula tab • Excel has many useful functions to use • The are in categories such as Financial, Logical, Text, etc., and there are many math and statistical functions • We’ll be learning to write our own functions, too

41. Using the formulas tab I clicked the small triangle next to fx to get the list of function categories

42. The function TODAY returns today’s date

43. I clicked on the function to insert it in a formula in cell C2

44. Here’s the resulting value. It will update to the current date whenever you open the workbook, so don’t use it if you want it to stay on a particular day.

45. More Complex Functions • “TODAY” is a pretty simple example. It doesn’t take any arguments  • More complex functions can be applied to spreadsheet cells or groups of cells (as in SUM or AVERAGE) • Use them in your formulas when you need them; it saves time over writing your own calculation

46. Here I want to add a list of numbers. So I typed “=SUM(“ and then selected the list of numbers. You can see how Excel has filled in the notation A1:A17, which refers to this list. I’ll finish by typing the closing parenthesis and pushing Return.

47. Here’s the final result.

48. Try It! • If any of this is new to you, open up a spreadsheet and try these ideas for yourself • Experiment with relative and absolute names and with formulas • By now we have only scratched the surface of Excel