Microsoft Office Lesson 4

1. Microsoft OfficeLesson 4 Lexington Technology Center October 12, 2004 Bob Herring On the web at http://www.lexington1.net/adulted/computer/office.htm

2. Office Lesson 4 Review of of the Previous Lesson • Worksheets • Menus and Toolbars • Worksheet Cells • How to Enter and Edit Text and Numbers • Simple Formulas • Copying and Pasting Formulas • Formatting, Inserting, and Deleting Rows and Columns • Worksheet Tabs • Copying and Renaming Worksheets • Excel’s Built-In Calculator • Centering Across Columns • Saving an Excel Workbook 2

3. Office Lesson 4 Using the Name Box • Cells and ranges can be named • To got to a named cell, type it in the name box, or, select the dropdown Click on dropdown to select defined names Type a Name and press Enter to go to the Cell 3

4. Office Lesson 4 Using the Name Box • To name, Click Insert on the Menu Bar and select Name, then Define Type the name here Insert Name Define 4

5. Office Lesson 4 Using Names in Formulas • Enter the table shown in the picture below. (Use the Fill Handle to help) • Use a formula to calculate the profit ( =B2-B3 ) • Name cell B3 “Jan”, C3 “Feb”, D3 “Mar”, etc Name the cells in this row Use the names to calculate quarter totals =Jan+Feb+Mar 5

6. Office Lesson 4 Absolute and Relative Cell Addresses • Relative Addressing • Cell addresses in formulas refer to the address of the data • that the formula acts upon • When formulas are extended, Excel changes the addresses • so that the formula refers to the correct address • Absolute Addressing • In this case, new formulas continue to refer to the original • data • The dollar sign ( $ ) indicates an absolute address • Example: = 20 * $A$1 locks the formula to cell A1 6

7. Office Lesson 4 Absolute and Relative Cell Addresses • Absolute addressing can be used for sales tax tables • Changing one cell changes the whole table • To use relative addressing here would mean changing all cells =A4*$C$3 =A4*0.07 7

8. Office Lesson 4 Help for Relative and Absolute References • Select Help,then Contents and Index • Click on the Find Tab and type • difference in the entry box • Select “difference” in the second • box • In the third box, scroll down to • the topic 8

9. Office Lesson 4 Help for Relative and Absolute References • Excel’s internal explanation of the difference 9

10. Office Lesson 4 Page Setup, Page Size and Orientation • Select File, then Page Setup • First Tab is for the page size and orientation • Many spreadsheets are printed as landscape (page sideways) • Use Fit to squeeze data onto the page the way you want 10

11. Office Lesson 4 Page Setup, Margins • Use this Tab to set standard margins -- Or, • Select Print Preview, then Margins to set them by eye Text can also be centered 11

12. Office Lesson 4 Page Setup, Headers and Footers • Use this Tab to format page headers and footers Automatic date Automatic page numbering 12

13. Office Lesson 4 Page Setup, Sheet Options • Use this Tab to print row and column headings and gridlines Row and Column Headings Gridlines Check box 13

14. Office Lesson 4 List Options • Lists can be used to fill in values (e.g., Days and Months) • You can make your own lists to save time and typing • Type in a list and click “Add”, or “Import” to get it from a worksheet Add Collapse Dialog Button Import 14

15. Office Lesson 4 Adding a List • Click in the “List entries” window • Type each entry in the list. Press “Enter” after each entry, OR • Separate entries with commas • Click “Add” to add the list to the Custom lists window Add Type List Here 15

16. Office Lesson 4 Importing a List • Click “Import”, and either type the list of cells to be imported or • click the collapse dialog button • Highlighting cells will copy them into the collapsed dialog • Click the expand dialog button to return to List Options Highlight Cells Expand Dialog Button Collapsed Dialog Box 16

17. Office Lesson 4 Importing a List, Continued • Click “Import” to add the list to the “Custom lists” window List Appears in Window Import 17

18. Office Lesson 4 Correcting Errors • Generally Speaking • Beware of GIGO; or, a computer program is only as good • as its inputs • Be cautious of the results -- know what answer to expect • Errors in Data and Formulas • To fix bad data, click on the cell and retype the number • To fix bad formulas, click the cell containing the formula, then • highlight the error in the formula bar and make corrections • Excel Error Messages • The program can help spot errors • Excel error messages begin with “#” 18

