Charting with Excel

Alexis Furuichi Data and Statistical Services Social Science Reference Center Firestone Library [email protected]

[email protected]

Agenda

• Graphing –Basic graphing –Tips & tricks –Pivot tables & charts –Applications • Resources

Basic Graphing

• Highlight data to be graphed (include the labels) • Click Chart Wizard icon (standard tool bar) or Click “Insert”, then “Chart”

Initial Setting/ Modifying

• Chart Type – Select the type of chart • Source Data – Define the cells that contain data for charting • Chart Options – Define titles, axis, gridlines, legend, data labels, & data table • Chart Location – Define where you want to insert the chart

Formatting

• Axis (X and Y) – patterns, scale, font, number, & alignment • Gridlines – patterns & scale • Data Series – patterns, axis, Y error bars, data labels, series order, & options • Legend – patterns, font, placement • Plot Area – patterns

Tips and Tricks

• Saving customized formats • Text v.s. Numbers – Formatted text versus input text • Saving graphs as pictures • Format cells to show numbers in thousands

Pivot Tables and Charts

• Summary table – good for more than one variable • Starting to make pivot tables – Highlight data to create pivot tables (include the labels) – Click “Data”, then “PivotTable and PivotChart Report”

Applications

• Combination graph • Add-on Data Analysis Tool: Histogram • Adding a trend/regression line • Graph with special points • Adding a mean line • Box and Whisker Plots • Dynamic Chart • Conditional Charts

Conbination Graph

Price ($)

12000

Auto Price and MPG, 1978

10000 8000 6000 4000 2000 0 A M C C on A co M rd C P ac A er M C B S ui pi ck rit C B en ui tu ck ry E B le ui ct ck ra L eS ab B ui re ck O B ui pe ck l R B eg ui al ck R B ui iv ie ck ra S ky la rk 5 0 20 15 10

MPG

30 25 price mpg http://peltiertech.com/Excel/Charts/BarLineCombo.html

Histogram

Auto Price 1978

40 30 20 10 0 3291 4868 6445 8022 9599 11175 12752 14329 More http://peltiertech.com/Excel/Charts/Histograms.html

Graph with a Regression Line

Price and MPG, 1978 MPG

45 40 35 30 25 20 15 10 5 0 0 y = -0.0009x + 26.964

2 4 6 8 10 12 14 16 18

Price ($1000)

http://www.mrexcel.com/tip067.shtml

Graph with Special Points

Auto Price, 1978

18 16 14 12 10 8 6 4 2 0 1 5 9 13 17 21 25 29 33 37 41 45 49 53 57 61 65 69 73 http://peltiertech.com/Excel/Charts/PointAtPoints.html

Graph with a Mean Line

m pg

45 40 35 30 25 20 15 10 5 0

autos

http://peltiertech.com/Excel/Charts/RunChtLines.html

27 21 16

Box and Whisker Plot

18000 16000 14000 12000 10000 8000 6000 4000 2000 0 price http://peltiertech.com/Excel/Charts/BoxWhisker.html

Dynamic Chart

Auto Price 12000 10000 8000 6000 4000 2000 0 P ac er C on co rd S pi rit 50 00 Fo x 32 0i E le ct ra R iv ie ra Le Sa br e S ky la rk http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

Conditional Charts

Auto Price, 1978 price ($1000)

18 16 14 12 10 8 6 4 2 0 1 3 5 7 9 11 13

autos

15 17 Between $0 and $4000 Between $5000 and $6000 19 21 Between $4000 and $5000 Between $6000 and $20000 23 25 http://peltiertech.com/Excel/Charts/ConditionalChart1.html

Resources

• Excel Tutorials - Microsoft Office Online http://office.microsoft.com/en-us/training • Trend/regression line http://www.mrexcel.com

• Various Excel Graphs http://peltiertech.com/Excel/Charts • Dynamic Chart http://www.j walk.com/ss/excel/usertips/charttips.htm