1.
1
Illustrating data – Making charts with Excel
Instructor Development Program of Dec 17, 2013 at the UC Berkeley Library
Jeffery Loo – jloo@berkeley.edu
Overview
1. Determine if a chart is needed
2. Pick the best chart for your data analysis and story
3. Design the chart for ease of reading and comprehension
4. Build the chart in Excel and embed in a document
Step 1. Determine if a chart is needed
Sometimes tables are the best format to present your data, and sometimes charts are better.
How do you know when to use a chart?1
Use a table
Use a chart
for ease of looking up values
to reveal relationships among the values
when precise values are required
when the message is contained in the shape of the
values
Activity: Table or chart, which would you select to represent the following data? Mark with an X.
Data
The birthdate of every student library employee
The rising and falling numbers of student library employees working in each
semester from 1995 to present
Important historical events in British Columbia and the year they occurred
The correlation between the number of hours worked by student library employees
and their academic success
Table
Chart

2.
2
Step 2. Pickthe bestchart for your data analysis and story
Picking the best chart begins with articulating the story you want to tell through your data.
What is your message? What is the relationship you want to display?
Quantitative messages
In fact, there are seven common quantitative messages you can express with charts.2 They differ in how
the separate values relate to one another.
Value comparison
comparing things in different categories (in no particular order)
Time-Series
showing changes in values over time
Ranking
showing the order of something by size or rank
Part-to-Whole
demonstrating how a part relates to the whole
Deviation
showing the difference from a standard reference
Distribution
showing the number of things in different categories
Correlation
showing that two or more measures are related (e.g., time spent studying
and academic success)

3.
3
Common chart types3
Histogram
Each rectangle represents a bin of
values/categories, and the area represents the
number of observations for each bin
Pie chart
Each slice of the pie represents a percentage value
of the whole pie
Relative numbers of native English speakers in the major
English-speaking countries of the world
Column/Bar chart
Rectangular bars are proportional to values that
they represent
Prisoners per 100,000 of the population of the country – 2005
Line chart
Data points are arranged in a sequence (e.g., time,
rank order) and they are connected by lines

7.
7
Chartanatomy6
1. Title
Rainfall in major cities of British
Columbia, December 2015
45
40
40
40
6. Gridlines
35
5. Data Labels
30
30
25
25
2. Legend
25
Rainfall
(mm) 20
15
Vancouver
Victoria
10
10
5
5
4. Scale
0
1-Dec
2-Dec
3-Dec
4-Dec
Date
5-Dec
6-Dec
7-Dec
3. Axis
1. Title
2. The Legenddistinguishes the variables in the chart by listing them and giving an example of their
appearance.
3. Axes are fixed reference lines for measurement coordinates. The horizontal axis is also known
as the x-axis; the vertical axis as the y-axis. Normally, the horizontal axis represents the
independent variable, which is the measure that the researcher/observer has control over in the
research design (e.g., time, date, category).
4. Scaleis the proportion and marking of the measurement on an axis. It is usually numerical or
categorical
5. Data labelsdescribe data points.
6. Grid lines visually align the data to facilitate reading.

8.
8
Step 3. Design the chart for ease of reading and comprehension
Keep charts simple for ease of comprehension7
1. Tell a story
Try telling one story per chart. Create separate charts for different narratives.
Write a title that articulates an analytical insight. Rather than “Books checked out by month”
try “More books checked out during the fall semester at the Library.”
2. Show the data
The dots, lines, and bars of your data should be prominent.
3. Use contrasting colors to distinguish groups, but use colors in moderation.
4. Use text labels to add detail and clarity
5. Help the reader read quickly
Prevent the reader from conducting any mental math. For example, separate stacked
columns; each part could have its own column.
Some perceptual tasks are easier than others. Use graphical features that optimize the
information transfer capability. Check out Cleveland’s Graphical Features Hierarchy.8
6. Remove design features that don’t explain the data.
Try removing elements and features like chart borders and gridlines, and then check
whetherthe chart is still comprehensible.
7. Explain the data by responding to journalistic questions
Label data or write a title that answers readers’ questions.Who made this chart? Who is this
data about? When was this chart made? What is the chart? What is the message? What is
the insight? How were the data collected? Where does this data apply? Why is this data
analysis important?
8. Be consistent to prevent distortions and disorientation
Use consistent intervals on axis scales.
Use a similar scale and consistent design for separate charts that are related.

