1 / 30

Microsoft Excel Lesson 6

Microsoft Excel Lesson 6. Lexington Technology Center March 21, 2002 Bob Herring On the web at http://www.lexington1.net/adulted/computer/microsoft_excel.htm. Excel Lesson 6. Review of Thursday’s Lesson. Linking worksheets Conditional formatting Sorting Filtering Pivot tables

lidia
Download Presentation

Microsoft Excel Lesson 6

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft ExcelLesson 6 Lexington Technology Center March 21, 2002 Bob Herring On the web at http://www.lexington1.net/adulted/computer/microsoft_excel.htm

  2. Excel Lesson 6 Review of Thursday’s Lesson • Linking worksheets • Conditional formatting • Sorting • Filtering • Pivot tables • Freezing panes 2

  3. Excel Lesson 6 Comments • When additional information is necessary in a worksheet, a • comment can be inserted • Select Insert, then chooseComment 3

  4. Excel Lesson 6 Deleting Comments • If you only want to delete comments, select Edit, then Clear, • and choose Comments 4

  5. Excel Lesson 6 Page Break Preview • When you are concerned about which columns and rows get • printed on which pages, use Page Break Preview • Select Edit, then Page Break Preview 5

  6. Excel Lesson 6 Page Break Preview • This is the Preview. It shows how Excel would break the pages • To change it, click on the break lines with the mouse and drag • them to the desired location 6

  7. Excel Lesson 6 Worksheet Protection • If you need to ensure that data in your worksheet cannot be • changed without permission, you can “protect” it • Select Tools, then Protection, and choose Protect Sheet 7

  8. Excel Lesson 6 Worksheet Protection • The “Protect Sheet” dialog appears • The default is to protect all contents, but different levels can be selected • A password may be added for further protection 8

  9. Excel Lesson 6 Data Validation • To help users know what to enter in the worksheet (and to prevent • errors), you can attach data validation rules to cells in the worksheet • Select Data, then Validation 9

  10. Excel Lesson 6 Data Validation • The “Data Validation” dialog appears • Set up the validation rule under the “Settings” tab • Click on the down arrow in the box that says “Any value” and pick • “Whole number” 10

  11. Excel Lesson 6 Data Validation, Continued • Create an message for the user under the “Input Message” tab • Type a title and a message that tells the user what to do 11

  12. Excel Lesson 6 Data Validation, Continued • Create another message to warn of errors under the “Error Alert” tab • Pick a graphic from the combo box on the left, then type a title and • an error message 12

  13. Excel Lesson 6 Data Validation, Continued • Data validation in action • The input message appears, and when we try to enter an incorrect • value, Excel tells us the acceptable range 13

  14. Excel Lesson 6 Tables • Tables are a quick way to calculate a range of inputs to a formula • For example, if you were shopping for a mortgage, you might want • to calculate what the payment will be for various interest rates, what • the total interest cost will be, and what payments would be for • different repayment periods • On a blank worksheet, enter the following: Start in cell C2 D5 D6 D7 14

  15. Excel Lesson 6 Tables • In the cell beneath “Payments,” enter the following formula: D5 D6 D7 =PMT(D5/12,D6,-D7) 15

  16. Excel Lesson 6 Tables • Highlight the cells outlined in red: Highlight here 16

  17. Excel Lesson 6 Tables • Select Data, then Table • In the Table dialog box, enter “D5” in the “Column input cell:” box D5 Data is in a column 17

  18. Excel Lesson 6 Tables Table is filled in 18

  19. Excel Lesson 6 Tables • Tables can be calculated using two variables • In this example, the table will calculate payments based on both • the interest rate and the period of the loan • In the marked cell, enter this formula: =ABS(PMT(D5/12,D6,D7)) 19

  20. Excel Lesson 6 Tables • Highlight the cells from the original formula through the full • range of the table. (In the example, B11 through F16) • Type “D6” in the “Row input cell:” box, as shown • The row input cell refers to the payment periods (360 months) • Type “D5” in the “column input cell:” box, as shown • The column input cell refers to the interest rates (10%) 20

  21. Excel Lesson 6 Templates • A template is just a worksheet that has a format that you would • like to use again • To create templates, select File, then Save As • In the “File name:” box, type an appropriate name • In the “Save as type:” box, choose “Template (*.xlt)” • Be sure to clear the data before saving the final template 21

  22. Excel Lesson 6 Text to Columns • Excel has a way to break text data into columns on your worksheet • Open Word, and open the file “Text to Columns.doc” • Highlight the data and click “Copy” • Change to Excel, click in cell A2, and then click “Paste” 22

  23. Excel Lesson 6 Text to Columns • Select Data, then Text to Columns 23

  24. Excel Lesson 6 Text to Columns • The “Convert Text to Columns Wizard appears • Since our data is not in fixed width columns, choose the “Delimited” • radio button (the default) • Click the “Next” button 24

  25. Excel Lesson 6 Text to Columns • In Step 2 of the Wizard, pick the separators (delimiters) in your data • In this example, the data is separated by commas • Click the Comma check box and Excel shows you this result • Click on the “Next” button 25

  26. Excel Lesson 6 Text to Columns • In Step 3 of the Wizard, you choose the data format for the columns • In the example, General is appropriate for the data we have • Click the “Finish” button 26

  27. Excel Lesson 6 Text to Columns • This is the final result • Double click in between columns C and D and D and E to expand • columns C and D to show all the data • Now highlight column E and separate the state and the zip code 27

  28. Excel Lesson 6 Course Review • Lesson 1 • Starting and Quitting Excel • Worksheets • Menus and Toolbars • Worksheet Cells • How to Enter and Edit Text • and Numbers • Saving an Excel Workbook • Lesson 2 • Opening a workbook • Basic formulas using operators (+, -, *, /, ^) • Using the Fill Handle • Copy and Paste formulas • Change Font sizes, weights, appearance • Using AutoFormat • Center across columns • Using the Name Box 28

  29. Excel Lesson 6 Course Review • Lesson 3 • Copying cells using relative • and absolute cell addresses • Functions • Printing • Page Setup • Excel Options • Correcting Errors • Clearing Cells or Worksheets • Lesson 4 • Creating Charts • Custom Formats • Design for Looks • Office Assistant and Help 29

  30. Excel Lesson 6 Course Review • Lesson 5 • Linking worksheets • Conditional formatting • Sorting • Filtering • Pivot tables • Freezing panes • Lesson 6 • Comments • Page Break Preview • Worksheet Protection • Data Validation • Tables • Templates • Text to Columns 30

More Related