Microsoft Excel Lesson 3

1. Microsoft ExcelLesson 3 Lexington Technology Center April 2007 Bob Herring

2. Excel Lesson 3 Review of Thursday’s Lesson • Opening a workbook • Basic formulas using operators (+, -, *, /, ^) • Using the Fill Handle • Copy and Paste formulas • Change Font sizes, weights, appearance • Formatting Rows and Columns • Inserting and Deleting Rows and Columns • Renaming and Copying Worksheets • Built-in Calculator • Center across columns • Using AutoFormat • Using the Name Box • Adding / Deleting Tools on the Toolbar 2

3. Excel Lesson 3 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 3

4. Excel Lesson 3 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 4

5. Excel Lesson 3 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 5

6. Excel Lesson 3 Help for Relative and Absolute References • Excel’s internal explanation of the difference 6

7. Excel Lesson 3 Printing Worksheets • Select File, then Print • Always a good idea to select Print Preview Select Printer Print Selection Number of Copies Print Preview 7

8. Excel Lesson 3 Printing a Worksheet • Click the Print Tool to print the active sheet • This sometimes leads to unexpected results; Print Preview can help! Print Tool Print Preview Tool 8

9. Excel Lesson 3 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 9

10. Excel Lesson 3 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 10

11. Excel Lesson 3 Page Setup, Headers and Footers • Use this Tab to format page headers and footers Automatic date Automatic page numbering 11

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

13. Excel Lesson 3 General Options • Select Tools, then Options Set the number of blank worksheets to start with Manage “Recently used file list” Set Standard Font Set File Location Set User Name 13

14. Excel Lesson 3 Calculation Options • Excel defaults to automatic recalculation every time a value is changed • To recalculate all at once, select Manual and use the Calc buttons 14

15. Excel Lesson 3 View Options • Customize the look of the Excel Window 15

16. Excel Lesson 3 Edit Options • Choose an editing style that fits your habits 16

17. Excel Lesson 3 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 • Click “Add” to type in a list, or “Import” to get it from a worksheet Add Collapse Dialog Button Import 17

18. Excel Lesson 3 Adding a List • Click “Add” or click in the “List entries” window • Type each entry in the list. Press “Enter” after each entry • Click “Add” to add the list to the Custom lists window Add Type List Here 18

19. Excel Lesson 3 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 19

20. Excel Lesson 3 List Options • Click “Import” to add the list to the “Custom lists” window List Appears in Window Import 20

21. Excel Lesson 3 Color Options • Use Excel’s built-in palette of colors ... • Or choose your own 21

22. Excel Lesson 3 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 “#” 22

23. Excel Lesson 3 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 23

24. Excel Lesson 3 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 24

25. Excel Lesson 3 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 25

26. Excel Lesson 3 Review • Relative and Absolute Cell Addresses • Printing • Page Setup • Excel Options • Correcting Errors • Clearing Cells or Worksheets 26