2.4: Using Excel to Create Graphs
- Page ID
- 22226
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.
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.
Then highlight the frequency distribution, and go to Insert, then in the Charts section click to Insert a Bar or Column Chart.
Once that button has been selected, this is the option menu that pops up.
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.
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.
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.
Then create a bar chart using the new sorted frequency distribution, following the same steps from the previous example.
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.
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.
Once that button has been selected, this is the option menu that pops up.
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.
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.
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.
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.
This is not a histogram. To make it a histogram right click on the graph, then choose Format Chart Area from the options.
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.
Click on the button that looks like a bar chart, then change the Gap Width to 0%.
This gives us a correct histogram.
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.
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.
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.
Highlight all the data entered into Excel, go to Insert, then click the Insert Line or Area Chart option.
Choose the top left option for a Line chart.
This will create a Line chart, showing both people's sales by week.
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.