This is an Eval Central archive copy, find the original at depictdatastudio.com.

Do you have a line chart with tons of lines?

Sometimes, the line you want to highlight… is stuck all the way in the back. Nobody can see that line, because it’s all covered up. Oops!

In this video, you’ll learn how to rearrange the line chart so that the line you want your audience to see isn’t covered up.

Watch the Tutorial

What’s Inside

0:00 The Problem: Excel Graphs with Covered-Up Lines
0:23 Welcome to Dataviz On The Go
0:31 The Dataset
0:47 Another Problem: The Spaghetti Line Chart
1:10 3 Fixes for Spaghetti Line Charts
1:37 The Edited Graph – But Average Line Hiding and Covered Up
2:06 The Solution & Where to Click in Excel
2:50 Bottom of the List… Front of the Graph
3:04 Download the Spreadsheet
3:11 Don’t Forget to Like, Subscribe, and Share

Download the Spreadsheet

Want to download the Excel file and follow along ?Download the file here: https://depictdatastudio.ck.page/products/moving-lines-to-the-front-or-back

Read the Transcript

Ann K. Emery: [00:00:00] In this video, you’re going to learn how to bring the average line to the front of your graph. So instead of covered up and hiding, it’s just going to be in the front so people can actually see it. If this sounds like something that might be relevant to you and your workplace, stick around. I’ll give you some context about this graph, and then I’ll teach you how to fix it right inside of good old Excel.

Hi, I’m Ann Emery, you’re watching Dataviz On The Go, the series where I make jet speed tutorials as I’m racing around between my workshops and meetings. And speaking of workshops, this is the real life, sort of, table that I was working on in a workshop recently. These aren’t the real state names. These aren’t the real timeframes or the real numbers, but the graph looked more or less like this.

What happened was, when we highlighted the table and we Inserted the graph. We were left with Ann Emery’s least favorite chart of all time, the spaghetti graph. This happens all the time in real projects. So if you get a spaghetti [00:01:00] graph, don’t worry. There are some common fixes and just to make it super duper clear what we’re not doing.

Let me just make it really, we’re just, we’re not doing this. Okay. Are we in agreement? We’re not doing this. Common fixes. Common fixes, uh, you could either gray everything out, highlight one, one line at a time, that’s what we’re going to do here. Another fix, you could do small multiples, or another fix is interactivity.

Okay, those are the three common fixes for really busy, really dense graphs. In this case, we wanted to gray everything out and highlight one line at a time so people could see the average. They could see the big picture. Here’s, on average, how all of our states are doing, but then a split second later, They could look a little bit further and they could see all the gray lines.

One star of the show, lots of sidekicks. But then the problem we ran [00:02:00] into is like, the average line is missing, or, not missing, but it’s hiding. It’s really hard. You can’t see it. Where’s our main character? So let me copy paste this one so we can admire them side by side. I’ll show you what to click on. You’re going to click on…

The middle of the graph. Not the side, not the top, not the bottom. Okay, the middle. You’re going to right click. You’re going to go to Select Data. I have a love- hate relationship with this menu. I think there’s a little bit of a learning curve. All you need to know is: the thing you want to be in the front of your graph needs to go on the bottom of the list.

I’ve clicked on the average here. I’m going to tap down, tap, tap, tap, tap, tap with the arrows. Is that at the bottom? Yeah. The thing that’s on the bottom of the list is going to be on the front of your graph. Bottom of the list, front of the graph. Super counterintuitive. You would have thought that Microsoft would do it the other way around, but they didn’t.

Okay. I put the average on the bottom of that list. [00:03:00] Therefore, it’s on the front of the graph so people can actually see it. If you want to download this and click around and explore some more, check below the video. I’ve got a link where you can grab it for free.

Don’t forget to like, subscribe, and share.