Creating Histograms, Box, Scatter & Dot Plots in Excel

Instructor: Bob Bruner

Bob is a software professional with 24 years in the industry. He has a bachelor's degree in Geology, and also has extensive experience in the Oil and Gas industry.

Microsoft Excel provides a number of easily accessible graph or chart options. In this lesson, we'll use an example to show you how to create a box, dot, and scatter plot and a historgram.

Collecting Data

In this lesson we'll look at the basic graphs and graphing options available in Microsoft Excel, created as chart objects directly in a spreadsheet. For this exercise, imagine that we've measured traffic on a commercial website used to sell hearing aids. We've gathered the ages of our visitors as they enter our site and tabulated the numbers of all those who asked for (yes) and those who did not ask for (no) a follow-up sales visit. Here's the raw data presented in tabular form:

AGE NO YES
18-32 13 0
33-47 31 5
48-62 56 16
63-77 60 77
78+ 78 92

Graphing Basics

To help us visualize the information in the table, we can use a number of easily accessible charts and graphs. The first step in creating a chart is to select all of the cells in the data. If you select column and row headings, Excel will use those headings for the relevant x and/or y axes. Skip any unwanted rows or columns to leave specific data out of the graph.

At this point you can either choose the ''Charts'' tab, if it's visible on your menu bar, or select ''Insert > Chart'' from the main list of menu options. Then select the basic type of chart or graph you want to create. Charts are collected under various menu categories, and each category provides multiple sub-options. Having selected the data and chart type, the graph will appear immediately on your spreadsheet, where it can be moved or resized as needed as you further refine the display.

Histograms

Let's start with a basic histogram. In Microsoft Excel, histograms are referred to as columns or bars, displayed with either a vertical or horizontal orientation. If you have more than one data object selected for comparison purposes, such as our ''Yes'' and ''No'' categories, each option will provide a slightly different view of the data. A clustered column will display each category of data side by side, which makes comparing relative data quite easy.

Here is a sample taken from our data:


Clustered Column Histogram
Clustered Column Histogram

There are two other basic histogram plots. The stacked column displays each bar on top of the others, rather than side by side. Unlike the clustered column, this tends to highlight the category totals, while also displaying each individual contribution. Notice that the y-scale automatically adjusts to fit the range of data being displayed.


Stacked Column Histogram
Stacked Column Histogram

Alternatively, you can display a 100% stacked column chart. As we can see in our data, stacked columns display percentages within each category, but do not provide any information about the relative values between columns.


Percent Column Histogram
Percent Column Histogram

Line Plot

The same data can be displayed as a series of lines using a line plot, which is useful for highlighting trends in data. Simply choose one of the line chart options with the same data selected from the table. In the example below, you'll see a line with markers at the data point locations. In this case, it's evident that something about the data seems significant in the middle, where the two lines cross.


Line Plot
Line Plot

It's useful at this point to note that once a chart is created, hovering over any portion of the graph and selecting the right mouse button brings up relevant options for the type of plot or actual area of focus you've selected. There are many basic options available from the menu, such as adding and removing data or adding labels to all the data points. Use the ''Format Data Series'' option to bring up another menu that allows you to modify many display options, such as colors, shapes, and fills. If you wish to change the way the axes or legends are formatted, be sure to focus on the object itself to get the correct, context sensitive options.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support