9.
9
Step 4. Build the chart in Excel and embed in document
In general:
1. To create a chart in Excel, start by entering the numeric data for the chart in a worksheet.
2. Then you can plot the data into a chart by selecting the chart type that you want to use on the
Insert tab.
3. To embed the chart in a document or presentation, select the chart and then copy and paste it
into the new file.
Detailed instructions are available on this Microsoft Excel page.9
Try the exercises on the next page to experience the chart-making workflow.

10.
10
Exercises10
These exercises are progressive. Each builds on the features and functions explored in the previous
exercise. We will be using Microsoft Excel 2010.
FYI - Microsoft Office software is available for download on your home computer for free through
campus.11
Exercise 1: Make a column/bar chart
1. Please download our exercise data set. Visit http://bit.ly/dataucblib, and then select File and
Download to save onto the desktop.
2. Open the file and check that you’re in the worksheet“Book returns” at the bottom left.
3. If you see a yellow “Protected View” band on the top, click the Enable Editing button.
About this data
The Lemur Science Library staff recently instituted Universal Returns and they wish to evaluate the
program. They collected data on the home location of the books returned at this branch.
They wish to make a chart that displays the ratio of the home location of books returned at this
particular library. This is a part-to-wholerelationship, so a bar chart is a good place to start.
4. Select the cells that contain the data. Click the first cell in the data range, and then drag to the last
cell, or hold down SHIFT while you press the arrow keys to extend the selection.
5. On the Insert tab at the top menu, in the Charts group, click Column, and then select the 2-D
Column Clustered Column Subtype.

11.
11
Here’s the chart. Let’s remove the legend noted below.
6. Click on the legend textbox and select the delete button on your keyboard.
Let’s add a title to the chart.
7. Click anywhere in the chart to display the Chart Tools.
8. Select the Layout tab.
9. In the Labels group, click Chart Title, and then click the Above Chartoption.
10. Click into the Chart Title text box that appears, and type the text that you want.Try writing a brief
title that answers some of the journalistic questions (Who, what, when …). We’ll make the font size
smaller in a bit.
Let’s re-size the chart and make it bigger and easier to read
11. Click the chart, and then drag the sizing handles at the corners to the size that you want.

12.
12
12. Right-click or select the chart title text, and then in the Mini toolbar that appears, reduce the font
size to 14.
Congratulations you have a beautiful chart!
Exercise 2: Make a clusteredcolumn/bar chart
1. Let’s move to the next worksheet. In Excel, click on the worksheet tab at the bottom left labeled
“Library patrons by room.”
About this data
This is data collected on the number of patrons visiting the library at 7pm and their location in the
library.
The goal of this study was to identify patterns of room use across the year. For instance, are there
periods when seminar rooms are more heavily used then other areas?
The researchers wish to make a chart that compares the usage of the different spaces in the library over
time. This is a nominal comparison and a time-series relationship, so we could start with a column chart.
Specifically, we’re going to make a clustered column chart, which draws bars to compare values across
the different categories of library space.
2. Select the cells that contain the data. Click the first cell in the data range, and then drag to the
last cell. Be sure to select the column headings (i.e., Month, etc.). This metadata will be used to
create a chart legend automatically.

13.
13
3. On the Insert tab at the top, in the Charts group, click the Column chart type, and then select
the 2-D Column Clustered Column Subtype.
Here’s the chart

