Download Producing graphs in Excel 2010: and more Slides MS Microsoft Excel skills in PDF only on Docsity!
Producing graphs in Excel 2010:
To produce a simple bar chart:
- Enter the data. Here, I'm going to produce a graph that contains a set of bars. It will show mean annual expenditure on beer, pizza, kebabs and shoes for men. That gives me four means to display. For each mean, I'm also going to show the associated standard deviation. Here's how to lay out the data: just pick some empty cells anywhere in the spreadsheet.
commodity beer pizza kebabs shoes mean annual expenditure men 400 300 220 70
standard deviations men 70 50 60 10 Uuww` EV
- By clicking and dragging, highlight just the means and their labels ( beer, pizza, etc).
commodity beer pizza kebabs shoes mean annual expenditure men 400 300 220 70
standard deviations men 70 50 60 10
- Click on the tab labelled "Insert". In the area that deals with " Charts" , select "Column". Click on the leftmost graph icon in the row that's headed "2D column".
This is the graph you should get:
To add the standard deviation to each mean:
Click once on the chart to select the whole thing (make sure you don't click on the inner part of the chart because that will select just the axes and bars). The tabs at the top will change to show "Chart tools". Select the "Layout" tab.
Click on "Error Bars" , and then click on "More Error Bars Options..." at the bottom.
0
50
100
150
200
250
300
350
400
450
beer pizza kebabs shoes
Series
(a) Click on " custom ", then click on " Specify Value ". A dialog box labelled " Custom Error Bars " will appear.
(b) Click on the box underneath " Positive Error Value ", to select it. (You may need to drag the " Format Error Bars " dialog box out of the way of your data before doing this). Click and drag on the cells that contain the standard deviations for the men. Then click on the box underneath " Negative Error Value " to select it, and then click and drag on the same range of cells that you just entered into the "Positive Error Value" box.
Essentially you are telling Excel exactly how to draw the error bars. You want each one to be a particular mean plus and minus its associated standard deviation. Excel remembers the range of cells that contain the means for the men, so by entering the range of cells that contain the men's standard deviation values into the "Positive error value" and "Negative error value" boxes, you are supplying Excel with the values that it will need to add and subtract from each mean in order to produce the correct error bars.
This is what the finished graph should look like:
To produce a more complicated bar chart:
- Enter the data. Here, I'm going to produce a graph that contains two sets of bars. One set will show mean annual expenditure on beer, pizza, kebabs and shoes for men. The other set will show the same data for women. That gives me eight means to display. For each mean, I'm also going to show the associated standard deviation. Here's how to lay out the data: just pick some empty cells anywhere in the spreadsheet.
commodity beer pizza kebabs shoes mean annual expenditure men 400 300 220 70 women 200 260 30 200
standard deviations men 70 50 60 10 women 100 60 30 50
- By clicking and dragging, highlight just the means and their labels (men, women, beer, pizza, etc).
commodity beer pizza kebabs shoes mean annual expenditure men 400 300 220 70 women 200 260 30 200
standard deviations men 70 50 60 10 women 100 60 30 50
- Click on the tab labelled "Insert". In the area that deals with " Charts" , select "Column". Click on the leftmost graph icon in the row that's headed "2D column".
0
50
100
150
200
250
300
350
400
450
500
beer pizza kebabs shoes
Mean annual expenditiure (+/- 1 SD)
Commodity
This is the graph you should get:
To add the standard deviation to each mean:
Click once on the chart to select the whole thing (make sure you don't click on the inner part of the chart because that will select just the axes and bars). The tabs at the top will change to show "Chart tools". Select the "Layout" tab. Click on "Error Bars" , and then click on "More Error Bars Options..." at the bottom.
The following dialog box pops up:
We have two "series" of data here, one series of means for men and one series for women. Click on "OK" to select the "men" series to begin with.
Follow the same procedure as before: a) Click on " custom ", then click on " Specify Value ". A dialog box labelled " Custom Error Bars " will appear. (b) Click on the box underneath " Positive Error Value ", to select it. Click and drag on the cells that contain the standard deviations for the men.
0
50
100
150
200
250
300
350
400
450
beer pizza kebabs shoes
men women
Let's suppose that I had ten men and ten women in this study. Have a go at working out the standard error for women using the same formula as before. You should get these values:
Standard errors men 22.13594 15.81139 18.97367 3. women 31.62278 18.97367 9.486833 15.
Have a go at making a figure that conforms to APA format, with error bars containing the standard error. Your graph should end up looking like this:
0
50
100
150
200
250
300
350
400
450
beer pizza kebabs shoes
Mean annual expenditure (+/- 1 SEM)
Commodity
men women
