OFF105: MS Excel Beginning and Intermediate for Advocates

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
Todays 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
Todays 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 B1B5 (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

• (B1B5) Addition
• (B1-B5) Subtraction
• (B1B5) Multiplication
• (B1/B5) Division
• (B1B5) Percentage
• (B1B6) Exponential Equations
• SUM (B1B5)
• AVERAGE (B1B5)

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
Todays 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
Todays 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 MenuPage SetupSheet
• 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
Todays 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
Todays 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
Todays 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
Todays 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

51
Importing and Exporting Data

• Survey Tool Data
• Contact Lists from other Databases
• PIKA Reports
• Email Contact Lists

52
Beginning and Intermediate Excel Training
Todays 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

53
Excel 2003 vs. Excel 2007
54
Excel 2003 vs. Excel 2007
55
Questions?

• www.lsntap.org/techlibrary
• Jeff Narabrook
• jeff_at_lstntap.org