1 / 93

Excel 2003 Specialist

Excel 2003 Specialist. Lesson 1: Creating and Modifying Workbooks. Objectives. Enter data Edit and delete data Fill cells Navigate worksheets Find and replace data Copy worksheet data Move worksheet data. Overview of Workbooks and Worksheets.

iden
Télécharger la présentation

Excel 2003 Specialist

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. Excel 2003 Specialist

  2. Lesson 1:Creating and Modifying Workbooks

  3. Objectives • Enter data • Edit and delete data • Fill cells • Navigate worksheets • Find and replace data • Copy worksheet data • Move worksheet data

  4. Overview of Workbooks and Worksheets • Workbook – the electronic spreadsheet file in which data is stored • Worksheet – the area of a workbook in which data is entered and manipulated • Cell – the intersection of each column and row • Active cell – the cell that is affected by the action you perform • Cell pointer – the thick rectangular border that encloses the active cell

  5. Overview of Workbooks and Worksheets (cont’d) Cell reference Active cell Cell pointer

  6. Entering Data • Text – data that will not be used in calculations • Value – data that contains only numbers or mathematical operators; values are used in calculations • AutoComplete – completes a text entry based on existing entries in the column containing the active cell • Date: • Two-digit year under 30 is in the 21st century • Two-digit year 30 or higher is in the 20th century

  7. Editing and Deleting Data Data can be edited by: • Completely replacing existing data with new data • Using Edit mode to modify existing data To activate Edit mode: • double-click the cell • select a cell and click in the Formula bar • select a cell and press F2 To delete data: • Select the data you want to erase and press DELETE

  8. Filling Cells • AutoFill – drag a cell’s fill handle to adjacent cells to copy the contents of the cell • AutoFill is useful for copying data or for completing text or number series, such as: • Days of the week • Months • Fiscal quarters • Dates Fill handle

  9. Action Result Click a cell Selects the cell Press the left, right, up or down arrow keys Moves the cell pointer one cell in the indicated direction Press ENTER or TAB Moves the cell pointer down one cell or right one cell, respectively Click below the scroll box in the vertical scroll bar Scrolls the worksheet down one screen Click to the right of the scroll box in the horizontal scroll bar Scrolls the worksheet to the right one screen Press CTRL+HOME Moves the cell pointer to cell A1 Press F5 Displays the Go To dialog box, which you use to move to specific cells Navigating Worksheets

  10. Finding and Replacing Data To find and replace data: • Display the Find And Replace dialog box In the Find And Replace dialog box, you can specify: • the text for which to search • the text with which to replace the search text • to replace the current or all instances of the search text with the replacement text • to find only the next or all instances of the search text • advanced search options, such as matching case and searching for formatting attributes

  11. The Research task pane provides reference information you can use to conduct research about topics you specify Finding and Replacing Data (cont’d)

  12. Copying Worksheet Data To copy data: • Use the Office Clipboard – copy data to the Office Clipboard, then click the item in the Clipboard gallery to paste it to a new location • Use AutoFill – drag the fill handle to adjacent cells • Use drag-and-drop – press and hold CTRL, drag the selected cell or range from one location and drop it into another

  13. Moving Worksheet Data To move data: • Use the Office Clipboard – cut data to the Office Clipboard, then paste it to a new location • Use drag-and-drop – drag the selected cell or range from one location and drop it into another

  14. Lesson 2:Manipulating Worksheet Data

  15. Objectives • Create formulas • Edit formulas • Use cell references • Use functions • Use the Function Wizard • Filter data • Sort data

  16. Creating Formulas • Formula – a cell entry that performs a calculation • Formula entries begin with an equal sign ( = ) • Operator – a symbol used in formulas to perform mathematical calculations: • Addition ( + ) • Subtraction ( - ) • Multiplication ( * ) • Division ( / ) • Exponentiation ( ^ ) • Range Finder – identifies cell references in a formula by highlighting them in different colors

  17. Editing Formulas To edit formulas: • Type over (replace) the existing formula • Activate Edit mode, highlight the cell reference you want to change, then click the correct cell

  18. Using Cell References • Relative cell reference – specifies the location of a cell relative to the cell containing the formula • Absolute cell reference – specifies the exact location of a cell without regard to the location of the cell containing the formula • Mixed cell reference – contains both relative and absolute cell reference components

  19. Using Functions • Function – a predefined formula that performs complex or advanced calculations • Function name – a word or abbreviation used to identify a function • Argument – the data required in a function to produce a value • Function Argument tool tip – a pop-up box you can use to access help information about function components

  20. AutoSum • AutoSum – automatically sums the values in columns or rows The AutoSum drop-down menu includes: • SUM (default) • AVERAGE • COUNT • MAX • MIN

  21. AutoSum Functions • AVERAGE – calculates the average of values in a selected range of cells • MAX – calculates the maximum value in a selected range of cells • MIN – calculates the minimum value in a selected range of cells

  22. AutoCalculate • AutoCalculate – displays the average, count, maximum, minimum or sum of the data in a selected range (contiguous or noncontiguous) without entering a formula or function The AutoCalculate result is temporary and displays in the status bar only

  23. Using the Function Wizard • Function Wizard - helps you determine which function to use or assists you with providing the necessary data for the arguments needed to complete the function To activate the Function Wizard: • click the Insert Function button in the Formula bar • display the AutoSum drop-down menu and click More Functions • after beginning a formula, display the Name drop-down list in the Formula bar and click More Functions

  24. The NOW Function • NOW function – returns a serial number that represents the current date and time • The NOW function contains no function arguments • The NOW function can be used in calculations

  25. The DATE Function • DATE function – returns the serial number that represents a particular date • Function arguments: • Year – a one to four digit number representing 1900 to 9999 • Month – a number from 1 to 12 • Day – a number from 1 to 31 • The DATE function can be used in calculations

  26. The IF Function • IF function – evaluates a condition and returns a value based on the true/false status of the condition • Function arguments: • Logical_test – a mathematical expression • Value_if_true – the result if the Logical_test is true • Value_if_false – the result if the Logical_test is false

  27. The PMT Function • PMT function – calculates the periodic payments of a loan based on constant payments and a constant interest rate • Function arguments: • Rate – the periodic interest rate of the loan • Nper – the number of payment periods • Pv – the present value of the loan • Fv – the future value of the loan • Type – the payment type (1 = payment at beginning of period; 0 = payment at end of period)

  28. Filtering Data • Filter – displays only worksheet data that meet a set of criteria • List – a series of rows containing related data. A list contains no blank rows and the first row has columns labels (the header row) • AutoFilter – filters worksheet data based on specified criteria • Criteria – a set of search conditions to which data is compared

  29. AutoFilter AutoFilter options: • Sort Ascending – sorts the data in ascending order by the items in the selected column • Sort Descending – sorts the data in descending order by the items in the selected column • (All) – displays all rows in the list • (Top 10…) – displays the top n rows in the list, where n is any number you specify • (Custom…) – specifies a custom filter • List items – the current data items in the selected column

  30. Simple and Custom Criteria • Simple criteria – consists of a single search condition for a column of data. Simple criteria can be applied once or multiple times • Custom criteria – a set of search conditions (consisting of a comparison operator and a value) to which data is compared. You can: • specify one or two custom criteria at a time • specify whether matching entries must meet both or either criteria • Comparison operator – a mathematical expression such as “is greater than”

  31. Sorting Data To sort data, the data must be in list format To sort a list based on one criterion: • Select a cell in a column in the list • Click the Sort Ascending or Sort Descending button in the Standard toolbar • Click the Sort Ascending or Sort Descending option in the AutoFilter drop-down list

  32. To sort a list based on multiple criteria: Display the Sort dialog box Specify sort keys and sort orders using the Sort By and Then By drop-down lists and options Specify whether or not the list contains a header row Sorting Data (cont’d)

  33. Lesson 3:Restructuring and Formatting Worksheets

  34. Insert and delete cells Insert and delete rows and columns Adjust column widths and row heights Hide and unhide rows and columns Apply numeric formats Align cell data Change fonts and text attributes Add borders Add background shading Use styles Find and replace cell formats Add worksheet backgrounds Apply AutoFormats Create workbooks from templates Objectives

  35. When you insert cells, surrounding cells shift to the right or down to accommodate the added cells When you delete cells, cell contents are deleted and the surrounding cells shift to the left or up to fill in the gap Inserting and Deleting Cells

  36. Inserting and Deleting Rows and Columns • When you insert rows or columns, existing data moves down or to the right • When you delete rows or columns, the remaining data moves up or to the left, and all data within the deleted rows or columns is also deleted • When you insert or delete rows or columns, the total number of rows and columns in the worksheet remains constant

  37. Adjusting Column Widths and Row Heights To adjust column width: • Use the Column Width dialog box – specify an exact amount (automatically rounded to the nearest one-seventh of a character) To adjust column width or row height: • Use the mouse – drag column or row borders to increase or decrease column width or row height • Use AutoFit – double-click the right border of a column or the bottom border of a row to fit the data contained therein

  38. Hiding and Unhiding Rows and Columns • Hide rows or columns to: • Prevent others from viewing certain data • Work on two columns or rows that do not appear in the same window • Unhide rows or columns to redisplay the hidden data

  39. Applying Numeric Formats • Number format – displays numbers with a specified number of decimal places • Percent format – displays numbers as percentages (%) with a specified number of decimal places • Comma format – displays numbers with thousands separators (,) and two decimal places

  40. Applying Numeric Formats (cont’d) • Accounting format – displays numbers with dollar signs ($) and thousands separators. Dollar signs are vertically aligned at the cell’s left edge. • Currency format – displays numbers with leading dollar signs and thousands separators • Date format – enables you to specify the appearance of a date entry

  41. Changing the Alignment of Data • By default, text is left-aligned and numbers are right-aligned within cells • Use the alignment buttons in the Formatting toolbar to left-justify, center or right-justify the data within cells

  42. Use the Orientation options in the Alignment tab of the Format Cells dialog box to rotate text in cells (you can rotate text up to 90 degrees in each direction) Rotating Text in Cells

  43. Merging and Splitting Cells To merge cells: • Click the Merge And Center button in the Formatting toolbar (centers the text in the merged cell) • You can merge cells only one row at a time. Merging multiple rows will keep only the upper-left-most data in the selected range. To split merged cells: • Click the Merge And Center button

  44. Indenting Text in Cells To indent text in cells: • Use the Increase Indent and Decrease Indent buttons in the Formatting toolbar

  45. Changing Fonts and Text Attributes • Font – the typeface and type size of data • Attributes – the characteristics of text formatting that you can apply to data To change fonts and text attributes: • specify attributes in the Format Cells dialog box • click formatting buttons in the Formatting toolbar • use the Format Painter to copy existing formatting to other cells

  46. Adding Borders To add (and modify) borders: • Use the Borders toolbar to specify style and color, then select the cell(s) with the border drawing tool to draw the border • Use the Borders button in the Formatting toolbar to specify border options • Use the Border tab of the Format Cells dialog box to specify border options

  47. Adding Background Shading To add background shading: • Use the Fill Color button in the Formatting toolbar to display and add color fills • Use the Patterns tab of the Format Cells dialog box to specify background colors and patterns

  48. Using Styles • Style – a set of formatting characteristics identified by a style name • Use styles to format cells quickly and consistently You can create styles: • by example – select a cell containing the formatting you want, then assign a name to the group of formatting attributes • by definition – specify the formatting attributes you want to include in the style using the Format Cells dialog box

  49. To apply a style, select the cell or range to which you want to apply the style, then select a style from the Style dialog boxNote: The style will override any formats you previously applied to the cell(s) Using Styles (cont’d)

  50. Finding and Replacing Cell Formats You specify cell format search and replacement criteria using the advanced search options in the Find And Replace dialog box

More Related