1 / 69

Excel 2010

Excel 2010 . What is an Excel program ? . Excel is a spreadsheet program that allows you to store, organize, and analyze information . The Excel Interface. Ribbon. The Excel Interface. Quick Access Toolbar Worksheets:

luana
Télécharger la présentation

Excel 2010

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 2010

  2. What is an Excel program ? • Excel is a spreadsheet program that allows you to store, organize, and analyze information.

  3. The Excel Interface • Ribbon

  4. The Excel Interface • Quick Access Toolbar • Worksheets: • Excel files are called workbooks. Each workbook holds one or more worksheets (also known as "spreadsheets"). • Three worksheets appear by default when you open an Excel workbook. You can rename, add and delete worksheets.

  5. The Excel Interface • Horizontal Scroll Bar • Zoom Control • Page View • Normal view is selected by default, and shows you an unlimited number of cells and columns. It is highlighted in the image below. • Page Layout view divides your spreadsheet into pages. • Page Break view lets you see an overview of your spreadsheet, which is helpful when you are adding page breaks.

  6. The Excel Interface • Row • A row is a group of cells that runs from the left of the page to the right. In Excel, rows are identified by numbers. Row 15 is selected in the image below. • Column • A column is a group of cells that runs from the top of the page to the bottom. In Excel, columns are identified by letters. Column L is selected in the image below.

  7. The Excel Interface • Name Box • The Name box tells you the location or the "name" of a selected cell. • Formula Bar • In the formula bar, you can enter or edit data, a formula, or a function that will appear in a specific cell. • Note how the data appears in both the formula bar and in selected cell.

  8. Cell Basics • Cells are the basic building blocks of a worksheet. Cells can contain a variety of content such as text, formatting attributes, formulas, and functions. • cell address : based on which column and row it intersects

  9. Cell Basics • To Select a Cell • To Select Multiple Cells • Cell Content: • TextCells can contain letters, numbers, and dates. • Formatting attributesCells can contain formatting attributes that change the way letters, numbers, and dates are displayed. For example, dates can be formatted as MM/DD/YYYY or Month/D/YYYY. • CommentsCells can contain comments from multiple reviewers. • Formulas and FunctionsCells can contain formulas and functions that calculate cell values. For example, SUM(cell 1, cell 2...) is a formula that can add the values in multiple cells.

  10. Cell Basics • To Insert Content • To Delete Content Within Cells:

  11. Cell Basics • To Delete Cells • To Drag and Drop Cells • To Copy and Paste Cell Content • To Cut and Paste Cell Content • To Use the Fill Handle to Fill Cells

  12. Cell Basics • To Access More Paste Options • To Access Formatting Commands by Right-Clicking

  13. Modifying Columns, Rows, and Cells • To Modify Column Width • To Set Column Width with a Specific Measurement: • Select the columns you want to modify. • Click the Format command on the Home tab. The format drop-down menu appears. • Select Column Width. Note : Select AutoFit Column Width from the format drop-down menu and Excel will automatically adjust each selected column so that all the text will fit.

  14. Modifying Columns, Rows, and Cells • To Modify the Row Height • To Set Row Height with a Specific Measurement:

  15. Modifying Columns, Rows, and Cells • To Insert Rows • Select the rowbelow where you want the new row to appear. • Click the Insert command on the Hometab • To Insert Columns • Select the column to the right of where you want the new column to appear. • Click the Insert command on the Hometab • When inserting new rows, columns, or cells, you will see the Insert Options button by the inserted cells.

  16. Modifying Columns, Rows, and Cells • To Delete Rows • Select the rows you want to delete • Click the Delete command on the Hometab • Do the same thing to Delete Columns

  17. Modifying Columns, Rows, and Cells • Wrapping Text and Merging Cells: • Select the cells with text you want to wrap. • Select the Wrap Text command on the Hometab • If you change your mind, re-click the Wrap Text command to unwrap the text.

  18. Modifying Columns, Rows, and Cells • To Merge Cells Using the Merge & Center Command: • Select the cells you want to merge together. • Select the Merge & Center command on the Home tab.

  19. Modifying Columns, Rows, and Cells • To Access More Merge Options: • Merge & Center: Merges selected cells into one cell and centers the text. • Merge Across: Merges each row of selected cells into larger cells. This command is useful if you are merging content across multiple rows of cells and do not want to create one large cell. • Merge Cells: Merges selected cells into one cell. • Unmerge Cells: Unmerges the selected cells.

  20. Formatting Cells • Formatting Text • Font, Color, Size, Alignment • To Add a Border • Formatting Numbers and Dates • Select the cells you want to modify. • Click the drop-down arrow next to the Number Format command on the Home tab.

  21. Formatting Cells • Formatting Numbers and Date: • General is the default format for any cell. When you enter a number into the cell, Excel will guess the number format that is most appropriate. • Number formats numbers with decimal place "4.00". • Currency formats numbers as currency with a currency symbol "$4.00". • Accounting formats numbers as monetary values like the Currency format, but it also aligns currency symbols and decimal places within columns”$ 12.5”. • Short Date formats numbers as M/D/YYYY "8/8/2010". • Long Date formats numbers as Weekday, Month DD, YYYY "Monday, August 01, 2010". • Time formats numbers as HH/MM/SS and notes AM or PM"10:25:00 AM". • Percent formats numbers with decimal places and the percent sign. "75.00%". • Fraction formats numbers as fractions separated by the forward slash "1/4“ • Scientific formats numbers in scientific notation. "140000“ "1.40E+05". • Text formats numbers as text, meaning that what you enter into the cell will appear exactly as you wrote it.

  22. Saving • Save AS • Save • AutoRecover • Save As PowerPoint 97 - 2003 Presentation and PDF • To save as PDF, Excel defaults to saving the active worksheet only. If you have multiple worksheets and want to save all of them in the same PDF file, click on Options. The Options dialog box will appear. Select Entire workbook from the Options dialog box and click OK.

  23. Creating Simple Formulas • To Create a Simple Formula in Excel: • Select the cell where the answer will appear • Type the equal sign (=) • Type in the formula you want Excel to calculate • Press Enter. The formula will be calculated and the value will be displayed in the cell

  24. Creating Simple Formulas • To Create a Formula Using Cell References: • Select the cell where the answer will appear • Type the equal sign (=). • Type the cell address that contains the first number in the equation • Type the operator you need for your formula. • Type the cell address that contains the second number in the equation • Press Enter. The formula will be calculated and the value will be displayed in the cell. Note: If you change a value in either B1 or B2, the total will automatically recalculate

  25. Creating Simple Formulas • To Create a Formula using the Point and Click Method: • Select the cell where the answer will appear. • Type the equal sign (=). • Click on the first cell to be included in the formula . • Type the operator you need for your formula. • Click on the next cell in the formula . • Press Enter. The formula will be calculated and the value will be displayed in the cell.

  26. Creating Simple Formulas • To Edit a Formula: • Click on the cell you want to edit. • Insert the cursor in the formula bar and edit the formula as desired. You can also double-click the cell to view and edit the formula directly from the cell. • When finished, press Enter or select the Enter command √ • The new value will be displayed in the cell.

  27. Worksheet Basics • To Rename Worksheets • To Insert New Worksheets • To Delete Worksheets

  28. Worksheet Basics • To Copy a Worksheet

  29. Worksheet Basics • To Move a Worksheet • Click on the worksheet you want to move. The mouse will change to show a small worksheet icon . • Drag the worksheet icon until a small black arrow appears where you want the worksheet to be moved. • To Color-Code Worksheet Tabs

  30. Worksheet Basics • Grouping and Ungrouping Worksheets • Select the first worksheet you want in the group • Press and hold the Ctrl key on your keyboard • Select the next worksheet you want in the group. Continue to select worksheets until all of the worksheets you want to group are selected. • Release the Ctrl key. The worksheets are now grouped. The worksheet tabs appear white for the grouped worksheets. • To Ungroup All Worksheets: • Right-click one of the worksheets. The worksheet menu appears. • Select Ungroup. The worksheets will be ungrouped.

  31. Worksheet Basics • To Freeze Rows • Select the row below the rows that you want frozen. For example, if you want rows 1 & 2 to always appear at the top of the worksheet even as you scroll, then select row 3 • Click the Viewtab • Click the Freeze Panes command. A drop-down menu appears. • Select Freeze Panes • To Freeze Columns • Select the column to the right of the columns you want frozen. For example, if you want columns A & B to always appear to the left of the worksheet even as you scroll, then select column C , then do the same thing above. • To Unfreeze Panes • Select Unfreeze Panes. From Freeze Panes

  32. Printing • To Print Active Sheets: • Select the worksheets you want to print. To print multiple worksheets, click on the first worksheet, hold down the Ctrl key, then click on the other worksheets you want to select. • Select Print Active Sheets from the print range drop-down menu. • To Print the Entire Workbook • To Print a Selection, or Set the Print Area: • Select the cells that you want to print. • Select Print Selection from the print range drop-down menu. • To Fit a Worksheet on One Page

  33. Printing • To Use Print Titles: • Click the Page Layout tab. • Select the Print Titles command. • The Page Setup dialog box appears. Click the icon at the end of the Rows to repeat at top field. • Your mouse becomes the small selection arrow . Click on the rows you want to appear on each printed page. The Rows to repeat at top dialog box will record your selection. • Click the icon at the end of the Rows to repeat at top field. • Repeat for Columns to repeat at left, if necessary. • Click OK. You can go to Print Preview to see how each page will look when printed.

  34. Creating Complex Formulas • Order of Operations • Excel calculates formulas based on the following order of operations: • Operations enclosed in parentheses • Exponential calculations (to the power of) • Multiplication and division, whichever comes first • Addition and subtraction, whichever comes first

  35. Creating Complex Formulas • Relative References • By default, cell references are relative references. When copied or filled, they change based on the relative position of rows and columns. If you copy a formula (=A1+B1) into row 2, the formula will change to become (=A2+B2). • Absolute references • on the other hand, do not change when they are copied or filled and are used when you want the values to stay the same.

  36. Working with Basic Functions • The Parts of a Function • Working with Arguments • Colons create a reference to a range of cells. For example, =AVERAGE(E19:E23) would calculate the average of the cell range E19 through E23. • Commas separate individual values, cell references, and cell ranges in the parentheses. If there is more than one argument, you must separate each argument by a comma. For example, =COUNT(C6:C14,C19:C23,C28) will count all the cells in the three arguments that are included in parentheses.

  37. Working with Basic Functions • Using AutoSum to select Common Functions: • Select the cell where the answer will appear (E24, for example). • Click on the Home tab. • In the Editing group, click on the AutoSum drop-down arrow and select the function you desire. • A formula will appear in the selected cell. If logically placed, AutoSum will select your cells for you. Otherwise, you will need to click on the cells to choose the argument you desire. • Press Enter and the result will appear.

  38. Function Library • The Insert Function command allows you to easily search for a command by entering a description of what you are looking for. • The AutoSum command allows you to automatically return results for common functions. • Use the Recently Used command • The Financial category contains functions for financial calculations like determining a payment (PMT) or interest rate for a loan (RATE). • Functions in the Logical category check arguments for a value or condition. For example, if an order is over $50 add $4.99

  39. Function Library • The Text category contains functions that work with the text in arguments using tasks like converting text to lowercase (LOWER) • The Date & Time category contains functions for working with dates and time and will return results like the current date and time (NOW) or the seconds (SECOND). • The Lookup & Reference category contains functions that will return results for finding and referencing. • The Math & Trig category includes functions for numerical arguments. For example, find the value of Pi (PI) . • More Functions contains additional functions under categories for Statistical, Engineering, Cube, Information, and Compatibility.

  40. Function Library • To Insert a Function from the Function Library:

  41. Function Library

  42. Sorting Data • To Sort in Alphabetical Order: • Select a cell in the column you want to sort by. • Select the Data tab, and locate the Sort and Filter group. • Click the ascending command  to Sort A to Z, or the descending command  to Sort Z to A. • To Sort in Numerical Order • To Sort by Date or Time

  43. Sorting Data • Custom Sorting:

  44. Sorting Data • Sorting Multiple Levels:

  45. Outlining Data • Outlinesgive you the ability to group data that you may want to show or hide from view, and create a quick summary using the Subtotal command. Because outlines rely on grouping data that is related, you must sort before you can outline.

  46. Outlining Data • Outline Data Using Subtotal • Sort according to the data you want to outline. Outlines rely on grouping data that is related • Select the Data tab, and locate the Outlinegroup • Click the Subtotal command to open the Subtotal dialog box. • the At each change in field, select the column you want to use to outline your worksheet • In the Use function field, choose from the list of functions that are available for subtotaling • Select the column you want the subtotal to appear in. • Click OK.

  47. Outlining Data • Show or Hide a Group • Click the minus sign, also known as the Hide Detail symbol, to collapse the group • Click the plus sign, also known as the Show Detail symbol, to expand the group again • View Groups by Level • Click the highest level to view and expand all of your groups. • Click the next levelto hide the detail of the previous level. • Click the lowest level to display the lowest level of detail.

  48. Outlining Data • Ungroup Data • Select the rows or columns that you want to ungroup. • From the Data tab, click the Ungroupcommand. • To ungroup all the groups in your outline, open the drop-down menu under the Ungroup command, and choose Clear Outline • Ungroup and Clear Outline will not remove subtotaling from your worksheet. Summary or subtotal data will stay in place and continue to function until you remove it

  49. Outlining Data • Ungroup Data and Remove Subtotaling • From the Data tab, click the Subtotal command to open the Subtotal dialog box. • Click Remove All. • All data will be ungrouped, and subtotals will be removed. • Create and Control Your Own Group • Select the range of cells that you want to group. • From the Data tab, click the Group command. • Excel will group the selected columns or rows. • Click the minus sign, also known as the Hide Detail symbol, to hide the group • The group will be hidden from view.

  50. Filtering Data • Filter Data • Begin with a worksheet that identifies each column using a header row. • Select the Data tab, and locate the Sort & Filtergroup • Click the Filtercommand • Click the drop-down arrow for the column you would like to filter. In this example, we will filter the Type column to view only certain types of equipment. • Uncheck the boxes next to the data you don't want to view. (You can uncheck the box next to Select All to quickly uncheck all.) • Check the boxes next to the data you do want to view. • Click OK. All other data will be filtered, or temporarily hidden.

More Related