Intro to Excel Spreadsheets

1
Intro to Excel Spreadsheets

• Nancy Clark
• FSU College of Medicine

2
What are they used for?

• Financial forecasting
• Statistical analysis
• Data collection
• Data manipulation
• Summarizing data
• Communicating data

• Budgets
• Research
• Gradebook
• Charts
• Big tables
• Job aids

3
Spreadsheet Construction
Cell Address

• Columns (letters)
• Rows (numbers)
• Worksheets
• Cells (E9)

4
Mousing Around a Cell
Tue
Mon
Highlight/Select
Move Contents
Fill Contents
5
Cell Formats
45.2719
45.3
45.27
.2719
27
5-Mar-02
5-Mar
3/5/2002
STUDENT

• Words
• Numbers
• decimal places
• Days/Dates
• Formulas Calculations

6
Formulas

• A2B2C2
• SUM(A2AS2)

• 29.00
• 1,886.00

AutoSum
Formula
Results
7
Formulas
SUM
(A2AS2)
Function
Range

• Today()
• Average(range)
• Count(range)
• Max(range)
• IF(logical_test,value_if_true,value_if_false)
• IF(F3gt89,"A",IF(F3gt79,"B",IF(F3gt69,"C","F")))
• Formulas can be Filled Down
• Insert menu, Function, Help on this function

• Column
• (C2C80)
• Row
• (B2AK2)
• Block
• (A2G50)

8
More on Cell Formats

• Alignment
• Word wrap
• Orientation
• Shrink to fit
• Font

9
More on Cell Formats

• Width
• Height

10
Merging and Splitting

• Add to Formating Toolbar
• Highlight cells to merge
• Click Merge Button
• Unmerge

11
Copying and Pasting

• Highlight cells
• Hit Copy (CTRL-C)
• Dotted lines
• Click on target cell
• Top of area
• Hit Paste (CTRL-V)
• Unless still dotted not in Clipboard
• CTRL to select multiple targets

12
Worksheets

• Open
• U\Nancy Clark\Spreadsheets\Handbase Pt
  Encounters.xls

13
Sorting Data

• Entire worksheet of tabular data with headings
• Data menu, Sort
• Small section of worksheet
• Highlight section
• Hit sort button

14
Formulas that Reference Other Worksheets

• COUNT(Allison!B2Allison!B100)
• Allison!B2 -- cell B2 on sheet Allison

15
Copying Worksheets

• Edit menu
• Move or copy sheet

2
1
3
16
Adding and Deleting Sheets

• To add a sheet
• Insert menu
• Worksheet
• To delete a sheet
• Edit menu
• Delete sheet

17
Page Setup (Format)

• Landscape or Portrait
• Print to 1 page wide
• Margins
• Headers and Footers
• Print Titles

18
Making Sheets Pretty

• Do it yourself method
• Fill Color
• Font Colors
• Borders toolbar
• Autoformat (Format)
• Conditional Formatting

19
Charts

• Visual representations of data
• Select Data
• Insert menu, Chart

20
Charts Step 1
21
Charts Step 2
22
Charts Step 3
23
Charts Step 4
24
Charts Final
25
Different Scales
26
(No Transcript)
27
Printing

• Always Print Preview
• Page Setup
• Select Print Area
• Highlight area
• File, Set Print Area
• File, Clear Print Area

28
Insert Excel Spreadsheets

• Copy and Paste
• Use Insert Microsoft Excel Worksheet button
• Double click on table or chart
• Calls up Excel

29
Suggestion for Spreadsheet

• Summarize your patient data
• No data? Do the composite.
• Do a chart of some part of the data
• Put the data sheet in as well
• 3 sheets
• Data
• Summary
• Chart

30
Statistical Analysis

• Using Excel

31
Descriptive Statistics

• Distribution
• frequency distribution
• Histogram
• Central tendency
• Mean
• Median
• mode

• Dispersion
• Range
• Standard deviation
• Variance
• N
• Not P (inferential stats)

32
Central Tendency

• Mean
• AVERAGE(b2b1500)
• Median
• MEDIAN(A2A7)
• Mode
• MODE(A2A7)
• N
• COUNT(A2A1500)
• COUNTBLANK(A2B5)

33
Dispersion

• Range
• MAX(A2A60)- MIN(A2A60)
• Standard deviation
• STDEV(A2A110)
• Variance
• VAR(A2A110)

34
Distribution

• Frequency distribution
• Not easy use SPSS
• FREQUENCY(data_array,bins_array)
• Use help
• Histogram
• Bar chart of frequency table

35
Data Tables

• Field names at top
• Each row is a record (sample)
• Sorting whole table
• By one column
• By more than one column
• Sorting individual sections

36
Hands on experience

• Analyze data in examples.xls

37
Sensitivity, Specificity

• sensitivity a / (ac)
• specificity d / (bd)