how to create a line chart in Excel

A line chart is a simple graph that is familiar to most audiences. Lines are great for showing continuous data, such as plotting how the value of something changes over time. In this post, we will cover how to create a line chart in Excel, using a sample dataset from a community exercise: table takeaways. The information is about an annual corporate fundraiser to provide meals to those in need. You can download the file here to follow along as we build the line chart. 

The dataset includes two columns—a column for the campaign year and a column for the number of meals served in a given year. Let’s assume we want to make a line chart with the years going along the x-axis and the meals served along the y-axis.

Creating a single line graph in Excel is a relatively straightforward process, as it is a default chart type.

Insert a line chart

To begin, highlight the data table, including the column headers. To do this, click cell B7 and drag your cursor to C18. Next, navigate to the Insert ribbon and select the line chart icon. (Note that you can also use the Insert menu at the very top, then choose Chart -> Line to achieve a similar result.)

Excel offers several different variations of the line graph. For our purpose, we will go with the first option available—a 2-D line chart without markers

Now, we have a simple line chart, but it is not exactly what we had imagined. Excel made the Campaign Year data points a series instead of the x-axis labels that we wanted.

To adjust the x-axis to show the years, right-click on the chart and go to Select Data… in the pop-up menu. Campaign Year and Meals Served are in the list in the series box in the middle. Pick Campaign Year and then click the minus (-) sign box below the list to remove Campaign Year as a series.

Next, add the Campaign Year as the horizontal axis labels by clicking the cell selector icon to the right of the box and then highlighting the data cells with the Campaign Year (cells B8:B17). Finally, click the OK button.

With this adjustment, we have our desired line chart with the years going along the x-axis and the meals served along the y-axis. Excel has automatically provided a ‘Meals Served’ title for us. We can improve upon this title, and some of the other formatting decisions Excel has made for us. We'll cover these steps in detail in an upcoming post. In the meantime, check out other Excel how-to articles below.

More Excel how-to’s 

For more tactical Excel instructions, check out these other resources: 

create a dot plot

Dot plots are great charts to have in your repertoire. They come in many forms, but the two I find myself using the most frequently are the Cleveland and the connected dot plot.

Dot plots.png

Unfortunately, many graphing tools don’t include dot plots in their default charting options—including Excel, my preferred graphing tool. To build a dot plot in Excel, you need to get creative and format an existing chart to present as a dot plot. 

It sounds like some sort of wizardry, yet hopefully, this article will take the magic out of the process, enabling you to build dot plots and other custom creations.

There are multiple ways to go about this. You could make a dot plot in Excel out of a stacked bar chart, a line graph, or an XY scatterplot. As the old adage goes, “There are many paths to the top of the mountain, but the view is always the same.” 

Today, I’ll share my preferred approach for making dot plots using an XY scatterplot. The good news is that once you learn how to make one, you basically know how to create the other because the connected dot plot builds off of the Cleveland version.

To illustrate the steps, I’ll use the data from a community exercise: a matter of taste.

A matter of taste dataset.png

Let’s jump in!

Build a Cleveland dot plot in Excel

Add a SPACING series. For dot plots, we’ll need to add an additional series that will space out the dots vertically, so they don’t overlap in the scatterplot. My dataset looks like the following.

Cleveland dot plot data.png

The values for my SPACING series are arbitrary. I tend to use numbers that end in 0.5 rather than whole numbers for reasons that will make sense once we get to the Connected dot plot.

Insert an XY Scatter chart. Now that we have our three data series, it’s time to add our XY scatterplot. Highlight the CREAMERY series, press CTRL (COMMAND for Mac users), and highlight the SPACING series. Then navigate to the Insert tab and choose the options for Scatter. The resulting graph should look like the following.

STEP 2 insert XY scatter.png

Add a second series to the XY Scatter chart. Next, right-click on the graph, and choose Select Data. You can then click the plus button to add a series. Choose the DAIRY GIRL values for the x-values and SPACING for the y-values.

STEP 3 add second series.png

Adjust the axes. Hopefully, you can start to see the dot plot taking shape. To align the horizontal gridlines, I’ll change the starting value of my vertical axis from 0 to 0.5. For my horizontal axis, I’ll change the range from 0-8 to 1-9, since the underlying data is collected along a hedonic scale. I’m also a fan of moving my horizontal axis to the top of the graph, so I’ll make that change as well.

