Microsoft Excel for Poverty Law Advocates: Intermediate Gabrielle Hammond LSNTAP.Org

1. Microsoft Excel for Poverty Law Advocates: Intermediate Gabrielle Hammond www.LSNTAP.Org

2. NTAP’s Intermediate Excel Training Topics Covered: • A Review of the Fundamentals • Multiple Worksheets • Formatting Worksheets • Fixing Headers for View and Print • Using the Fill Function • Relative versus Absolute References • Charts and Graphs • Mail Merge from Excel Data

3. Review of the Basics • A spreadsheet program • The Name Box • The cell, cell range, and simple functions • Order of mathematical equations

4. Starting Simple: Cell References and Cell Ranges • In the Name Box, the cell location is referenced. • Next to the Name Box is the Insert Function bar. Formulas are denoted by the EQUALS sign “=“. • A cell reference is a group of cells you designate, usually with a colon. Example: =B1:B5

5. Remember Simple Functions • = is key to recognize all characters thereafter as a formula • : is a range (B1 through B5) • , indicates only the cells on either side of the comma: (B1 only and B5 only, not B2, B3, or B4) • SUM • AVERAGE

6. Remembering Simple Equations • B1+B5 Addition • B1-B5 Subtraction • B1*B5 Multiplication • B1/B5 Division • B1%B5 Percentage • B1^B6 Exponential Equations

7. Name that Cell Reference! • The cell in Column A and Row 1 • A1 • The range of cells in Col A and Rows 10 thru 20 • A10:20 • The range of cells in Row 15 and Cols B thru E • B15:E15

8. Name that Cell Reference! • All cells in Row 5 • 5:5 • All cells in Rows 5 - 10 • 5:10 • All cells in Column H thru J • H:J • Range of cells in Col A thru E, rows 10 thru 20 • A10:E20

9. NTAP’s Intermediate Excel Training Topics Covered: • A Review of the Fundamentals • Multiple Worksheets • Formatting Worksheets • Fixing Headers for View and Print • Using the Fill Function • Relative versus Absolute References • Charts and Graphs • Mail Merge from Excel Data

10. Topic 1: A Workbook Contains “Sheets”

11. Topic 1: To Rename or Move, Right-Click.

12. Topic 1: Shortcuts • You can also move a worksheet by dragging and dropping the tabs • You can rename by double-clicking a tab and then when the name is highlighted you type the new name

13. Topic 1: Why Multiple Worksheets? • Group Important Data in one File • Worksheets refer to each other: Link them together. • Example: The State of Colorado Child Support Spreadsheet creates a pleading from data entered by the plaintiff

14. Topic 1:Create Cross Reference btw Worksheets • Move the cursor to the desired cell on the other sheet, type = and select the cell you want referenced. • OR, USE CODE: =sheetname!cell reference; for example =Gabrielle!B13

15. NTAP’s Intermediate Excel Training Topics Covered: • A Review of the Fundamentals • Multiple Worksheets • Formatting Worksheets • Fixing Headers for View and Print • Using the Fill Function • Relative versus Absolute References • Charts and Graphs • Mail Merge from Excel Data

16. Formatting Worksheets • Formatting Cells • Number • Alignment • Font • Border • Patterns • Protection

17. Formatting Cells

18. Format Cells

19. NTAP’s Intermediate Excel Training Topics Covered: • A Review of the Fundamentals • Multiple Worksheets • Formatting Worksheets • Fixing Columns and Rows for View and Print • Using the Fill Function • Relative versus Absolute References • Charts and Graphs • Mail Merge from Excel Data

20. Freeze Panes for Viewing • An Excel spreadsheet can be completed with infinite rows or columns. • When you want to keep rows or columns fixed on the screen for easier viewing

21. Freeze Columns & Rows • 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 • Ex: To freeze Row 1, put cursor in cell A2.

22. Freeze Columns for Viewing • To Freeze a Column for Viewing: • Put cursor in first cell R of column you want to freeze. • Go to Window on Menu • Click Freeze Panes • Example: To freeze Column A, put cursor in B1

23. Freezing Both Columns and Rows • Put cursor in first cell below and to the R of Column and Row you want to freeze. • Go to Window on the Menu Bar. • Click Freeze Panes. • Example: To freeze Col A and Row 1, put your cursor in Cell B2.

24. Freeze Panes

25. NTAP’s Intermediate Excel Training Topics Covered: • A Review of the Fundamentals • Multiple Worksheets • Formatting Worksheets • Fixing Headers for Viewand Print • Using the Fill Function • Relative versus Absolute References • Charts and Graphs • Mail Merge from Excel Data

26. Print Column and Row on Every Page • Row headings are the row numbers to the L or the worksheet • Column headings are the letters or numbers that appear at the top of the columns to a worksheet

27. Print Rows or Columns Each Page • Click the worksheet • File Menu | Page Setup | Sheet • Find the Rows to Repeat at Top or Columns to Repeat at Left • Select the Row and Column Headings when your cursor is in that field • Notice the code: $1:$1 or $A:$A

28. Print Columns & Rows Each Page

29. NTAP’s Intermediate Excel Training Topics Covered: • A Review of the Fundamentals • Multiple Worksheets • Formatting Worksheets • Fixing Headers for View and Print • Using the Fill Function • Relative versus Absolute References • Charts and Graphs • Mail Merge from Excel Data

30. Saving Time: The Fill Function • Allows you to conveniently replicate a formula across columns or rows • Can be absolute or relative • A Time Saver.

31. Fill Function: Before

32. Fill Function: After

33. Relative v. Fixed Values • 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.

34. Relative References • Think of compound interest…. • Every cell will be changed relative to the prior cell.

35. Fixed or Absolute Values • Every cell is changed according to the original formula / value. • Think of simple interest…

36. Fill to the Right – Relative Reference • OPTION 1….Good ole Copy and Paste • 1. Enter formula in one cell. • Copy it. • Drag cursor across fields you want it copied to… Paste. • Relative Reference moves the referenced field in relation to the fill • OPTION 2…Fill to the Right • 1. Menu Bar | Edit | Fill to Right. • 2. Menu Bar | Edit | Fill… Series • OPTION 3…Use the Keyboard • 1. Enter the formula that will be filled • 2. Highlight row to be filled and click <ctrl>R

37. Fill Down – Relative Reference • Enter the formula that will be filled • Highlight column to be filled and click <ctrl>D • Other 2 Options Work too…

38. Absolute Reference: Remember $$ • OPTION 1: • Enter the formula to be filled • Use format $COLUMN$ROW for fixed reference • OPTION 2: • Shortcut to get fixed reference is F4

39. Fill to the right – Fixed Reference From another worksheet • Enter the formula to be filled • Use format =Sheetname!$x$z for fixed reference where x and z are the cell references on the other sheet

40. Advanced Topic Preview: Food for Thought • Mixed Fixed references like $COLUMNROW or COLUMN$ROW • F4 toggles through full and partial fixed references

41. NTAP’s Intermediate Excel Training Topics Covered: • A Review of the Fundamentals • Multiple Worksheets • Formatting Worksheets • Fixing Headers for View and Print • Using the Fill Function • Relative versus Absolute References • Charts and Graphs • Mail Merge from Excel Data

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

43. Pie Chart

44. Select Pie from Wizard, then Next

45. Highlight Data Range

46. Click Series Tab and Select Category Labels and Name chart then Next

47. Click on Data Labels, Select Desired Items

48. Click on Legend Tab, Select Location then Next

49. Select where to put Chart and name new Sheet if applicable

50. Finished Result