how to improve a line chart in Excel

In a recent post, we shared how to create an Excel line chart, ending with the line chart below. This article builds upon this graph and covers simple design changes to the Excel defaults that will make it quicker and easier for our audience to understand the data.

Formatting adjustments

Excel built-in charts typically have borders, gridlines, and color legends—things that can make it harder for our audience to get at the data. 

Declutter

First, let’s focus on removing the elements that are taking up space but not adding informative value, like the chart border. Often, we use a border to differentiate parts of our slide or visual. In most cases, we can remove the chart border and use white space to better set them apart.

Removing the horizontal gridlines introduces additional contrast that allows the line to stand out more. Right-click on the gridlines and then select delete in the menu to achieve this. These two little changes simplify the graph and make it easier to see the data.

Next, we can adjust the thickness and the color of the line to something that will provide more emphasis and make the data stand out further. Right-click anywhere on the line and go to Format Data Series… in the menu. A formatting window will appear on the right of your screen. Navigate to the paint can icon on the upper left, then choose ‘Solid line’ and adjust the color to a dark grey. Just below this, we have the option to adjust the width of the line to something thicker—let’s go with 3 pt here.

After changing the line color and width, here is the chart we have:

Another change we can make is to adjust the y-axis number format to reduce the number of zeros that appear. Right-click on the y-axis, then choose Format Axis… from the menu. A format window will again appear on the right-hand side of your screen. Select the bar icon on the upper right to change the ‘Display units’ drop-down menu to Thousands.

At the very bottom of the formatting pane, under ‘Number’, let’s add a ‘K’ to the end of the ‘Format Code’ field to signify the y-axis numbers are in thousands—this also allows us to replace the axis title with ‘Meals served’, instead of the ‘Thousands’ title Excel automatically added.

While we are formatting the y-axis, let’s also add an axis line and tick marks to provide an anchor. Expand the ‘Tick Marks’ menu and select ‘Outside’ from the ‘Minor type’ drop-down menu. Let’s also add tick marks to the x-axis and align the tick marks with the data points on the line by choosing ‘On tick marks’ under the ‘Axis position’ option. The added tick marks maintain the cleanliness of the graph while bringing back a visual connection between the axis and the data that was lost when we removed the gridlines.

Here is what the axis formatting changes look like:

Finally, let’s reduce the amount of effort it takes to look back and forth from the legend to the line. There are a couple of options for this. One is to move the legend to the top of the graph as a subheader so the audience reads it before they get to the chart. Another option is to label the line directly. However, in this scenario, we’ll remove the legend altogether because we only have a single line and the axis title already explains what the data is.

We’ve identified and eliminated some items that were not necessary to our visual, making a more simplified view. To avoid having to make formatting changes, over and over again, you can use a clean slate template to build your graphs.

Provide additional context

Our chart is now looking considerably better, but we can do more to improve upon it. There are some items to add to our line chart to provide our audience with an additional understanding of what they are looking at.

First, let’s craft a clearer title. The data is the only context we have for this example, so let’s use it to make some assumptions about why our audience may care about the information. Meals served peaked in 2016 and since then the trend has declined. Our title could prompt the audience to ask how to return to the donation levels and meals served during the best fundraising year. 

To make changes to the title, click on the existing title twice and type your preferred words. For example, “Meals served over time: declining since 2016” could be a title for the chart. Now, select the new title and drag it to the upper left corner to create a visual frame around the graph. Putting the title in the top left also ensures that a viewer sees that important context as they begin to read the chart.

Finally, we can add data markers and text annotations to emphasize key points. Since the chart title calls out 2016, we can add a data marker for that year to draw the audience’s attention to that point. Double-click on the 2016 data point so it is the only one highlighted, then right-click and go to ‘Format Data Point’. Under the Marker Options, pick the circle from the Type drop-down and increase the size to 8. Additionally, let’s add a 1 pt white border around the data markers to make it stand out from the line.

Now we have an elegant line chart showing us that meals served have been declining since 2016. We can take it a step further and tell our audience what they should do with this information. For example, we could Insert a text box next to the 2016 data point to spark a conversation about how to reverse this trend.

To continue learning with this example, see how Cole tells a story with this dataset in the how to become a data viz superstar video.

More Excel how-to’s 

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