19. Office Lesson 4 Recognizing Errors • Common Error Messages • ##### Cell isn’t wide enough to show the data • #VALUE! Wrong type of data for a function • #DIV/0! Tried to divide by zero • #NAME? Cell name not defined or (usually) misspelled • #REF! Cell reference is not valid • #NUM! Function requires a number • #NULL! Called a non-intersecting range of cells 19

20. Office Lesson 4 Clearing an Entire Worksheet • Select worksheet by clicking the row and column intersection • Select Edit, then Clear • Choose All, or Formats, or Contents, or Comments Place Cursor at Row & Column Intersection 20

21. Office Lesson 4 Clearing Cell Contents • Select cells to be cleared by clicking left mouse button and dragging • Select Edit, then Clear • Choose All, or Formats, or Contents, or Comments Cells to be Cleared 21

22. Office Lesson 4 Design for Looks • Here is some (fictitious) data from an author’s book tour • Unformatted, it is hard to read and understand 22

23. Office Lesson 4 Design for Looks • The same data, with some formatting • Give this a try! 23

24. Office Lesson 4 Cell Formatting -- Number Tab • General -- No specific format; Excel decides how it will look • Number -- Formats numbers; positive, negative, decimal places General Format Number Format 24

25. Office Lesson 4 Cell Formatting -- Number Tab • Currency -- Adds the dollar sign and sets two decimal places • Accounting -- Aligns dollar signs and decimal points Currency Format Accounting Format 25

26. Office Lesson 4 Cell Formatting -- Number Tab • Date -- Gives a choice of date formats • Time -- Allows user to select time formats Date Format Time Format 26

27. Office Lesson 4 Cell Formatting -- Number Tab • Percentage -- Multiplies number by 100 and adds percent sign • Fraction -- converts decimals to fractions of various kinds Percentage Format Fraction Format 27

28. Office Lesson 4 Cell Formatting -- Number Tab • Scientific -- Converts numbers to scientific format • Text -- Treats all inputs, including numbers, as text Note: To enter formulas and symbols as text, precede them with a single quote ‘ Scientific Format Text Format 28

29. Office Lesson 4 Cell Formatting -- Number Tab • Special -- A few commonly-used formats • Custom -- Create your own formats Special Format Custom Format 29

30. Office Lesson 4 Cell Formatting -- Alignment Tab • The Alignment tab sets the location of the text within cells • Text can be vertical as well as horizontal • Use the “Wrap text” check box to keep column headers narrow Wrap text 30

31. Office Lesson 4 Cell Formatting -- Font Tab • The Font tab displays all the font commands in one dialog box • Allows users to make subscripts ( CO2 ) and superscripts ( X2 ) • Underlines can be set for accounting (both single and double) 31

32. Office Lesson 4 Cell Formatting -- Border Tab • The Border tab gives many options for highlighting cells • Be careful using with the “Gridlines” option checked 32

33. Office Lesson 4 Cell Formatting -- Patterns Tab • Shadings and patterns can be added to cells using the Patterns tab 33

34. Office Lesson 4 Cell Formatting -- Protection Tab • Cells can be locked or hidden using the Protection tab • This will not have any effect unless you use the Protect Sheet • option under the Protection section of the Tools menu 34

35. Office Lesson 4 Sorting • Excel can quickly sort your data by column • Select Data, then Sort to bring up the Sort dialog box 35

36. Office Lesson 4 Sorting • Excel allows the user to sort on up to three columns, with the option • to sort ascending and descending • The “Options” button lets you sort by days or months (or any list) and • to sort lower case and upper case and left to right instead of top to • bottom Sort order for “Case sensitive”: Symbols, Numbers, Lower, Upper 36

37. Office Lesson 4 Freezing Panes • Column and row headers can be immobilized on the screen • Click in the cell whose upper left corner will be the intersection • of the frozen area • Select Window, and then Freeze Panes 37

38. Office Lesson 4 Panes Frozen • Excel displays vertical and horizontal lines to mark the boundary • of the frozen area Horizontal Pane Marker Vertical Pane Marker 38

39. Office Lesson 4 Review • Using the Name Box • Absolute and Relative Cell Addresses • Page Setup • Custom Lists • Correcting Errors • Design for Looks • Cell Formatting • Sorting • Freezing Panes 39