How to Create Line Graphs in Excel

CHRIS LYSY

/freshspectrum

How to Create Line Graphs in Excel

This is the second in a series of posts on chart design in Excel. In each post we will take on a different chart type. Today we’ll go with a chart that almost always tells a story, the line graph.

What you’ll find in this post:

An Oversimplified How To
How to create a good looking line graph.
A little bit of inspiration.
Some other considerations.

An Oversimplified How To

Creating a line chart (a.k.a. line chart) is really simple in Excel. It just takes a column of data and a couple of buttons.

Step 1. Highlight a column of numbers.

Step 2. Click on the insert tab, then the line chart icon. Then select the first 2-D line.

*Thought: Why oh why would anyone seriously choose a 3-D Line!!!!

Step 3. Woohoo, you created a line graph!

How to create a good looking line graph.

Okay, so that’s the most basic of basics. Here are some more steps you probably want to take to create something worth sharing.

Labeling your graph.

So in addition to just a column of numbers it’s a good idea to have row labels and column headers. For line graphs you are going to be dealing with some kind of time element (day, week, month, quarter, year, etc.). I’ll use years for my example, but because this creates a second column of numbers, instead of just showing up as labels Excel will create a second line (not what we want).

Select Data Source

I will go ahead and right click on the chart so I can open up the Select Data Source menu. I only want to see one data series in there, but I’ll use the Horizontal (Category) axis labels space to set the years as a label.

Shrinking the Axis

Depending on your data, you don’t always have to start your axis at 0 with line graphs (like you always have to do with bar charts). This fake data I’m using is all within the range of 54-70, so I’ll go with a chart range of 45 to 70. You can get the format axis menu by just right clicking on the chart.

Note: if you have multiple line graphs, be consistent on your axes. And if it makes logical sense to start at zero, then start at zero.

Creating a Clean Graph

I like labeling individual points on the line chart, so the grid lines and y axis are not necessarily required. But given the chart, I kind of like the way the grid lines look with 5 as the major unit.

Add and Format Data Labels

If I only have a simple line graph (with on line) I usually add data labels. Usually I just stick the labels above the chart, but this is just one of the many little design choices you make on the fly. Overall you just want to be consistent.

Adding Line Markers

I like using line markers with line graphs. Usually I go with a subtle circle just a little larger than the line. They do have a purpose, showing the markers lets the reader see exactly how many data points the line represents.

If you have multiple lines, I suggest using a highlight color on the one you want your reader to focus on. Then use a gray scale for the rest. This makes for a nice graph that really pops.

Highlighting an Individual Marker.

It is possible to change colors/sizes of individual markers. Why might you do that? Perhaps the data was collected differently for that data point, or maybe you just want to add a bit of context and need a way to draw attention to a specific point in the chart.

Adding Annotations

The best way to add a little focus to a line graph is usually through a simple annotation. Just select the chart, then go to the insert tab. And then insert a text box.

To draw a connection to a specific point on the line, insert a shape (line).

Saving the Image

Once you have made all the changes you want to the chart you can save it as a picture. Just right click on the chart area and click “Save as Picture.”

Alternatively, you can just copy and paste the chart from Excel into Word or PowerPoint. If you’re staying within the Office suite, this is the ideal way to move the image as it retains your full ability to reformat the graphic.

Also, just a note, if you notice your text box or inserted shape is missing this is why. When you insert a shape you need to have the chart selected. If not, it will live in the spreadsheet but not the actual chart image!

Beyond the Basics

This is just the basics. There are all sorts of things you can do with line graphs.

Generally though I suggest keeping the chart itself simple and minimalist. You can always add flare through annotations/additional graphics. Doing things like making your charts 3D doesn’t help the presentation, it just makes the chart harder to interpret and can also skew the data.

A little bit of inspiration.

Line graphs are another one of those ubiquitous chart types that you find everywhere.

I like this example from the NY Times learning network. It’s a two part line graph. The first gives an overall line for all of transportation greenhouse gas (with other sources in gray for context). The second graph is the same line graph, but broken down as an area chart so you can see the pieces that make up the first.

NY Times – Learning Network – What’s Going On in This Graph? | Nov. 20, 2019

Here is a chart created by a group of students at Atlanta University in the early 1900s under the leadership of W.E.B. DuBois. At it’s core, it’s just a line graph (an upside down one) but the two colors tell the story.

“Proportion of Freemen and Slaves among American Negroes”, 1900, via Library of Congress Prints and Photographs Division. Read more about these charts > W. E. B. Du Bois’ staggering Data Visualizations are as powerful today as they were in 1900

This chart here is just a line graph showing the US unemployment rate over time. Seems like something happened in the year 2020…

Civilian Unemployment Rate Chart U.S. Bureau of Labor Statistics – Accessed 12/31/2020

Other Considerations

How do you lie with line graphs?

As with any other chart or graph, you can certainly lie with line graphs. There are a bunch of different ways to do that. One common way is by messing with the axis, illogically flattening or spreading the line. Another way is to cut down the data shown. You might see politicians doing this, where they pick only the stretches of line charts they want to show.

How to Lie with Charts – Hands On Dataviz

How many lines is too many lines?

You should try to avoid putting too many lines in a single graph, at least if your goal is for every line to be read. But using gray or semi-transparent lines can add some really nice context.

The Spaghetti Plot – from Data to Viz

Primary Sidebar

INTRODUCING OUR NEW COMMUNITY!

Join us!

FAQS

Who runs this blog?
Chris Lysy (LinkedIn, Twitter, “chris @ freshspectrum.com”

Can I use one of your cartoons for free?
Most likely yes, you’ll find my licensing info here.

Can I support your cartooning?
Totally, become a Patron.

Want to hire me as a data consultant?
I fix data problems for a living.

Check out my data consulting page for more information.