STEP 4 adjust axes.png

Format, format, and format! Now it’s a matter of formatting the chart. I’ll typically increase the size of my dots, delete the vertical gridlines, craft a clear title, delete the vertical axis, and add text boxes for the horizontal labels. The results look like the following.

STEP 5 Cleveland dot plot.png

You can continue to format your chart to achieve your desired look, but I’ll stop here so that we can more easily modify this view into a different variation: the connected dot plot. 

Build a connected dot plot in Excel

The following instructions build off of the above steps for a Cleveland dot plot. If you haven’t done so already, you’ll want to build a Cleveland dot plot to follow along. We will use our existing dot plot, and add a bar chart behind the dots to give that connected appearance.

Create two more data series. To create the bars, you’ll need to create two more data series using your existing data. Copy the CREAMERY and DAIRY GIRL series into new columns. I’m calling them FRONT (DG) and BACK (C). The FRONT series will represent the minimum of the CREAMERY and DAIRY GIRL series, while the BACK series will be the maximum. In our case, this is easy because all of the DAIRY GIRL values are smaller than the CREAMERY values. 

Warning! When you copy the data, put them in reverse order, as shown below.

Connected dot plot data.png

Adjust the Cleveland plot. For teaching purposes, we are starting from the Cleveland dot plot, so we need to make a few adjustments. Delete the horizontal gridlines as we won’t need those anymore, and adjust your vertical axis to go from 0 to 6. Once you’ve done this, you can delete the vertical axis again. (I’ve maintained it so you can see the range.)

STEP 2 adjust Cleveland dot plot.png

Add new data to the dot plot. Next, add the FRONT and BACK series to your chart. Right-click on the graph, and choose ‘Select Data’. You can then click the plus button to add a series. Your graph will look silly at this stage. In the below visual, I can only see the dots for the FRONT series, even though both series were added.

You’ll also want to make sure the FRONT series is positioned at the bottom of the series list. Reorder things if needed.

Change the chart type. Select each of the new series and change the chart type from a scatterplot to a standard horizontal bar chart. Because I cannot visually see both of the series, I’m using the drop-down window in the Format tab to select each series.

STEP 4 select series.png

Once selected, I can navigate to the Chart Design tab and choose Change Chart Type. The resulting graph looks like the following.

Overlap the bars. Right-click on the bars and choose Format series. You can then adjust how much the bars overlap (from 0% to 100%). The FRONT series (or shorter bar) should be in front now.

A quick side note! We spaced out the original dots using numbers ending in 0.5 (SPACING series), so the bars and dots align perfectly along the vertical axis. If you used whole numbers instead, then the bars will not align behind the dots and you’ll need to adjust your dataset.

Adjust the second horizontal axis (bottom). To align the bars and dots horizontally, you’ll need to make both horizontal axes match. Click on the bottom axis, choose Format Axis and change the range from 0-8 to 1-9. Once aligned, you can delete the bottom axis; I’ve preserved it so you can see the range.

STEP 6 adjust horizontal axis.png

Change the colors of the bars. The final step is to hide the top bar by changing the color to white. I have also adjusted the longer bar to a neutral gray so that the dots continue to stand out. Voila, a connected dot plot!

STEP 7 connected dot plot.png

As stated previously, there are several ways to brute-force a dot plot in Excel—and many other tools for that matter. This is the method that I prefer because it works for both the Cleveland and connected versions. It also works regardless of whether I’m building a horizontal or vertical dot plot. If you have a different approach or suggestion, be sure to let others know via the comments or if you enjoy consuming content via video check out our How to make a dot plot in Excel tutorial. The snippet below talks through an alternative approach to adding category labels.

You may be thinking that this is a lot of effort. You’re not wrong. Custom charts are tedious, but sometimes it’s worth it. That’s why I often advise people to keep things low-tech when determining which graph to use. Sketch your ideas with pen and paper to determine which chart makes sense for your scenario, then figure out how to build it in your tool—Google searches, and a bit of creativity go a long way!

To learn more about tools, check out some of the how-to videos in the community (open to premium only). If you want to practice making dots, feel free to download the attached Excel spreadsheet and experiment independently.

baby steps

