Using Excel in the Library

1
Using Excel in the Library

• Instructor
• Sandy Lamoureux
• sllamoureux_at_earthlink.net
• An Infopeople Workshop
• Fall 2004

2
This Workshop Is Brought to You By the Infopeople
Project

• Infopeople is a federally-funded grant project
  supported by the California State Library. It
  provides a wide variety of training to California
  libraries. Infopeople workshops are offered
  around the state and are open registration on a
  first-come,
• first-served basis.

3
Introductions

• Name
• Library
• Position at the library
• How do you want to use Excel?

4
Agenda

• Introduction to Excel
• Creating a worksheet
• Formatting a worksheet
• Working with formulas
• Managing data in a worksheet
• Creating graphs and charts
• Saving and printing a worksheet

5
What is Excel?

• An electronic spreadsheet program.
• Store, summarize, evaluate, share, and present
  relevant information in an organized format.

6
Worksheets are made up of

• Rows - designated by numbers
• Columns designated by letters
• Cells
• an intersection of a row and a column
• a single addressable unit in a spreadsheet
• Worksheets - Individual spreadsheets

7
Workbook

• Workbooks are made up of one or more worksheets.

8
Moving Between Cells

• to move to the next cell to the right in
  a row.
• to move to the next cell down in a
  column.
• You may also use the arrow keys
• to move to adjacent cells.

Tab
Enter
9
Creating a Worksheet

• Plan
• What information do you want to organize?
• What problem do you want to solve?
• What would be the best way to present your
  information?
• Determine row and column labels

10

• Open a new worksheet
• Name and save the worksheet
• Enter column and row labels
• Enter and format data
• Change column width
• Sort information

11
In order to effect you must select.
12
Items to Format in a Worksheet

• Data
• Numbers
• Dates
• Cells
• AutoFit
• Borders and Shading

13
In order to effect you must select.
14
Working With Formulas

• A Formula in Excel, is the equation that produces
  a new value from existing values.
• Use formulas to calculate a value to be
  displayed.
• When a cell contains a formula, the formula is
  displayed in the Formula Bar.
• The result of the formula displays in the cell.

15

• A Formula always begins with
• A1B1C1
• Add cells A1, B1, C1
• A1 D2
• Subtract cell D2 from A1

16
Order of Calculations

• 1. All calculations inside parentheses (nesting)
  are done first.
• 2. Exponents ()
• 3. Multiplication () and Division (/)
• 4. Addition () and Subtraction (-)

17

• A1B1C1
• A1B1C1
• (A1B1)C1
• A1/B1C1
• A1/(B1C1)

18
Troubleshooting Formulas

• sign
• Cell references
• Relative
• Absolute -
• Order of calculation
• Parentheses
• Punctuation
• Number formatting

19
Charts and Graphs

• Definition A chart or graph is a graphical
  representation of values in the worksheet.

20
Chart Wizard

• Excel offers many types of charts and graphs
• Use the Chart Wizard to create
• For greatest impact
• keep charts simple
• label them clearly

21

• Use the Chart Wizard
• Select data to include in the chart.
• Run the chart Wizard from the tool bar.
• Select the type of chart for the data.
• Label components of the chart.
• Try different chart types.
• Refine the chart after the wizard is finished.

22
Why would you use excel to organize information
in a list?

• Columns and rows
• Organizing information
• Sort and filter information

23

• Shelf reading
• Desk schedules
• Items to order
• Friends addresses and phone numbers
• Volunteer hours

24
Managing Information in a List

• Sort
• Rearrange the order of rows based on the
  contents of one or more columns.
• Filter
• Hide rows in a list so only rows that meet a
  certain criteria are visible.

25
Make the List Readable

• Format column labels so they are easy to read
• Use cell borders
• Avoid blank columns and rows in the list
• Dont type leading or trailing spaces

26
Create Your Own Spreadsheet

• Plan
• What would be the best way to present your
  information?
• Organize data
• Determine row and column labels
• Enter your information into Excel
• Format worksheet
• Enter available information
• Calculations

27
Printing a Worksheet

• Use Page Setup
• Set the Print Area
• Print comments, gridlines
• Print row and column headings
• Repeat headings on each page
• Fit printed document to the page
• Use Print Preview

28
Remember ---
In order to effect you must select.