1 / 73

Worksheets, a Further Look

Worksheets, a Further Look. Excel 5.0 - Intermediate. Renaming Worksheets. Double click on the tab you wish to rename Enter the new name in the Rename Sheet dialog box. Or right-click on a worksheet tab and use a pop-up menu. Deleting and Inserting Worksheets. Pop-up menu.

gisela
Download Presentation

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 5.0 - Intermediate

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

  3. Deleting and Inserting Worksheets Pop-up menu • 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 OR Drop-down menu

  4. Finding Files • Allows you to easily locate files stored on disk You can save the search criteria you entered by clicking on the Save Search As button and entering a name for the search. Next time you wish to use it, select the name from the Saved Searches drop-down list box

  5. Displaying Other Toolbars • From the View menu, select Toolbars to display the Toolbars dialog box • Additional toolbar options include: • Auditing Chart • Drawing Formatting • Forms Full Screen • Query and Pivot Standard • Stop Recording TipWizard • Visual Basic Workgroup

  6. Managing ‘Child Windows’ Within Excel • Child windows are sub-windows contained within the main Excel ‘parent’ window • Each workbook is displayed as a child window • Make sure that you know how: • To enlarge or reduce windows • To move windows in Excel • To close windows in Excel • To cascade windows in Excel • To tile windows in Excel • To arrange windows horizontally or vertically • To select a window display mode • To switch between windows • To switch between applications

  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, you must freeze the panes

  8. The Series Command • Excel recognizes the concept of a series • For instance, if you were to enter the word MONDAY, Excel 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 increases or decreases the values by a constant value • Growth • Excel will multiply the value by a constant factor • AutoFill • Excel 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

  9. Review Questions

  10. Formatting, Further Techniques Excel 5.0 - Intermediate

  11. Using Cell Formats as Styles • You may define selected, formatted characters as new styles • Use the Style command located under the Format menus

  12. Using Colors in Excel • 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

  13. Using Colors Based on Values • Make sure that you know how: • To change colors based on the value in the cells • To change colors in the color palette • To copy custom color palettes between worksheets

  14. Gridlines Make sure that you know how: To turn off the gridlines on-screen and when printing To turn off the gridlines so that they will not print

  15. Hiding Data and Re-displaying Data • Columns, rows or entire worksheets may be hidden • Hidden items will not print when you print the worksheet

  16. Hiding Other Screen Elements • Other screen elements that may be hidden include: • The Formula bar • The Status bar • Toolbars • The Formula bar • The Note Indicator

  17. Review Questions

  18. Further Functions Excel 5.0 - Intermediate

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

  20. 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)

  21. Data Validation Functions • Excel has a number of functions which are mainly used to validate data entry. • ISBLANK • ISERR • ISERROR • ISLOGICAL • ISNA • ISNUMBER • ISREF • ISTEXT

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

  23. A Sample HLOOKUP

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

  25. Entering a CHOOSE Function

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

  27. Date Math • 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

  28. The Time Functions • A range of Functions Exist to assist with manipulation of Time • Examples • HOUR • MINUTE • NOW • SECOND • TIME • TIMEVALUE

  29. Functions for Text Manipulation • A wide range of Functions Exist to assist with Text manipulation. • Some Examples • CONCATENATE • LEFT • LEN • LOWER • REPT • RIGHT • TRIM • UPPER

  30. Calculating Worksheets • Each time formulas are affected by changes to information, Excel normally calculates the worksheet • However, there are certain functions, such as certain time and date functions, which require you to carry out the calculation manually • The automatic recalculation can also be turned off whilst you work F9 will force Excel to recalculate

  31. Review Questions

  32. Database and List Management Excel 5.0 - Intermediate

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

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

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

  36. Creating, Editing and Navigating a Database

  37. Sorting an Excel Database

  38. AutoFilter

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

  40. 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.

  41. Creating Sub-totals • Allows for easy creation of sub-totals within list databases

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

  43. The Excel Data Form

  44. Review Questions

  45. Manipulating and Customizing Charts Excel 5 - Intermediate

  46. Manipulating Charts • Make sure that you know how: • To add data to a chart • To remove data from a chart • To change underlying chart data • To move elements within the chart window • To draw inside a chart window

  47. Formatting Charts • You Can: • format the chart frame • change the order of the data series • control the space between the data series • make each plot point a different series color • create a combination chart • assign a data series to different Y-Axes

  48. Formatting 3-D Charts

  49. Customizing Chart Series • Each chart series appears in a different color or pattern, and is comprised of a set of data points • You can change the series name, the patterns used to represent them, and the legend • Select Format Selected Data Series Or • press CTRL+1 • To display the Format Data Series dialog box

  50. Customizing the Plot Area, Walls, and Chart Area • In a 2-D chart, the plot area is the area bounded by the axes • In a 3-D chart the plot area includes category names, axes titles, and tick-mark labels; they also have walls and a floor • The chart area comprises the entire chart, including legends • Know how to change the colors and patterns of the plot area • Know how to change the color and pattern of 3-D chart walls • Know how to change the color and pattern of the chart area • Know how to change the charts default font

More Related