1 / 78

Worksheets, a Further Look

Worksheets, a Further Look. Excel 2000 - Intermediate. Renaming Worksheets. Double click on the tab you wish to rename Enter the new name. Or right-click on a worksheet tab and use a pop-up menu. Deleting and Inserting Worksheets. You can insert and delete worksheets:

helene
Télécharger la présentation

Worksheets, a Further Look

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. Worksheets, a Further Look Excel 2000 - Intermediate

  2. Renaming Worksheets • Double click on the tab you wish to rename • Enter the new name Or right-click on a worksheet tab and use a pop-up menu

  3. Deleting and Inserting Worksheets • You can insert and delete worksheets: • By clicking on the relevant worksheet tab with the right-hand mouse button and selecting Insert or Delete from the pop-up menu OR • By selecting Worksheet from the Insert menu, or Delete Sheet from the Edit menu

  4. Finding Files • Allows you to easily locate files stored on disk

  5. Displaying Other Toolbars • From the View menu, select Toolbars and pick from the sub-menu displayed • Or right click on any toolbar to see a pop-up list

  6. Customizing the Look of the Toolbars & Menus • Right click on any toolbar • Select Customize • Select the Options tab

  7. Freeze Panes • To enable you to see which row or column you are in when you are working on a large worksheet, you may wish to lock the row or column headings • To do this, use Freeze Panes

  8. Grouping and Ungrouping Worksheets Allows you to edit and format several worksheets at the same time Changes made to the active sheet will be reflected in all sheets in the group

  9. The Series Command • Excel 2000 recognizes the concept of a series • For instance, if you were to enter the word MONDAY, Excel 2000 can automatically extend the series across selected cells to display the rest of the days of the week • The same can be done with dates, times and items such as Q2 or Qtr2 • Different types of series • Linear • Excel 2000 increases or decreases the values by a constant value • Growth • Excel 2000 will multiply the value by a constant factor • AutoFill • Excel 2000 can extend various types of data, such as Qtr1, Qtr2, Qtr3 • AutoFill may be used directly on your worksheet using drag and drop techniques, or may be applied using the Series command located under the Edit/Fill drop down menu

  10. Review Questions

  11. Further Formatting Techniques Excel 2000 - Intermediate

  12. Conditional Formatting • Allows you to highlight data if it meets a specified criteria • You can also find cells that meet a specified criteria

  13. Creating New Styles Based on Existing Cell Formatting • You may define selected, formatted characters as new styles • Use the Style command located under the Format menu

  14. Using Colors in Excel 2000 • Make sure that you know how: • To change the color of your text • To print text in color • To apply colors to cells • To apply a pattern to cells Fill Color Font Color

  15. Using Colors Based on Values • You can format cells so that negative number within those cells are automatically displayed in red

  16. Gridlines Make sure that you know how: To turn off the gridlines on-screen To turn on the gridlines so that they will be printed

  17. Hiding Data and Re-displaying Data • Columns, rows or entire worksheets may be hidden • Hidden items will not print when you print the worksheet TIP: Right click and select Hide

  18. Review Questions

  19. Further Functions Excel 2000 - Intermediate

  20. What are Functions? • Functions are special commands • They can be used to make complex operations simple • Functions extend the powers of Excel 2000

  21. Using Paste Function • The Paste Function is an Excel Wizard that will help you construct your functions Paste Function Icon

  22. The IF( ) Function • This function is used to make a decision based upon a value within the Spreadsheet • IF(Logical_test, Action_if_true, Action_if_false)

  23. Data Validation Functions • Excel 2000 has a number of functions which are mainly used to validate data entry • IFBLANK • IFERR • IFERROR • IFLOGICAL • IFNA • IFNUMBER • IFREF • IFTEXT

  24. The HLOOKUP / VLOOKUP Functions • A means of looking for valuesin predefined tables of figures • Examples • Postage rates • Tax tables • Discount tables

  25. A Sample HLOOKUP

  26. The CHOOSE Function • A means of choosing from a list of up to 29 options • Easier though less versatile than “Lookups”

  27. Entering a CHOOSE Function

  28. The Date Functions • A wide range of functions exist to assist with date manipulation • Examples • DATE() • DAY() • MONTH() • TODAY() • WEEKDAY()

  29. Mathematical Date Functions • Make sure that you know how: • To calculate the difference between two dates in days or weeks • To add days or weeks to a date • To add months to a date • To find out the last day of the month

  30. Time Functions • A range of functions exist to assist with manipulation of Time • Examples • HOUR • MINUTE • NOW • SECOND • TIME • TIMEVALUE

  31. Text Manipulation Functions • A wide range of functions exist to assist with Text manipulation • Some examples • CONCATENATE • LEFT • LEN • LOWER • REPT • RIGHT • TRIM • UPPER

  32. Review Questions

  33. Database and List Management Excel 2000 - Intermediate

  34. What is a Database? • A structured collection of related data about one or more subjects • An address book, a Telephone directory, a Timetable etc.

  35. Excel 2000 Databases • Made up of records and fields in the form of an Excel 2000 table • The top row normally contains the field names • Each row normally contains the details that make up each record

  36. Database Terminology • Record • A collection of Data about an individual item • A Row in Excel 2000 • Field • A single item of data common to all records • A Column in Excel 2000 • Field Name • The top row of an Excel 2000 database naming the fields Jones R. A.The Paddock, Cirencester. Cirencester

  37. Creating, Editing and Navigating a Database

  38. Sorting an Internal Excel 2000 Database

  39. What is AutoFilter?

  40. Filtering Data • Any data in a list can be selectively filtered

  41. Top 10 AutoFilter

  42. Advanced Filtering • Use of the advanced filter allows for: • Multiple selections from the same field • Selections based on calculations • Selections across multiple fields

  43. Totaling and Manipulating Filtered Data • Use SUBTOTAL not SUM • Rows not visible after you have applied AutoFilter are not ‘Hidden’ - they are just not displayed • They are not included in SUBTOTAL functions (but are included in SUM functions) • They are not printed (unlike manually hidden cells) • They are not used to construct new chart information • They remain unchanged if the AutoFill command is used • They remain unchanged if you apply formatting commands • They remain unchanged if you use the Sort, Clear, Copy or Delete Row commands

  44. Creating Subtotals • Allows for easy creation of subtotals within list databases

  45. Using Dataforms • Dataforms allow you to: • enter new information easily • edit existing information • delete existing records • find particular records easily

  46. The Excel 2000 Data Form • The Excel 2000 Data Form allows you to easily view, modify, add, and delete records from a list or database • You can also use the Data Form to display specific records based on a filter criteria

  47. Review Questions

  48. Customizing Charts Excel 2000 - Intermediate

  49. The Chart Wizard (1) - Chart Type • Select or click within the data • Click on the Chart Wizard icon • This will guide you through the chart creation process

  50. The Chart Wizard (2) - Data Source • Allows you to define the data that will be used to create the chart

More Related