Graphing Tutorial: HISTOGRAM (bar graph)
The histogram or bar graph is an excellent graphic for illustrating amounts,
distribution and variation of data. The average mass of oysters found on
three different beaches, the abundance and diversity of species on a beach
or the distribution of eel grass coverage along a transect (survey line) are all
excellent opportunities for the use of a histogram.
In the example here we will construct a histogram illustrating the abundance
(population size) of four taxa (mussels, chitons, barnacles and limpets)
sampled at three different sites or ‘quadrats’ along a beach.
Graphs are meant to communicate patterns and explore relationships. As you
follow along with this tutorial, think about how the choices made in constructing a
graph help reveal different relationships.
How might you set up a graph to be most useful in answering questions you may
have about your data?
How can you use labels, titles and legends to help others understand your work?
Pay attention as you proceed, as there is always more than one way to construct
and convey information. This tutorial is designed to give you some simple tools
in MS Excel to help you explore and illustrate scientific phenomena using your
own logic, reasoning and creativity.
Examples will be given throughout this lesson. It is recommended that you open
a second window and mimic/follow the directions given.
This is designed for people who have very little experience with Excel and its
graphing/chart making options.
Begin by opening a new workbook in MS Excel and copying the table
The program’s Chart Wizard is fairly simple to use if you are consistent
with how you make your tables. It is recommended to follow the direc-
tions closely for each step, and then return and experiment later.
We will not be able to cover all aspects in this tutorial.
NOTE: this table represents a portion of the data we will illustrate with
our final graph. It is easier to learn for now with a smaller data set.
Highlight only the cells that contain the data and the column headings
and click on the Chart Wizard button on the tool bar.
Another option is to choose ‘Chart...’ from the ‘Insert’ drop down menu.
The Chart Wizard window will appear. Excel refers to a Histogram as
‘Column’ in the chart type box. You can highlight this and click next, or simply
double click on the indicated graphic. You should come back and explore the
other options at a later time.
The wizard will display a sample graph using the data you highlighted. Excel
organizes the X-axis based upon columns or rows.
See the ‘Series in:’ command below.
Toggle back and forth between ‘Series in: Columns’ and ‘Series in: Rows’. No-
tice what happens to the legend on the right and the labels on the X-axis.
How do the two options present the material differently?
Organize your graph in ‘Columns’ and click next.
The ‘Chart Options’ window allows you to change the appearance and orga-
nization of your graph. These can also be changed on the graph itself after
you exit the Chart Wizard, however it’s often easier to set them up here.
You should be on the ‘Titles’ tab.
Make sure you choose a Title that describes the data set in enough detail so
that your graph can stand without any other accompanying text or explana-
Include units and scale where appropriatle. Abundance implies # of single
individuals, if the scale was in thousands of individuals, this would have to
be shown in the Y-axis label, or the Title of the graph.
Now click on the Axes tab. This allows you to arrange the units and increment
labels on the X and Y axes.
Try turning the different options off and on. When might each of these options be ap-
Click on the ‘Gridelines’ tab. This window allows you to manipulate the reso-
lution of the grid itself. Are you trying to distinguish between values that are
close together? Or looking for general trends? Try adding ‘Minor gridlines’ to
the Y Axis and see what happens
The actual chart will appear larger and less cluttered than the preview here,
however simplicity should always be a priority. In the final chart, you will be
able to fade the color of the gridlines, but again, focusing on simplicity and
clarity here is of utmost importance.
Now navigate to the ‘Legend’ tab, this allows you to place your legend above,
below or beside your graph.
Experiment with this and note how the different orientation affects
how well the legend can be read.
Might there be advantages to different orientations? Or not having a legend
The ‘Data Labels’ tab allows you to directly label data points on your graph. In this case
the data point is the top of each ‘bar’. This may be especially usefull for graphs where
knowing the exact number value is important, or in situations where you do not want to
use a legend.
Experiment with these options, keeping in mind that the actual graph will be
larger and easier to read.
Also keep in mind the need for simplicity in a good graph.
The Data Table option provides an interesting variation that displays your raw
data below the graph.
Most of the time your data sets will be too large to use this function, and
will be better shared with a separate table.
MAKING YOUR CHART:
Navigate back through the Chart Options tabs and make your chart look like the example
below. Click ‘Next’.
You will have an option to place this chart on the Excel sheet where your data
is, or create it as a page (sheet) of its own.
For now, choose the ‘new sheet’ option and title it ‘West Point Beach’
Then click ‘Finish’
Your new graph should
appear as its own page
(or sheet) with a tab at
the bottom indicating
You can navigate back
to your original data
table by clicking on the
tab labeled ‘Sheet 1’.
Many aspects of your
graph can still be al-
tered. In fact, you will
probably want to play
with font size and place-
ment of the legend in
this window, as it is
easier to judge the clar-
ity of your final product.
CHANGING THE SIZE OF THE GRAPH: Click anywhere on the gray area of the
graph itself. The perimeter should become highlighted.
Now you can move the sides by clicking and dragging. Here we’ve moved the top
down to make room for a larger title.
CHANGING FONT DETAILS:
Double click on the title. A Format Chart Title box will appear. This allows
you to change the font itself, the size, style etc.
Go to the Size box and scroll to ‘24’
While the title is highlighted, you can click and drag to reposition it.
Any of the text on your graph can be repositioned in a similar manner.
Now change the font for the Data Label.
Double click on the ‘75’ above the barrnacle column and increase the font size to 12
Change the fonts for the other data labels and the X and Y axis labels to make your graph
look like the example below.
Next we will change the font size for the legend to make it easier to read.
First, however, we must make room.
Click on the gray area of the graph, then click and drag the right edge of the graph towards
Double click on the Legend and change the font size to ‘12’
Now reposition the legend closer to the graph itself.
Your finished Histogram should closely resemble the one below.
Figures and Graphs should strive to convey necessesary information without excess. At the
same time, a reader should be able to understand the basics of what a graph or chart repre-
sents without the immediate context of the paper or lab report it is a part of.
How did we do?
What more information might be worth including in the title? The X and Y axis labels?
Is there any extraneous or unexplained information?
NOTE: we did not manipulate color in this example. Color is often essential for organizing
complicated graphics, and is fairly easy to do. Double click on any bar (column) or on one of
the gridlines and a box will appear that allows you to manipulate color for that object.
The X axis labels may seem reduntant- Quadrat 1 and Quadrat- however this will become
more clear when we construct a larger graph.
In reality, one of the two labels would be removed by double clicking and pressing ‘delete’.
WORKING WITH A LARGER DATA SET:
Navigate back to your original worksheet by clicking on the ‘Sheet 1’ tab.
Enter the following data table.
Highlight the the cells.
Click on the Chart Wizard button
Again, select the histogram (bar or column graph).
Again, you may choose to organize the X-axis (series) by columns or rows.
Toggle back and forth between these two. What are the advantages of the
different choices for organization?
Which is better for comparing the distribution of a single organism (say mussels)
from quadrat to quadrat?
Which is better for getting a general picture of taxa distribution for each quadrat?
For our purposes, choose to organize your graph by rows.
Experiment with different Chart Options. What information do you think is nec-
essary? What will make the graph look busy?
Try to make your final graph resemble the one above.
Remember, a graph is not only a tool for presenting information, but for exploring your
data set as well.
Be prepared to experiment with your graphs while you are writing a lab report to try find
additional insights and patterns. If a pattern is not apparent in your graph, try reorga-
nizing or comparing different data sets and see what you can find.
For example, in the above graph, you should be able to see which Quadrat is distinctly
different than the other two (Quadrat 3), and which Quatrad appears to have the least
amount of variation (again, Quadrat 3).
Knowledge about the position of the three Quadrats, say closer or farther from the low
tide line, would allow you to draw further conclusions from these patterns.
Also, the use of simple statistics (standard error and t-test) may influence your ability to
find patterns in a particular graph. These techniques will be covered later in lab.