p366: lecture #1 use of excel for analysis lei chen, md jan 6, 2002

P366: Lecture #1Use of Excel for analysis

Lei Chen, MD

Jan 6, 2002

Administration

• Course web site - www.usc.edu/go/phar366

- all course material will be posted there

• Group assignment - self selected groups

- max of 5 people per group

- submit group information in today’s lab

Class Objectives

• Provide students with the basic data management and statistical analysis skills

• An opportunity to apply basic statistical knowledge

• Practice, practice and practice

Structure of Course

Four major lectures, supplemented by lab

sessions

– Lec #1: Microsoft Excel– Lec #2: Basic Microsoft Access– Lec #3: Advanced Microsoft Access– Lec #4: SPSS

Introduction to Microsoft Excel

• An electronic spreadsheet program

• Very popular and widely used

• Multiple functions provided, such as graphing, statistical tools, financial analysis tools, data mapping tools, etc.

• We will use version Excel 97

Excel Basics

Understanding Excel 97 Screen– Excel icon– Title bar– Menu bar– Tool bars– Formula bar– Scroll bar

Excel Basics

Terminology– File

• a file is operated through a window • a file can have multiple worksheets

– Worksheet • a rectangular grid • maximum size for a worksheet is 66,536 rows by

256 columns• Can also store graphs

Excel Basics

Terminology– Cell

• addressable unit of a worksheet• can store different data types

numerical

text

date / time

formula,etc

Excel Basics

• Manipulating windowsMaximize, minimize, close

• Manipulating filesCreate, open, save

• Manipulating worksheetsInsert, delete, copy/move, rename

Excel Basics

• Where can I find the commands– Menu– Tool bar– Right mouse button

Entering data

• Cell by cellClick a cell, type, move to next cell to enter

more data

• Auto-completeUse of file handles to copy, or create a series

• Importing external file

Selecting data

• Selecting cells

• Selecting columns / rows

• Selecting a data block

• Selecting the whole worksheet

Editing Data

• Move a regionCut - paste, or drag - drop

• Copy a regionCopy – paste

• Paste special

• Insert cell / column / row

• Delete cell / column / row

• Undo / redo

Formatting Data

• First select a region you want to format

• Formatting cells – Formatting numerical data – Font type, style, size and color– background color– Alignment: left, center, right, text rotation – Others to improve the display

Formatting Data

• Formatting columns / rows– Determine row height and column width– Hide / uncover to simplify the view of a

complex worksheet

• Formatting entire worksheet– Hide / uncover– Selecting a background

Basic Data Processing

• Sort– You must select all columns/rows that you want to

remain related

• Filter– A quick and easy way to find and work with a subset

of data which meet the criteria you specify– The order of data will not change

• Name box• Add comments

Advanced Data Processing

• Graphics

• Excel formulas

• Excel analysis ToolPak

Graphics

• Follow the instructions

• Select the graph type you need

• Specify the data for X-axis and Y-axis

• Name X and Y axis, title the graph

Excel Formula

• Excel supports a variety of functions

• A formula can consist of function name, operators, cell references

• Copy a formula: use of fill handle

• All formulas start with “=”

Excel Formula

• Use of cell reference in a formula– Cell reference can be entered in two ways: typed, or selected using mouse– Relative reference change when you move or copy a formula– Absolute reference stay the same type in $ sign, i.e. $E$5

Excel Formula

• Examples– Arithmetic operations: sum, aver, max, min– Logical function, “if”

IF(A3=“PAXIL”, 1, 0)

IF(A3=“PAXIL”, 2, IF(A3=“Prozac”, 1, 0))– Calculate age using two dates

Excel AnalysisToolPak

• Go to “Tool” in menu, then Data analysis

• A variety of statistical analysis available

• Two examples– T-test– Regression

Example-Ttest

• Comparing two group means

• Sorting data first according to the intervention variable

Example - regression

• Prepare independent and dependent variables, including dummy variables

• Use Excel formula

• Specify the data as independent and dependent variables

• It is easy, isn’t it?

Summary

• Those are the Excel Basics

• Excel has limitations– If you wish to manage large or sophisticated

database, you may use Access– If you analysis is large or sophisticated, you

may want to use others, i.e. SPSS