Excel Charts and Graphs
Instructor Heidi Lyons
Going from SAS into Excel
First, make sure you have the HTML output turned on
Tools Options Preferences Results Create HTML
Using the Chart Wizard
Using Chart Wizard
1.
2. 3. 4. 5. 6.
Select the cells that contain the numbers wanted in the chart Click on the Wizard tool Choose a type of chart Data ranges Added Detail As object in or As new sheet
Using the Chart Wizard
Using the Chart Wizard
Using the Chart Wizard
Using the Chart Wizard
Using the Chart Wizard
Percent
Di s tr i ct o
10 15 20 25 30 35 0 5
fC ol u iss iss ip pi an a m bi a
M
Using the Chart Wizard
Percent of Children under 18 Below Poverty
M ex W ic o es tV irg in ia Ar ka ns as Te xa s Al ab am a O kl ah om So ut a h Ca ro lin a
Ne w
Lo ui si
Place
Series1
More than one Data Series
Example
Percent
Di s tr i ct o
40 35 30 25 20 15 10 5 0
fC ol u m bi a ip pi an a o iss iss
M
Ne w W es tV ia Ar ka ns as Te xa s Al O kl ab am a ah om So ut a h Ca ro lin a irg in M ex ic
Lo ui si
Percent of Children under 18 years Below Poverty Level in the Past 12 Months
Place
Place
Percent
D is tri ct of C
10 15 20 25 30 35 0 5
ol um bi a
iss is sip pi Lo ui si an N a ew M ex W ic o es tV irg in ia Ar ka ns as Te xa s Al ab am a O kl ah om So ut a h C ar ol in a
M
Percent of Children under 18 Below Poverty
Place
Series1
Other Types of Charts
There are 14 different types of Charts
Column Bar Line Pie Scatter Surface Cylinder, Cone, and Pyramid
Bar
Percent of Children under 18 years Below Poverty Level in the Past 12 Months
South Carolina Oklahoma Alabama Texas Arkansas Place West Virginia New Mexico Louisiana Mississippi District of Columbia 0 5 10 15 20 25 30 35 Place
Line
Percent of Children under 18 years Below Poverty Level in the Past 12 Months
35 30 25 20 15 10 5 0 Place
ia Ar ka ns as
fC ol u
Lo ui si
W es tV
iss
Al
Ne w
O kl
Di s
tr i ct o
Place
So ut h
M
Ca ro lin a
an a
o
Te xa s
a ab am
a
ip pi
M ex ic
m bi
ah om
iss
irg in
a
Pie
Married-couple family: Male householder, no wife present: Female householder, no husband present:
Pie
Example
Scatter
Residuals X-Y graphs
Running Regression in Excel
Very limited Tool
Add-Ins….
Running Regression in Excel
Tools
Data Analysis
Running Regression in Excel
Y-range put in your dependent variables values
Must be a column Same with X-range
Ask for output in new workbook Ask for residuals
Scatter
6
4
2
0 0 1 2 3 4 5 6 7
Series1
-2
-4
-6
Cylinder, Cone, and Pyramid
Percent of Children under 18 Below Poverty
35 30 25 Percent 20 15 10 5 0 Series1
District of Columbia
Louisiana
West Virginia
Texas
Place
Oklahoma
Combination
Combination
4.50% 4.00% 3.50% 53,000 3.00% 2.50% 2.00% 1.50% 50,000 1.00% 0.50% 0.00% 1996 1997 1998 1999 2000 2001 2002 2003 49,000 52,000 Unmarried HH Percent of Births to Unmarried Mothers 55,000
54,000
51,000
48,000
Population Pyramid
85 and over 80-84 75-79 70-74 65-69 60-64 55-59 50-54 45-49 40-44 35-39 30-34 25-29 20-24 15-19 10-14 5-9 under 5 5 4 3 2 1 0 1 2 3 4 5
How to Fix it
Double click on the vertical axis Pick Format Axis. Click Patterns tabs→ Major tick=None →Minor tick=None →Tick mark labels= Low Ok
How to Fix it
Double click on the horizontal axis Pick Format Axis. Click Number tabs→ Category = Custom →Type = 0 Ok
How to Fix it
Click on the Bars Go to Options Overlap=100 Type=0 OK
Getting the Cart into Word
Right Click Copy Edit Paste Special
Paste Special
Getting your Chart into Power Point
Other CFDR Help
Come on down CFDR: Bowling Green State University