Today’s quick post is an example of an incremental improvement to an existing graph that makes it easier for the audience to read. When practicing being a better communicator with data at work, it’s easy to become consumed by the desire for perfection and talk ourselves into thinking it’s an all-or-nothing approach. Rather, the opposite is true: incremental improvements are achievable baby steps we can take to become better data communicators. 

Consider the following visual and imagine it’s part of a semi-annual update to senior leadership. The data displayed is total dollar volume of an organization’s funding amongst its various initiatives. Within each initiative, the stacked bars break down the dollar volume into three stages: distributed, pending and funded.

 
Picture1.png
 

Assume a stakeholder asks you for an updated version by the end of the day. Given the tight deadline you don’t have time to make big sweeping changes—however, you can use this as an opportunity to make an incremental and impactful improvement. Study the visual and ask yourself: what is one change that would make this graph easier to read?

Do you have your change in mind? Read on to see what mine is!

My incremental improvement is to reposition the words to the top. More specifically, I’d choose to left-align the chart title and supporting subtext while moving the legend and x-axis labels to the top of the graph. The benefit is that my audience sees how to read the graph before they get to the data! You can see this change reflected below.

 
 

There are certainly more modifications I’d make given ample opportunity but time constraints are real. Continually evaluating our graphs for readability and implementing small incremental changes sets us up for improved future iterations and, ultimately, success. You can download the Excel file to see how I made these changes. 

What was the incremental change you’d make given limited time? Leave a comment with your thoughts! Now, consider your own work: what baby step could you apply to make an existing graph easier to read?

how to do it in Excel: a shaded range

Today's post is a tactical Excel how-to: adding a shaded region to depict a range of values. 

To illustrate, let’s consider an example from the tourism industry. Suppose a watersports company offers four categories of outings: fishing charters, family rentals, nature cruises and sunset cruises. The graph below shows the monthly volume of passengers for each offering over a year.

 
 

We can see there’s clear seasonality in this business—overall volume is highest in the summer and each outing type generally follows the same monthly pattern. Let’s say you manage the Family rentals and you’d like to compare your monthly volume to what you’re seeing across the entire fleet. 

For the purpose of this tactical illustration, let’s assume the shape of the data—relative peaks and valleys—is more important than the specifics of each category individually. If that’s the case, I can simplify by showing a shaded region to depict the range of absolute passengers each month.

My resulting graph looks like this:

 
Picture23.PNG
 

Creating this shaded region in Excel requires some brute-force formatting utilizing area charts. Here’s a step-by-step overview of how I accomplished this—you can download the file to follow along.

In my Excel spreadsheet, the data graphed above looks like this:

 
Picture3.png
 

The first thing I’ll do is add two new columns calculating the minimum and maximum values for each month. I used a =MIN() and =MAX() function and my resulting series looks like this:

 
Picture4.png
 

To create the shaded region, first I added the Min and Max as new data series and deleted the lines depicting fishing, sunset and nature. Then I adjusted the formatting of Min and Max to create the grey band around family rentals. The following steps show how I accomplished this:

Next, delete the series for fishing, nature and sunset cruises (leaving only family rentals displayed) by highlighting each individual line and pressing delete

 
Picture2.PNG
 

Add a new data series for the Maximum by right-clicking the chart and choosing Select Data:

 
 

In the Select Data Source dialog box, click the + button to add a new data series for the Maximum (my Max series is in cells P6:P17 with Name in P5). Click OK when done. 

 
Picture7.png
 

My resulting chart looks like this:

 
Picture8.PNG
 

Reformat the the Max line by changing it to a 2D stacked area: right click the “Max” line, go to “Choose Chart Type” then select “Line”. Scroll down to the 2D area types and select the 2D stacked area chart (It’s the middle one for my version of Excel):

 
Picture9.png
 

My resulting graph looks like this:

 
 

Change the Max 2D stacked area to grey fill by right-clicking the series, choosing Format Data Series

 
 

In the Format Data Series dialog box, select Fill and choose the Solid fill option. My selected grey is RGB 191-183-185. 

 
Picture12.png
 

My resulting chart looks like this (note: my Max stacked area chart is set to display as Series 2 although we’ll adjust this in a later step):

 
 

Add a new data series for the Minimum by right-clicking the chart and choosing “Select Data”:

 
Picture14.PNG
 

In the Select Data Source dialog box, click the + button to add a new data series for the Minimum (my Min series is in cells O6:O17 with Name in O5). Click OK when done. 

 
Picture15.png
 