how to create a line chart in Excel

in ,

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: 

how to do it in Excel: emphasizing a data point

in , ,

Today’s post is a tactical one: how to highlight a data point in Excel. 

When we craft visualizations for explanatory purposes—that is, when there’s a specific finding or recommendation that we want to communicate to someone specific—our goal is to drive action. In those cases,  our visuals should emphasize what’s interesting in the data and what requires attention. Highlighting key points in our graph is an important step in creating successful explanatory communications.

Here are some charts that use this technique effectively, making it clear where an audience is meant to focus. You can check out many more examples by using the Discover feature in the SWD community. 

Let’s get right to how to accomplish this in Excel. The example we’ll be working with today is a graph that shows average revenue by event type for a venue that hosts various public functions. You can download the accompanying data file to follow along with these steps.

Here’s our starting point, which Excel created by default.

First, I'm going to create a clean, uncluttered version of this graph. (You can reference this prior post for steps to reformat Excel graphs.)

Let’s assume my audience is a marketing leader who’s interested in knowing which event type is expected to have the highest average revenue in the coming year. Since the answer is “Fundraisers,” I’ll direct attention to the 2020 forecast for that event type with these steps:

  1. Click once anywhere on the Fundraisers line, then click on just the 2020 data point. Right click on the selected point and choose Format Data Point. 

2. The Format Data Point menu opens. Under the Fill & Line tab (with the paint bucket icon), click on the button that says “Marker,” and then navigate to Marker Options. Click the Built-in option, then choose a filled circle in the drop-down Type menu. I’ll also adjust the size to 9 (the size you choose should depend on the existing thickness of your lines and your individual needs for using the marker as emphasis).

3. In the Fill menu, I can adjust the color by selecting my preferred choice under Solid fill:

4. I’ll also adjust the outline color in the Border menu below it. I chose no line, but this is my personal preference.

My resulting visual looks like this:

Even if you just quickly glance at this graph, notice how your eyes are drawn to that blue dot. By adding and formatting this particular data point, I’ve made it clear to my audience that the Fundraiser value in 2020 is where I want them to look.

Since the 2020 data point is a forecast, I’d want to visually distinguish it from actual data by varying the line style (making it dotted instead of solid) and the marker style (making it open rather than filled). I’ll also include the numeric data labels for specificity’s sake.

Here’s how we can apply those changes, as we continue to refine our graph:

5. To vary the line style, highlight the data point of interest the same way you did earlier in Step 1. Right-click and choose Format Data Point.

6. In the Fill & Line tab again, instead of clicking on Marker, click into the Line menu, where you can adjust the various settings of the line immediately preceding the data point. To achieve the effect in my example above, my selections are a solid blue line with a width of 2.5 pt and a Dash type of Square dot. You can follow these same steps for the other event types in the graph as well so that all of the lines for 2020 are dashed to indicate a forecast (without changing the color to blue).

7. To add the numeric labels, I’m opting to include them as text boxes so that I have greater control over the formatting. You can read more about the considerations for text vs embedded labels in this prior post.

These formatting changes require a bit of extra effort, but they make your charts far more effective. Don’t assume that your audience knows where to look in your visuals—make that task easier for them through your design choices.

But wait…there’s more! What about all the other data? Isn’t that interesting to the audience? It absolutely could be—I invite you to practice how you’d approach this task in the community exercise emphasize multiple takeaways. You can also check out how I’d walk my audience through a story in the video so what? using story to drive action (open to premium subscribers). 

More Excel how-to’s:

how to do it in Excel: adjusting bar width

in , ,

Today’s post is a tactical one: how to adjust the widths of bar charts in Excel (and why you should). 

Before we get into the step-by-step, I should mention that there aren’t any strict rules for optimal spacing between bars. Rather, it’s personal preference similar to wearing white after Labor Day (in the U.S., that’s the first weekend in September). As a resident of the muggy Southeast, I’ll be rocking white until fall temperatures arrive in mid-October. However, if you live in cooler climes and consider Labor Day the symbolic end of summer, your preference might be to say sayonara to white until Memorial Day. 

The same gray area goes for optimal spacing between bars. The actual width is not set in stone. Our goal is to enable our audiences to compare the lengths of the bars (instead of the area between them), so general guidance is to thicken the bars to minimize the surrounding white space.

