Skip to main content
Statistics LibreTexts

1.22 Creating a Bar Chart and Frequency Table in Excel

  • Page ID
    22439
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    Step 1: Download the Excel Spreadsheet

    First, download the Excel spreadsheet below. 

    Creating a Bar Chart and Freq Table.xlsx

    Notice the data consist of values from 1 to 4.  The data is discrete meaning you can list all the values. 

    List of Data

    Step 2: List all the possible values

    To create a frequency table, starting in cell D1 enter the values 1, 2, 3, and 4.  

    List All Possible Values

    Step 3: Select the Data Analysis option

    Then click the Data tab on the Main Menu, and locate the Data Analysis option.  Click the Data Analysis option.

    Data Tab look for Data Analysis to the far right

    Step 4: Locate the Histogram option

    Select the Histogram option and click the OK button.

    Histogram option in the Data Analysis Tool

    Step 5: The Histogram options

    When the dialog box appears, fill in the information.  The data is in cells A1 thru A60; therefore, in the Input Range enter $A$1:$A$60.  Next, the list of possible values is in cells D1 thru D4.  Enter $D$1:$D$4 in the Bin Range.  Select the output range to start at G1; therefore, enter $G$1 for that option.  Finally, click the Chart Output to view the Bar chart.  Then hit the OK button.

    Histogram Dialog Menu

    Step 6: View the results

    When you click the OK button in the dialog box above, the data will be displayed on the Excel spreadsheet starting at cell G1.

    Bar Chart Output

     Step 7: Finishing the Frequency table

    The Data Analysis tool will create the Frequency column for you; however, you must complete the table to answer questions on the homework.  First, move the chart down.  To move it down, click on the edge of the chart, and drag it down to row 14.

    Move Bar Chart by clicking on edge of chart

    Cumulative Frequency

    In cell I1, enter "Cum Freq."  In cell I2, enter the formula =H2.  In cell I3, enter the formula = H2+I3.  Copy the formula in cell I3 down to I6.  You now have the Cumulative Frequencies for the table.

    Cumulative Frequency Column

    Relative Frequency

    In cell J1, enter "Rel Freq."  In cell J2, enter the formula = h2/sum($h$2:$h$6).  To format the value so it rounds to 4 decimal places, change the formula to =round(h2/sum($h$2:$h$6), 4), and copy the formula in cell J2 down to J6. 

     Cumulative Relative Frequency

    In cell K1, enter "Cum Rel Freq."  In cell K2, enter the formula = I2/sum($h$2:$h$6).  To format the vale so it rounds to 4 decimal places, change the formula to =round(I2/sum($h$2:$h$6), and copy the formula in cell K2 down to K6. 

    Step 8: Format the Bar Chart

    To format the Bar chart, click on the word Bin.  Then change it to a title that is representative of the values 1 - 4.  For this example, change the word to High School Class. 

    Screenshot of setting title of x axis

    Then change the Title name to Frequency of Student by class.

    Screenshot of setting the Bar Chart Title

    You can get rid of the Frequency label on the right-hand side by clicking the word Frequency and uncheck the box next to Legend.

    BarChartLegend.JPG

    Final Bar Chart

     


    1.22 Creating a Bar Chart and Frequency Table in Excel is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?