My resulting chart looks like this:

 
Picture15.PNG
 

Reformat the Min line by changing it to an area: right click the “Min” line, go to “Choose Chart Type” then select “Line”. Scroll down to the 2D area types and this time, we’ll select the 2D area chart (mine is the first one in my version of Excel):

 
 

My resulting graph looks like this:

 
Picture17.PNG
 

Reformat the Min 2D area to grey fill by right-clicking the series, choosing Format Data Series: 

 
Picture19.PNG
 

In the Format Data Series box, change to Solid fill with Color = White. Under Border, select No line

 
 

The result is this:

 
Picture18.PNG
 

For final formatting changes, I added text boxes for the Max and Min labels and changed Family rentals to render in black for sufficient contrast against the grey band. Depending on where your Max series is displayed, you may also need to ensure that the white Min series is displayed on top if it is not rendering. Highlight the Max series and in the formula bar, ensure the last option is 2. You can also adjust the display order in the “Select Data Series” dialog box. 

 
 

Voila! A shaded region to emphasize a range around my data point of interest:

 
 

Are there other brute-force Excel methods you’re aware of for achieving this effect? Or other considerations with embedding this shaded region? Leave a comment with your thoughts and stay tuned for a new resource coming soon where you can practice and share similar tips!    


Elizabeth Ricks is a Data Visualization Designer on the Storytelling with Data team. She has a passion for helping her audience understand the ’so-what?’ as concisely as possible. Connect with Elizabeth on LinkedIn or Twitter.

power pairing: color + words

What is one thing you’ll do differently after learning the storytelling with data lessons?

At the end of our workshops, participants are often prompted to reflect on this question. The resulting discussion usually evolves into things that can be easily integrated into the day-to-day work already being done. One piece of advice we frequently give may surprise you—there are two easy actions that don’t require complicated technical skills! First, adopt the habit of stating your takeaway in words. Second, develop the practice of using color sparingly. Today’s post is a quick illustrative example that puts these tips to use. 

At a recent client workshop, we discussed a visual similar to the one below. It is a snapshot of an organization’s current accounts payable (AP) by vendor at a point in time. At a basic level, the graph is fine. It’s cleanly designed with a left-aligned chart title, data labels incorporated into the bars, and no clutter of gridlines or chart border. The bar chart is easy for me to read—I can quickly see that AP is highest for Microsoft and how incrementally larger it is compared to the other vendors because of the consistent baseline (the y-axis). 

 
Picture1.png
 

What I can’t easily see is what I should take away from this chart. At client workshops, we often don’t have this important context—because of this, we often show multiple approaches for highlighting different potential takeaways. Below you’ll see several strategies for employing color and words in this visual. In each of these, notice how the words set up your expectations for what’s emphasized in the graph and color used sparingly indicates where to look in the visual. 

If the audience is interested in the highest spend, I could emphasize the largest vendor:

 
 

Perhaps the audience will be more curious where AP is concentrated. I could instead focus attention on the top vendors:

 
 

What if the conversation is about expectations—is this spend surprising or unsurprising? I might add additional context with super-categories—useful if the audience is unfamiliar with these vendors’ services—grouping and employing similarity of color and position to visually tie the text to the data it describes. 

 
 

Consider pairing color and words in your visuals to be more effective when communicating for explanatory purposes with data. You can practice employing this technique with this community exercise or download the data file to explore the above graphs. Bonus: you don’t need fancy tools to do either of these things!

tactical tip: embedding a vertical reference line in Excel

Today's post is a step-by-step Excel “how-to” inspired by a reader question we received following a recent post on using dotted lines in data visualizations.

Dave asked:
“Do you know of a trick for drawing vertical lines to delineate years (or actuals/historical vs forecast/future segments of the chart)? I currently have to draw them with the line drawing tool, which gets messy when moving the chart on a PPT slide. If there were a way to embed it in the data or somehow format the chart, that'd be awesome.”

The following chart illustrates what Dave describes. The data is units of output over time where the first nine months of the series are actual data and the remaining four months of the year are a forecast. The dotted line serves as a visual cue to differentiate actual from forecast. Created in Excel, the line was physically drawn on the graph with the Shape Illustrator. While this approach might suffice as a quick method for achieving the desired effect; it isn’t ideal for recurring use of the graph, particularly if the line’s position on the x-axis might change in future iterations.

 
Picture1.png
 