14.
14
Let’s add more details to the chart to improve clarity for readers
Add an axis title to the vertical axis, so readers understand that this axis measures the number of
patrons.
4. Click anywhere in the chart. This displays the Chart Tools.
5. On the Layout tab, in the Labels group, click AxisTitles.
6. Click Primary Vertical Axis Title, and then click the Horizontal Titleoption.
7. Click into the Axis Title text box that appears in the chart, type “Number of patrons”.
Let’s add a title to the chart.
8. Click anywhere in the chart to display.
9. Select the Layout tab.
10. In the Labels group, click Chart Title.
11. Click the Above Chartoption.
12. In the Chart Title text box that appears in the chart, type the text that you want.Try writing a
brief title.
Congratulations you have another beautiful chart!
But wait, would this chart look better if it were a line chart?
Let’s try converting this clustered column chart into a line chart.
13. Click anywhere in the chart to display the Chart Tools.
14. On the Design tab, in the Typegroup, click Change Chart Type.
15. In the Change Chart Type menu that appears, click on the Line chart option and then the OK
button.

15.
15
16. What do you think of the line chart?
17. Let’s switch back to the stacked column chart before with the keyboard shortcut CTRL +
Z.Sometimes switching between different types of charts may help us find a more ideal way of
presenting your story.
With our original column chart, let’s add a predefined chart style.
18. Click anywhere in the chart to open the Chart Tools.
19. On the Design tab, in the ChartStyles group, click the chart style that you want to use.Tip: To see
all predefined chart styles, click More
.
You can embed Excel charts into a Word document or PowerPoint document.
Let’s start with Microsoft Word.
20. Click anywhere in the chart.
21. Copy the chart with the keyboard shortcut CTRL + C.
22. Open Microsoft Word.

16.
16
23. In the new document, paste with the CTRL + V shortcut.When you make changes to the data in
the Excel spreadsheet, your chart will be automatically updated in the Word document.
Exercise 3:Create a line chart with multipledata series
1. Let’s move to the next worksheet. In Excel click on the worksheet tab at the bottom left labeled
“Therapy pet hugs.”
About this data
The goal of this study was observing library patron preferences for puppies or kittens during the therapy
pet visits at the library before exams.
The researchers want to make a chart that shows the number of kitten versus puppy hugs across this 14day program. This is a nominal comparison and a time-series relationship, so a line chartis a good place
to start.
2. Select the cells that contain the data. Click the first cell in the data range, and then drag to the
last cell. Be sure to select the column headings (i.e., Month, etc.). This metadata will be used to
create the chart legend.
3. On the Insert tab at the top, in the Charts group, click the Line chart type, and then select the
Line Subtype.

17.
17
Let’s re-size the chart and make it bigger and easier to read.
4. Click the chart, and then drag the sizing handles at the corners to the size that you want.
We could add more details to the chart to improve clarity for readers.
Let’s add an axis title to the vertical axis, so readers know it measures number of patrons.
5. Click anywhere in the chart to display the Chart Tools.
6. On the Layout tab, in the Labels group, click AxisTitles.
7. Click Primary Vertical Axis Title, and then click the Horizontal Titleoption.
8. Click into the Axis Title text box that appears in the chart, and type “Number of pet hugs”.
Let’s add a title to the chart.
9. Click anywhere in the chart to display the Chart Tools.
10. Select the Layout tab.In the Labels group, click Chart Title, and then click the Above Chartoption.
11. In the Chart Title text box that appears in the chart, type the text that you want.
Congratulations you have another beautiful chart! But wait,doesn’t the chart seem a bit cluttered?
Let’s try hiding the gridlines in the chart.
12. Click on the chart to display the Chart Tools.
13. On the Layout tab, click Gridlines.
14. To hide chart gridlines, point to Primary Horizontal Gridlines and then click None.
Let’s add some data labels.
15. Click the chart area to display the Chart Tools.
16. On the Layout tab, in the Labels group, click Data Labels, and then click the Above option.

18.
18
Too much of a good thing! Let’s remove the series data labels and label the single data point where
the two lines meet on December 8.
17. Click the chart area to display the Chart Tools.
18. On the Layout tab, in the Labels group, click DataLabels, and then click the None option
19. To label the Dec 8 data point, click on the data point where the two lines meet. In your first click,
it selects the data points for the entire data series. So click a second time to select that single
data point only.
20. In the Chart Tools, on the Layout tab, in the Labels group, click DataLabels, and then click the
Above option.
Congratulations, you are done!