Skip to main content
Statistics LibreTexts

2.4: Using Excel to Create Graphs

  • Page ID
    22226
  • \( \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}}\)

    It can be difficult to be perfectly precise when creating graphs by hand. Also, it can be very time consuming if presented with a large amount of data. Technology can be used instead to create professional and precise graphs, and many pieces of technology also do much of the calculation, enabling the use of much larger amounts of data.

    In this section, you will learn about several of the most common types of graphs and how to create them in Excel.

    The first step will always be to summarize the data into a frequency distribution or a grouped frequency distribution (as appropriate). You can do this by hand, or in Excel sorting the data then using the \(= \text{COUNT}()\) function, or to count the items automatically, use Pivot tables or the \(= \text{COUNTIF}()\) function.

    Different kinds of graphs are best used for different kinds of data. Bar Graphs and Pie Charts are used for categorical variables. Whereas Histograms and Frequency Polygons are more generally used for numerical variables.

    Bar Graphs

    Bar graphs are regularly used with categorical data to compare the frequency of different categories. However, they can also be used with numerical data as well.

    Example \(\PageIndex{1}\)

    Twenty people were asked their favorite colors and their responses are shown below. Construct a bar graph in Excel that shows the frequency of each color.

    yellow blue red red blue
    green pink red pink purple
    yellow orange blue red pink
    purple blue red green purple

    Solution

    Enter the data into a single column in Excel, each color should be in its own cell.

    Create a frequency distribution. Each color will be a category. Then count up how many times that color appears. If you want, you can automatically do this in Excel using a pivot table or using the \(= \text{COUNTIF}()\) function. Another great option is to sort data entered already in Excel so it is easier to count.

    Color Frequency
    blue 4
    green 2
    orange 1
    pink 3
    purple 3
    red 5
    yellow 2

    Enter the frequency table into Excel. At this point, the top of your spreadsheet may look something like this.

    clipboard_ed92acc3a7a44579bc4a266a34bb1a02f.png
    Figure \(\PageIndex{1}\): The Raw Data is in Column A. The Sorted Data is in Column C. The frequency distribution is in Columns E and F. (Copyright; Katherine Bowe via ScreenCapture)

    Then highlight the frequency distribution, and go to Insert, then in the Charts section click to Insert a Bar or Column Chart.

    clipboard_e2734be43750ec8ca6ec100b9d07a671f.png
    Figure \(\PageIndex{2}\): The frequency distribution is highlighted, Insert at the top has been selected, and the button to push is darkened. A description of that button has popped up. (Copyright; Katherine Bowe via Screen Capture)

    Once that button has been selected, this is the option menu that pops up.

    clipboard_ea2801aa0920239f7a16ee56a484b34ee.png
    Figure \(\PageIndex{3}\): Menu for creating a bar graph. (Copyright; Katherine Bowe via Screen Capture)

    The top three options under 2-D Bar are the most common to use. The top left one, Clustered Column, will give a basic bar chart or a grouped bar chart if you're using more than one variable. The top middle one, Stacked Column, is used only if you're using more than one variable, it shows the bars in a stacked format. the top right one, 100% Stacked Column, is used only if you're using more than one variable, it shows stacked bars with sections given as a percent to compare parts of a whole.

    Occasionally, the 3-D Bar options are used for effect. You should typically use the vertical column options instead of the horizontal options.

    Choose the Clustered Column for this example and out pops our graph.

    clipboard_e09950f0908cd5048467520ad192ce9b9.png
    Figure \(\PageIndex{4}\): Bar graph showing frequency of favorite colors. (Copyright; Katherine Bowe via Excel)

    Many options can be edited such as the title, the color of the bars, axis descriptions, etc. The default title (if you don't highlight the headings in your frequency distribution) is "Chart Title" which should always be changed to something more meaningful.

    Pareto Graphs

    Pareto graphs are a special kind of bar chart that order the bars largest to smallest.

    Example \(\PageIndex{2}\)

    Use the same data from the last example to create a Pareto Graph in Excel.

    Solution

    Just like in the previous example, you need to start with a frequency distribution. But this time, you need to sort the frequency distribution by frequency.

    Make sure to highlight the entire frequency distribution, not just the frequency column so that the correct frequencies stay with the correct colors.

    On the Home ribbon, select Sort & Filter, then Custom Sort.

    clipboard_e9bb14848f964eba59a8d712ed194bdc1.png
    Figure \(\PageIndex{5}\): Change the Sort by to Frequency and the Order to Largest to Smallest. (Copyright; Katherine Bowe via Screen Capture)

    Then create a bar chart using the new sorted frequency distribution, following the same steps from the previous example.

    clipboard_e87dbb27a9e36672387a3d53eb0da5a1e.png
    Figure \(\PageIndex{6}\): Pareto graph showing frequency of favorite colors. (Copyright; Katherine Bowe via Excel)

    Always be sure to have a more descriptive title than "Chart title".

    Pie Charts

    Pie charts display data in a circle, with "pie" slices for each category. For Pie charts to work, the data must total 100% so the entire pie is filled. The relative size of the slices display the percent of each category out of the whole.

    Example \(\PageIndex{3}\)

    Use the same data from the first example to create a Pie Chart in Excel.

    Solution

    Begin by creating a frequency distribution of the data in Excel, just like in Example 1.

    Then highlight the frequency distribution, and go to Insert, then in the Charts section click to Insert Pie or Doughnut Charts.

    clipboard_e3f42341aff0fefd5c765d2803d212103.png
    Figure \(\PageIndex{7}\): The frequency distribution is highlighted, Insert at the top has been selected, and the button to push is darkened. A description of that button has popped up. (Copyright; Katherine Bowe via Screen Capture)

    Once that button has been selected, this is the option menu that pops up.

    clipboard_e4999f27a4e618abaf353e57b3cbc8df2.png
    Figure \(\PageIndex{8}\): Menu for creating a pie chart. (Copyright; Katherine Bowe via Screen Capture)

    The top left option, Pie, will give a basic pie chart.

    The Pie of Pie or Bar of Pie options could be used to highlight a particular section of the whole pie chart. This is used when there is a cluster of very small pie slices. Occasionally, the 3-D options or the Doughnut are used for effect.

    Choose the Pie for this example and out pops our graph.

    clipboard_e09af5beb3b51a43487f86e0c17d97a97.png
    Figure \(\PageIndex{9}\): Pie chart showing percent of favorite colors. (Copyright; Katherine Bowe via Excel)

    Always be sure to have a more descriptive title than "Chart title".

    Pie charts are limited because they don't show differences between similarly sized slices. For example, in the above chart, it's difficult to see the difference between the sizes of blue, pink, purple, and red since they're close in size. Excel does give the option to label the size of the slices, which is a good option if this issue occurs.

    Histograms

    Histograms are used for numerical data to compare the frequency of different values or classes of values. Histograms show a graphical view of the overall shape of a distribution. Histograms are very similar to bar charts, but should be displayed without space between the bars. Bar charts are mostly used for categorical data, whereas histograms are only used for numerical data.

    Example \(\PageIndex{4}\)

    Twenty Elementary Statistics students were asked their heights and their responses are shown below in inches. Construct a histogram in Excel that shows the frequency distribution of the heights.

    72 67 63 76 67
    61 70 74 71 75
    82 74 77 63 66
    76 76 72 75 73

    Solution

    Start the same way as creating a bar chart.

    Enter the data into Excel, each number in its own cell. Create a grouped frequency distribution. We need to use a grouped frequency distribution (where there are ranges of numbers instead of individual numbers) because there are too many individual numbers. The idea is to summarize the data, not show the exact same data in graphical form. Decide on the classes, ensure the class sizes are the same for all the classes, then count up how many times that number occurs. To make it easier, sort the data, then use the \(=\text{COUNT}()\) function.

    After creating the frequency distribution in Excel, your spreadsheet may look something like this.

    clipboard_e39e0bbf1af5da629424d0f4ae150b831.png
    Figure \(\PageIndex{10}\): A grouped frequency distribution of the data in Excel. (Copyright; Katherine Bowe via Excel)

    Highlight the frequency distribution in Excel, go to Insert, then in the Charts section click Insert a Bar or Column Chart, then choose the Clustered Column. This creates a bar chart of this data.

    clipboard_e89efba88a7fc831d73a9eb552e79b2eb.png
    Figure \(\PageIndex{11}\): A bar chart of the data. (Copyright; Katherine Bowe via Excel)

    This is not a histogram. To make it a histogram right click on the graph, then choose Format Chart Area from the options.

    clipboard_ed2ac42c37318b9758832ff735332ae81.png
    Figure \(\PageIndex{12}\): Choose Format Chart Area on the option list. (Copyright; Katherine Bowe via Excel)

    Click the down arrow next to Chart Options, then choose Series "Frequency". It's called Frequency since that's what I named the column containing the frequencies. Yours may be called Series "...", where the dots could be something else.

    clipboard_e6985b088c5bc94570410477584e248d3.png
    Figure \(\PageIndex{13}\): Choose Series "Frequency". (Copyright; Katherine Bowe via Excel)

    Click on the button that looks like a bar chart, then change the Gap Width to 0%.

    clipboard_e047a25bd82d342488844bdd8abcfd86f.png
    Figure \(\PageIndex{14}\): Change the Gap Width to 0%. (Copyright; Katherine Bowe via Excel)

    This gives us a correct histogram.

    clipboard_ea412053fa54f2754fa5108dd0a52f7f1.png
    Figure \(\PageIndex{15}\): Histogram of the data. (Copyright; Katherine Bowe via Excel)

    Excel does offer an option to automatically create a histogram, which is not recommended. If you use the automatic option, Excel overrides the classes you choose. It also displays the classes in interval notation, which is not the best option for readability. Here is the graph created using the automatic histogram function.

    clipboard_e00af3ee1b23798931d475bb30694d537.png
    Figure \(\PageIndex{16}\): Histogram created using Excel's automatic histogram option. (Copyright; Katherine Bowe via Excel)

    The resulting graph is not as informative and will not be considered correct.

    There are adjustments that can be made in Excel to fix it, but I've found it much easier to create histograms by starting with a bar chart first then changing the gap width rather than attempting to fix an automatic histogram.

    Frequency Polygons

    Frequency polygons show the overall shape of a distribution, similarly to histograms. However, they are especially useful for comparing different variables on the same graph. These can also be called Line Graphs, or if you are looking at a relationship over time, then these are used as a Time Series graph.

    Example \(\PageIndex{5}\)

    Makila and Jannai are in a club that fundraises money by selling cookies. The sell lasts five weeks. Below is shown how many boxes they each sold each week. Create a line graph depicting this data.

    Makila Jannai
    Week 1 26 14
    Week 2 40 24
    Week 3 22 32
    Week 4 63 36
    Week 5 78 47

    Solution

    Start by entering the data into Excel. After entering the data, it may look something like this.

    clipboard_e5c7894e9ad884391dda6903fca057c40.png
    Figure \(\PageIndex{17}\): The data entered into Excel. (Copyright; Katherine Bowe via Excel)

    Highlight all the data entered into Excel, go to Insert, then click the Insert Line or Area Chart option.

    clipboard_e0f101f70b6ec17a414e34fa4ad4f6311.png
    Figure \(\PageIndex{18}\): Insert Line or Area Chart in Excel. (Copyright; Katherine Bowe via Excel)

    Choose the top left option for a Line chart.

    clipboard_ec9da0379ea81e6ad0fad8bbecdfa0c42.png
    Figure \(\PageIndex{19}\): Line chart option in Excel. (Copyright; Katherine Bowe via Excel)

    This will create a Line chart, showing both people's sales by week.

    clipboard_e42c62a9f4fc761ec3d68472a1b1fa2e0.png
    Figure \(\PageIndex{20}\): Line Chart in Excel. (Copyright; Katherine Bowe via Excel)

    This chart has a seperate line for each person and a meaningful title. It also has a key that shows which color line goes with which person.

    From the chart, Makila made more sales overall, though less in week 3 than Jannai. Makila's sales went up and down, but Jannai's went steadily up each week.

    Using Excel to create graphs can give us precise information in an easy to read format. It can be a lot faster and more precise than creating graphs by hand, and it allows the possibility of using large amounts of data.

    Data will always need to be organized into a frequency distribution or a grouped frequency distribution first. Graphs should not be created based on raw data.


    2.4: Using Excel to Create Graphs is shared under a CC BY license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?