After some research and playing around in Excel, I’ve devised one method for achieving this effect, which I’ll outline in this post (I’m sure there are others!). Don't be dismayed by the number of steps: it's a one-time setup after which can be easily refreshed in future iterations by changing where you want the reference line. I’m using Excel 2016 and you can download the accompanying file.

In my spreadsheet, the data for the Output over time chart looks like this:

 
Picture2.png
 

1. Go to a blank cell range and enter these values as shown in my screenshot below. I’m choosing to add these new values directly underneath my data range in cells F19:G21. This will eventually become the coordinates for a secondary scatterplot that we’ll add in a later step.

 
Picture3.png
 

2. Choose where you want the vertical reference line to cross the x-axis and enter those values below “X”. In this example, I want the line located on the September data point, the ninth point in my data series. In cells G20:G21, I entered “9” in each, as shown below. (Note: for a more automated approach in a larger dataset, a MATCH formula could also calculate where September falls in the range: =MATCH("Sep",$F$7:$F$18,0).

 
Picture4.png
 

3. Add a new data series by right-clicking the graph and choosing Select Data:

 
 

4.  In Select Data Source dialog, click the Add button.

 
 

5. In the Edit Series dialog, enter a name for your data series (I chose “reference”) and select the X values you entered from Step 2. I selected the 9’s in G20:G21. Click OK to exit the dialog boxes.

 
 

The resulting visual looks like this:

 
 

6. Right click the new line and choose Change Series Chart Type.

 
 

7. In the Change Chart Type dialog box, select Combo section under All Charts tab. Then select Scatter with Straight Lines and check the option for Secondary Axis. Click OK to exit.

 
Picture10.png
 

The resulting visual looks like this:

 
 

8. Go to the chart, right click the red reference line and choose Select Data again. In the Select Data Source dialog, highlight reference and click Edit.

 
 

9. In Edit Series dialog, update the X values to be the original values you selected in Step 5. Set the Y values to be 0,1. Click OK to exit.

 
Picture13.png
 

The resulting visual looks like this:

 
 

The remaining steps are visual cleanup: first, I forced the red line to align with the top of the primary y-axis and second, I hid the secondary axis line and text labels.   

10. Right-click on the secondary y-axis and select Format Axis:

 
Picture15.png
 

11. In the Axis options section, type 1 into the textbox beside the Maximum option.

 
 

12. In the Text Options section, under Text Fill, choose No fill. This will remove the text labels on the secondary y-axis.

 
 

13. In the Axis Options section, under Line, choose No line. This will remove the secondary y-axis line.

 
 

Voila! The resulting visual has an embedded vertical line, which is plotted on a hidden secondary y-axis.

 
Picture20.png
 

Recall that this goal of this specific scenario was a dotted line which visually differentiated the actual and forecast sections. My last step was to change the formatting of the line to appear as a thin, grey dashed line.  

(Note: To achieve your preferred formatting, right-click the line and select Format Data Series in the context menu where you’ll find formatting selections in the resulting dialog pane.)

 
 

This method does come with some trade-offs to consider.

One downside is that you lose some control over the exact placement of the line where it crosses the x-axis. Below you’ll see a comparison between the manual vs embedded approach. With the manual approach, the line can be drawn exactly on the tick mark between the Aug & Sep data points, providing a clean alignment with the x-axis. With the embedded approach, the line is centered above the Sep label, resulting in a slightly less seamless effect.

 
 

On the cosmetic side, another downside is losing the flexibility to manipulate the length of the line for labeling purposes. I’ll illustrate this with a horizontal bar chart (which I also created using this method). With the manual approach, I can physically draw the line to extend above the x-axis line, aligning it closely to the “Target” text label. With the embedded approach, the line stays below the x-axis line, creating a gap between the line and the label that describes it.

 
 

You can download the Excel file to see the behind-the-scenes of these graphs. Are there other methods you’re aware of for achieving this effect? Or other considerations with embedding the reference line directly? Leave a comment with your thoughts!


Elizabeth Ricks is a Data Visualization Designer on the Storytelling with Data team. She has a passion for helping her audience understand the ’so-what?’ as concisely as possible. Connect with Elizabeth on LinkedIn or Twitter.