Skip to main content
\(\require{cancel}\newcommand\degree[0]{^{\circ}} \newcommand\Ccancel[2][black]{\renewcommand\CancelColor{\color{#1}}\cancel{#2}} \newcommand{\alert}[1]{\boldsymbol{\color{magenta}{#1}}} \newcommand{\blert}[1]{\boldsymbol{\color{blue}{#1}}} \newcommand{\bluetext}[1]{\color{blue}{#1}} \delimitershortfall-1sp \newcommand\abs[1]{\left|#1\right|} \newcommand{\lt}{<} \newcommand{\gt}{>} \newcommand{\amp}{&} \)

SectionA.1How to make a bar graph in Microsoft Excel

This tutorial uses the following data set. It contains the scores on a 5 point quiz for a class of 18 students.

Score 5 4 3 2 1 0
Count 6 2 3 0 2 5
TableA.1Number of students who earned each score
Part 1: Enter the data
  1. Write headings VALUE, COUNT, PERCENT in A1, B1, C1.

  2. Enter the values, taking advantage of the fact that the values all differ by the same amount.

    1. Write the score 0 in A2.
    2. Write =A2+1 in A3.
    3. Click the lower-right corner of cell A3 and down to enter scores 2 through 5 in A4 through A7.
  3. Write SUM in A9.
  4. Enter the counts in B2 through B7.
  5. Sum up the counts by writing =sum(B2:B7) (without the quotation marks) in B9.
Part 2: Make a bar graph of counts
  1. Highlight the values and counts (A2 through B7).
  2. Click Insert and click the icon for Recommended Charts.
  3. You should see one labeled Clustered Column and showing one set of columns with the correct labels and data values. Click OK.
  4. Change the generic title to a meaningful one.
Part 3: Add data for percentages
  1. Compute the percentage for the first value by writing =100*B2/B9 (without the quotation marks) in C2.

  2. We need to apply the same formula several times. Here is a way to make that easier.

    1. Attempt to repeat the formula for the other values by dragging C2 down to C7.
    2. To see what went wrong, look at the formula that appeared in C3. What should the correct formula be?
    3. Compute the correct percentages by changing the formula in C2 to =100*B2/B$9 and repeating part (b).
    4. What does the $ do?
  3. Make column C easier to read by highlighting the whole column and clicking Format, Format Cells, Number from the Cells menu.
Part 4: Make a bar graph of percentages

  1. Transfer the value and percentage data to new adjacent columns.

    1. Highlight A1 through A7. Click Ctrl-C. Put the cursor in E1 and click Ctrl-V.
    2. Highlight C1 through C7. Click Ctrl-C. Put the cursor in F1.
    3. Click Paste at the far left of the menu bar. Under Paste Values, chose Values and Number Formatting.
  2. Make the bar graph by repeating part 2 using columns E and F instead of A and B.