Let’s turn now to how to accomplish this in Excel. In the spirit of Labor Day, I’ll use some data from the Bureau of Labor Statistics (BLS) showing the top ten occupations in the U.S. as of May 2020. 

Compare the bar spacing in the two visuals shown below:

optimal bar chart spacing.png

On the left, the gaps are attention-grabbing and create an unnecessary shimmer to the visual. The adjusted version puts emphasis on the length of the bars.  Download the Excel file to following along with these steps to manually adjust:

  1. Highlight all the bars, right-click and choose Format Data Series:

how to adjust bar chart spacing.png

2. In the Format Data Series menu, under Series Options, adjust the Gap Width dialog box:

 
how to adjust bar chart width.png
 

The result is this:

bar chart example.png

Another benefit of doing this is that now there’s enough space to pull the long data labels into the ends of the bars. This is just one of the decluttering steps we can take to reduce perceived cognitive burden. Here’s how to achieve this:

3. Click on any data label to highlight them all, then right-click and choose Format Data Labels:

how to reformat bar charts.png

4. In the Format Data Labels menu, select Label Options, and in the Label Positions section, choose Inside End. (While you’re at it, in the Label Contains section, uncheck “Show Leader Lines.” These are almost never necessary.)

 
bar chart example.png
 

My graph renders like this, due to my color scheme. I’ll adjust the font color so that the labels have sufficient contrast against the dark blue bars. (TIP: you can use the online WebAIM contrast checker to see if your text is sufficiently readable against your background color.)

bar chart example.png

5. To adjust the font color, click to select all the labels, choose the Font options dropdown arrow, and then select a different hue (you can also do this in the Format Data Labels menu if you still have it open):

bar chart example.png

The final visual looks like this:

bar chart example.png

I might even choose to further format the numbers by displaying the units in millions.

Just as the modern-day guidelines for wearing white after Labor Day are subjective, so too are the rules for the exact spacing between bars. As the designer of your own graphs, experience and personal preference will help you find your own “Goldilocks” of spacing: too thin, too thick or just right.

bar chart example.png

More Excel how-to’s:

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.

visualizing gender identity

in

At a recent custom client workshop, the question was raised: how do we best visualize the results of our gender identity survey? 

The organization had collected data on gender self-identification from four categories of employees: the board, directors, managers, and associates. Their responses are summarized in the table below (details have been slightly modified to protect confidentiality.)

gender identity survey results.png

Before we jump straight to visualizing this data, let’s spend a minute familiarizing ourselves with the responses. To do this, I’ll introduce some visual elements into this tabular data, to draw attention to some interesting things. 

A heatmap allows us to convey the relative magnitude of the numbers with color saturation. We could do this within a single column:

gender identity survey responses.png

Or, across multiple columns:

gender identity survey results.png

A heatmap alleviates some of the cognitive burden of processing tabular data. 

However, there’s an interesting piece of the data that might be better served with a different visual: the wide range of magnitude in responses. Scanning down then columns, the minimum value is zero, then some single-digit values (1%), and a maximum value anywhere from 50% to 73%. 

Along our rows at the bottom, where we are counting the absolute numbers of people in each role, the range is even greater, from a low of eight directors to a maximum of 161 associates:

gender identity survey results.png

One benefit of switching from a table to a graph would be to help a viewer understand the overarching shape of the data, which is less apparent when visualized in tabular form. While there’s many ways we could graph this data, I opted for a square area chart for its versatility in displaying numbers of different magnitudes.

 
unit graph example.png
 

A square area chart is an example of a unit graph: a single square represents one unit. The benefit of this type of graph is that you can visualize more information—two dimensions encoded as width and height—in less space. 

I used Excel to create my square area graph, first illustrating the survey population:

 
 

Then displaying the overall breakdown with color saturation:

 
gender identity survey results example.png
 

I could split this into 4 separate graphs to show the breakdown by role—and where the number of roles is concentrated.

unit graph example for gender identity.png

When working with survey data, a square area graph can be an effective choice to visualize the magnitude of responses within a population. If interested, you can download the Excel file for a behind-the-scenes peek at how I created these visuals. 

A square area chart is just one example of a unit graph. Check out these additional resources on this type of visual: