1 / 55

OFF105: MS Excel (Beginning and Intermediate) for Advocates Jeff Narabrook lsntap

OFF105: MS Excel (Beginning and Intermediate) for Advocates Jeff Narabrook www.lsntap.org. Salient Functions of Excel. Lists of data Contacts, dates, etc. Financial tracking Budgets Charts and graphs Grant Reporting Customized calcuation sheets Import and export data .

denim
Download Presentation

OFF105: MS Excel (Beginning and Intermediate) for Advocates Jeff Narabrook lsntap

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. OFF105: MS Excel (Beginning and Intermediate) for Advocates Jeff Narabrook www.lsntap.org

  2. Salient Functions of Excel • Lists of data • Contacts, dates, etc. • Financial tracking • Budgets • Charts and graphs • Grant Reporting • Customized calcuation sheets • Import and export data

  3. Beginning and Intermediate Excel Training Today’s Agenda • What is Excel and Why Use It? • Excel Fundamentals • Using Worksheets • Formatting Worksheets • Fill Function • Relative versus Absolute References • Charts and Graphs • Importing and Exporting Data • Excel 2003 vs. Excel 2007

  4. What is Excel and Why Use It? • Spreadsheet program allowing data entry into rows and columns • Very useful for managing certain types of data • Your funders and partners expect you to be able to use it!

  5. Beginning and Intermediate Excel Training Today’s Agenda • What is Excel and Why Use It? • Excel Fundamentals • Using Worksheets • Formatting Worksheets • Fill Functions • Relative versus Absolute References • Charts and Graphs • Importing and Exporting Data • Excel 2003 vs. Excel 2007

  6. Excel Fundamentals • Name Box • Formula Bar • Cell References • Making Formulas • Workbook and Worksheets

  7. Formula Bar and Name Box

  8. Cell References • A colon indicates a range of cells B1:B5 (B1 through B5) • A comma indicates only individually listed cells B1, B2, B3, B5 (does not include B4)

  9. Cell Range

  10. Cell Range

  11. Creating Formulas • Formulas contain • An = sign • Cells or cell references • Calculation symbol

  12. Calculation Symbols • =(B1+B5) Addition • =(B1-B5) Subtraction • =(B1*B5) Multiplication • =(B1/B5) Division • =(B1%B5) Percentage • =(B1^B6) Exponential Equations • =SUM (B1:B5) • =AVERAGE (B1:B5)

  13. Example: =SUM(….)

  14. Workbook and Worksheets • Excel documents are called ‘workbooks’ which contain ‘worksheets’ • Worksheets are meant to contain sets of data that can be linked to another

  15. Worksheets

  16. Beginning and Intermediate Excel Training Today’s Agenda • What is Excel and Why Use It? • Excel Fundamentals • Using Worksheets • Formatting Worksheets • Fill Function • Relative versus Absolute References • Charts and Graphs • Importing and Exporting Data • Excel 2003 vs. Excel 2007

  17. Using Worksheets • Managing worksheets • Renaming • Moving • Adding/Deleting • Linking worksheets together • Why? • How?

  18. Managing Worksheets

  19. Linking Worksheets Together • Why link worksheets? • How do you link worksheets? • Move the cursor to the desired cell on the other sheet, type = and select the cell you want referenced.

  20. Manually Linking Worksheets • Manual link with Excel code: • =sheetname!cell reference; • For example =Budget!B13 will give you the contents of cell B13 in worksheet named ‘Budget’ • Make sure to use single quotes if the title has a space • Ex. Worksheet Budget Summary will be linked as =‘Budget Summary’!A1 • You can also rename cells in the name box for easier linking codes • No numbers or spaces

  21. Beginning and Intermediate Excel Training Today’s Agenda • What is Excel and Why Use It? • Excel Fundamentals • Using Worksheets • Formatting Worksheets • Fill and Function • Relative versus Absolute References • Charts and Graphs • Importing and Exporting Data • Excel 2003 vs. Excel 2007

  22. Unformatted Worksheet

  23. Formatted Version

  24. Formatting Worksheets • Formatting Cells • AutoFit • Number • Merge and Center • Background Fill • Font • Alignment • Sort • Border • Protection

  25. Freeze Panes • Freezing a row and/or column can make spreadsheets much easier to edit, by keeping your primary headers always in view

  26. How to Freeze Panes • To Freeze a Row: • Put cursor in first cell immediately below the row you want to freeze. • Go To “Window” on the Menu • Click ‘Freeze Panes’ • Notice the Line that demarks it as frozen • To Unfreeze: • Go to “Window” on the Menu • Click ‘Unfreeze’

  27. Freeze Panes

  28. Freeze Panes

  29. Freeze Panes

  30. Freeze Panes

  31. Freeze Panes

  32. Page Setup for Printing • File Menu|Page Setup|Sheet • Repeat Rows/Columns • Prints your primary headings on every page • Header/Footer • Creates a header/footer for each page • Add/remove gridlines • Add or removes cell grid lines • Adjust margins • Adjusts page margins

  33. Beginning and Intermediate Excel Training Today’s Agenda • What is Excel and Why Use It? • Excel Fundamentals • Using Worksheets • Formatting Worksheets • Fill Function • Relative versus Absolute References • Charts and Graphs • Importing and Exporting Data • Excel 2003 vs. Excel 2007

  34. Saving Time: The Fill Function • Allows you to conveniently replicate content and formulas across columns or rows

  35. Fill Function

  36. Fill Function

  37. Fill Function • Click on lower right-hand corner until a + shape appears. Keeping your mouse button depressed, drag below to the cells you want filled.

  38. Fill Function

  39. Fill Function

  40. Fill Function

  41. Beginning and Intermediate Excel Training Today’s Agenda • What is Excel and Why Use It? • Excel Fundamentals • Using Worksheets • Formatting Worksheets • Fill Function • Relative versus Absolute References • Charts and Graphs • Importing and Exporting Data • Excel 2003 vs. Excel 2007

  42. Relative v. Absolute References • Relative is the default. • Relative: • Copies formulas across a worksheet, but does not copy a fixed value in a static cell. • Absolute: • Copies a specific cell reference and its value to other cells.

  43. Beginning and Intermediate Excel Training Today’s Agenda • What is Excel and Why Use It? • Excel Fundamentals • Using Worksheets • Formatting Worksheets • Fill Functions • Relative versus Absolute References • Charts and Graphs • Importing and Exporting Data • Excel 2003 vs. Excel 2007

  44. Charts • Click Insert | Chart • Pick type of Chart or Graph Type • Enter Data Range • Add Legends and other frills

  45. Pie Chart

  46. Highlight Data Range

  47. Title, Axis Names

  48. Data Labels

  49. Finished Result

  50. Beginning and Intermediate Excel Training Today’s Agenda • What is Excel and Why Use It? • Excel Fundamentals • Using Worksheets • Formatting Worksheets • Fill Function • Relative versus Absolute References • Charts and Graphs • Importing and Exporting Data • Excel 2003 vs. Excel 2007

More Related