How to Make a Bar Graph in Excel

 

1.      First, calculate the mean (average) and standard error for all the bars you will be drawing on the graph.  See other Excel guide sheets for these instructions.  If you would like specific X-axis labels (words or numbers that you wish to appear under the bars), type these into a blank column or row on the spreadsheet; be sure to have the same spacing that occurs in your row or column of means. 

2.      You can begin by using the cursor to highlight all the means you wish to graph.

3.      Now click on the picture of a graph in the toolbar at the top.  (Alternatively, you may go to “Insert – Chart” from the drop-down menus.)

4.      Step 1 of the Chart Wizard will appear.  Pick the picture & description that best matches the type of graph you are trying to draw.  Remember that you can use the “Press & Hold to View Sample” button to help you figure out if this is what you want.  A bar graph is called a “Column Chart” in Excel; you will probably want a “Clustered Column Chart” – the first choice.  Select that & click “Next”

5.      Step 2 of the Chart Wizard has 2 tabs, the “Data Range” & “Series” tabs.

a.      The “Data Range” tab asks you to pick your data range.  If you did not originally highlight the means, you may use the selection tool to do so from this screen.

b.      Click on the “Series” tab.

                                                  i.      In the space labeled “Name” you can type what you would like to be the title of the graph or of this set of bars in the key (called series legend by Excel).

                                                ii.      In the space labeled “Category (x) axis labels”, you can highlight a data range that contains the words or numbers that you would like to appear under each bar.  Click on the picture of the spreadsheet beside the blank to allow you to highlight the range on the spreadsheet.  Hit “enter”, then click “Next”.  Don’t worry if it doesn’t look quite right.  We’ll fix it later it necessary.

6.      Step 3 of the Chart Wizard has several different tabs.  You will want to go through most of them as you make the graph look the way you want.

a.      Titles tab –

                                                  i.      Chart Title – This is the graph title that will appear above the graph by default.  There may already be a title if you entered one under step 2.

                                                ii.      Category X axis – enter the X axis label here.  Don’t forget to include units if appropriate.

                                              iii.      Value Y axis – enter the Y axis label here.  Don’t forget to include units in parentheses if appropriate.

b.      Axes tab – You can change the type of scaling on X axis through this type and alter the appearance of the Y-axis as well.  Generally, you will not need to change the default settings on this.

c.      Gridlines tab – You can check the boxes on this tab to make gridlines appear or disappear.  You may alter the appearance of your graphs as you wish.  Do avoid making the graphs too cluttered.

d.      Legend tab – You can check to whether or not you wish to have a key (series legend) appear; you can also check boxes to indicate where on the page you would like the key to appear.  Note that as you check or uncheck boxes, you can see what the result would be in the graph picture.

e.      Data Labels tab – You can check boxes to show specific data labels and values.  Play with the options.  Remember, less clutter is usually better.

f.        Data Table tab – You can play with showing the data table on the graph.  Since you will be turning in the entire spreadsheet, my preference would be less clutter, i.e. don’t put the data table on the graph.

g.      Hit “Next” when you are done making your choices on step 3.

7.      Step 4 of the chart wizard simply asks where you would wish for the graph to appear.  You can check for the graph to appear by itself on a separate sheet in the workbook, or you can check for the graph to appear as an object embedded in the current sheet of the notebook, next to the data.  I am comfortable with either choice; go wild.

8.      There are many formatting menus that you can reach to make your graph look exactly as you would like.  Additionally, you can go back and change any of the selected options, data, etc., without starting over on the graph.  As a general rule of thumb, place the cursor on the item you would like to change, and double-click the mouse to immediately open a menu, or right-click the mouse for a set of menu options that you can choose from.  Make your graph as individual as you wish, however, remember that it needs to communicate effectively, e.g. I need to be able to see the tops & bottoms of the error bars.  Please use the Excel Help function or just play to find all the options available.

9.      Add the error bars to your graph as follows.

a.      Place the cursor directly on the top line of one of your bars, and right-click the mouse.  One of the choices should, “Format Data Series”.  One of the tabs reads “Y error bars”; click on that tab to bring it to the front.

b.      Choose the “Both” option.

c.      Choose “Custom” and use the spreadsheet picture to highlight the standard errors that you calculated from the spreadsheet.  You will have to select the standard errors twice; once for “+” and once for the “-“ options.  (NOTE:  The standard error option shown on this tab calculates standard error of the population, not standard error of the mean.  It is a much larger number than standard error of the mean and is not the number I would like for you to graph.)