By default, Excel will label TONS of increments along our y-axis scale.
And if we’ve already got the exact values within the graph itself, the y-axis can become even more cluttered.
In this video, you’ll learn how to adjust your graph’s vertical scale so that it only shows the minimum and maximum value. You’ll adjust the min and max “bounds” and the “units.”
You’ll also get bonus tips for setting up this chart type, the columns with a target/goal line. This is a “combo chart” in Excel. You’ll need to set up the table in a very specific format to get this chart to display correctly.
What’s Inside
- 0:00 Overview
- 0:24 Welcome to Dataviz On The Go
- 0:32 Setting Up the Table for the “Combo Chart”
- 1:54 The Edited Chart (Dataviz Best Practices)
- 2:16 How to Only Label the Min and Max
- 4:11 Download the Spreadsheet
- 4:20 Don’t Forget to Like, Subscribe, and Share
Download the Spreadsheet
It’s here: https://depictdatastudio.ck.page/onlylabelminandmax
Read the Transcript
Ann K. Emery: [00:00:00] In this video, you’re going to learn how to only label the min and the max on your scale. Like this. Instead of putting the labels on the columns and the scale, we only need the min and the max on the scale. Otherwise, it would be redundant. If this seems like it might be relevant to you and your workplace, stick around, I’ll give you some context, and then I’ll get into the how tos.
It’s Ann Emery. You’re watching Dataviz on the Go, the series where I make quick tutorials as I’m racing around between my meetings and my workshops. All right, let’s get into the context and then the how to’s. This is how you’d have to set up your table for this style of graph. You would list out your fictional project names in this example, the actual number that you want to appear in your columns or a percentage or a currency, and then you’d repeat the target
over and over and over in the third column, you’re going to arrange them least to greatest so that you get that [00:01:00] order in your chart least to greatest. You’re going to add an intentional gap in your table. That’s on purpose because guess what? Yep. You guessed it. It creates an intentional gap in your chart.
And then the final thing you need to know here is you’re going to have this target appear one extra time at the end. Okay. Because that’s what helps the line extend a little bit beyond the columns so that you can add a nice direct label right here. Okay. That’s the gist of how you need to set up your table for this style chart, which is the columns with the target line or the goal line.
It’s going to be a combo chart. So you’re going to highlight your table. You’re going to insert a combo chart. You’re going to get this mess. Which isn’t helpful and doesn’t tell us everything. And also has the super redundant, super detailed scale right here as well. The edited version is going to look mostly like this grouping by color, a nice dark light contrast here.
You’re [00:02:00] going to gray out the target line because that’s the sidekick. That’s not the main character. You’re going to add some words. You’re going to add a little bit of math. You’re going to add some icons. Okay. It’s pretty much done. It’s pretty much done with the exception of the overly labeled scale like this.
So the how to’s, let me copy and paste this chart. So as usual, we can admire them side by side, and here’s where you’re going to click. You’re going to click on the. The numbers on the scale. You have to be really careful here. Don’t click on the white space between the numbers, click on the actual number, click on the numeral on the actual font color here, not in the white space.
Okay. You click there. You see the rectangle appear around the outside of the numbers. You do a right click, you go to the bottom of your list right here. Format axis, and it pulls up this sidebar for us. You can see the min and the max. That’s pretty easy to follow, right? If the biggest number is a [00:03:00] 951, let’s make it extend to a thousand.
Nice, even round number, a little bit bigger than the biggest number. This is the part you have to adjust right here. The units, the units mean how often do you want a demarcation. Excel guesses. It’s gonna guess too much. It’s gonna guess too often. It guessed right here. It said, how about every hundred? And I’m like, nope, because why?
Why? Right? If you already have the exact values on the columns, you don’t need the numbers to estimate the values because there’s no estimation needed. They already have the exact values. You could do something like I don’t do this. I’m just going to show you what this does. If you insert a 250 here, then you get a demarcation every 250.
So if you only want to label the min and the max, then whatever your max value is, that’s what’s going to go in this box. Got it? If your scale goes from zero to a thousand, then you want a demarcation [00:04:00] every Thousand units. You press enter, it shows you every thousand. Now it’s perfect. We’ve got the min and the max on the scale, plus the exact values on the columns.
If you wanna download this spreadsheet and dig around and explore and see how I set everything up, look below this video and there’s a link to download it. Don’t forget to like, subscribe and share.
Leave a Reply