I was at a conference earlier this fall and someone asked me whether it’s possible to create an Excel chart where only the first (top) y-axis label has a percentage sign format while the others were just number labels. In other words, instead of adding percentage signs to every y-axis label or putting the phrase “Percentages” in the title or sub-title, she wanted a single percentage sign. Something like this:
Interesting challenge, I thought. You can’t select single axis labels in Excel, so you’ve got to go about it a different way. But don’t give up hope! There is a way! And it’s not too difficult, though it’s a little time consuming. All we’re going to do is combine the column chart with four separate scatterplot series and use the names of those scatterplot series to label the y-axis. You may think there are a lot of steps to this task, but they are all pretty minor and once you’re done you’ll be ready to combine charts and use scatterplots for a lot of different tasks.
Here we go. Create your basic column chart and you’ll get your standard y-axis labels. However many gridlines you are going to use (if you use them!) will directly correspond to how many separate scatterplots you are going to need. For this example, I’ll use four increments, and thus four separate scatterplots. We will attach them to the secondary x- and y-axes and add a left-side data label. That’s it. Here’s my data:
Create your column chart and then add the first scatterplot series by selecting data and adding this series. It’s important you refer to cell D3 in the name field.
When you click OK, you’ll see that you have a new column. There are now a bunch of little steps:
1. Select that column and change it to a scatterplot.
2. Select the point, right-click to Format Data Series and plot the series on the Secondary Axis.
3. Show the Secondary Horizontal axis by going to the Axes menu under the Chart Layout button in the ribbon. (Notice how the point moves over when you do so.)
4. Right-click in your chart and choose Select Data. You now need to add the x-values to the scatterplot series (remember, we named it “100%”). Once you do that, you’ll see that red square move to the top-left corner of your chart.
5. Change the maximum value on your secondary y-axis to 100 by selecting and formatting the axis.
6. Let’s do a few things now: Turn off the secondary x- and y-axes by selecting each axis, formatting each, and turn off the labels, tick marks, and lines. Don’t just delete the axes! That will undo your work.
7. See how that point is up there? Now, select that point and add a data label. Once you’ve added it, format it and instead of using the Y value as the label, use the Series Name, and move it to the left side of the point.
8. Let’s now add our other four scatterplot series. (This is actually the long way to do this. For this specific chart, you don’t need to add four separate series; see the Note at the bottom of the post.)
9. Now, we can do some styling. Set the increments of the y-axis to 25….
10. Add data labels to each point and move them to the left (you won’t need to change the format from Y value to Series Name as we did before because the value is the series name)…..
11. Set the Marker Style to No Marker for each series….
12. And now set the y-axis labels to missing and resize your y-axis a bit to fit the entire 100% label.
13. Move the y-axis around and style the chart as you like.
Note: You don’t necessarily need all 5 separate scatterplots. Because you can use the y-axis value as the data labels for the 0, 25, 50, and 75 points, you can have one scatterplot point for the ‘100%’ label and then another single series for those points.
You can extend this methodology to have other labels on the y-axis and then move the label wherever you’d like.
I hope you found this tutorial useful and please let me know if you have any questions.
Want to know an easier way?
Start with your column chart, one series, labels from 0 to 100.
Select the axis, press Ctrl+1 to format, select Number, apply the following custom number format:
[=100]0″%”;0_%
[=100] means apply the following format to a value of 100. The format is 0″%”, meaning display as a whole number (100) followed by the text in quotes, “%”. So 100 is formatted as “100%”. After the semicolon is the formatting for any other numbers. The underscore means insert a blank as wide as the following character, so 0_% means display the value as a whole number plus a space as wide as the percent sign. This space will cause all numbers to align vertically while only putting the percent sign after 100.
I need to create a graph with weekly dates across the bottom and various labels instead of numbers up the y axis. The children learn one skill, and then need to retain that skill and learn a second skill, then a third skill. I would like to plot those skills. How do I change y axis to labels rather than numbers?
If I’m understanding correctly, the values ranked along the vertical axis are not tagged to values, correct? So they are just placeholders, like a dot plot, right? In that case, you should be able to use this approach but instead of the data label for each point, use the series values or categories.
My axis values vary from 0 to o.2, but I would like to highlight 0.02, 0.05, 0.10 and 0.20 I have tried:
[=0.02]0.00″*”;[=0.05]0.00″*”;0.00_; works to highlight 0.02 and 0.05 but [=0.02]0.00″*”;[=0.05]0.00″*”;[=0.1]0.00″*”;0.00_;
Custom number formats are for displaying labels which are in place. You can’t use them to tell Excel where you want labels. And you also only get to specify a couple of the conditions.
To specify labels which are not evenly spaced out, you would use the approach in the article, with dummy points at 0, 0.02, 0.05, 0.1, and 0.2. Hide the points and apply data labels to the hidden points.
Thanks for all of your help!
Can I add different spacing between just a few of the items on my Y axis, so some of the items look “grouped” visually?
Bridget –
The same way I suggested non-uniform dummy points at 0, 0.02, 0.05, etc., you can use non-uniform points at whatever Y values you like, to get the grouping appearance you want.
How I can remove the bottom number “0” from the Y-Axis.
Oh, yes! This is awesome! Love those custom formats. Definitely need to up my game on those. Thanks, Jon.
What if you want the Y axis label to be sorted decreasing i.e. instead of 0, 25, 50, 75, 100%, it should be 5, 4, 3, 2, 1?
Jim –
In this case, use your regular Y values, but format the Y axis, and check the box that says Plot Values in Reverse Order. You may also want to change the Horizontal Axis Crosses setting to Maximum.
I want my chart in PowerPoint 2010 to be scaled from -5 to 20. But, I only want to display axis labels that are greater than 10.
I can Rube Goldberg it by putting a white box over the labels, but that is unacceptable for printed copies of the presentation. I can also change the font color to white, but to my understanding that would only work for values in one of the three categories 0.
Your help would be greatly appreciated.
Rich –
Format the axis, and enter this as your custom number format:
[>=10]0;;;
Hi Jon,
I am trying create chart in which y-axis should show percentage (0-100) whereas stacked columns should show the actual count rather than the percentage.
x-axis displays the month
Each stacked column should sum upto 100 to show the various types encountered in a month.
Hi Mansi,
Just to make sure I’m understanding this correctly, do the data all sum to the same amount? In the chart you attached, the values in the different months sum to different amounts. I can think of a few ways to handle this, but want to make sure I have it right before digging in.
Thanks,
Jon
If you want the labels on the stacked bars to show the actual amounts, and the axis to show percentage, I assume you want each stack to add to 100%.
In this case just make a stacked 100% column chart. The axis goes from 0% to 100%, and if you add data labels, they will by default show the counts.
Hi guys. Is there any way to change Y axis numbers order? As you can see now it is 0 to 2500, my boss wants it to start at 2500 and end with 0, thus 2500 will be on the bottom and 0 will be on top. Killed 5 hours trying to figure it our, but still couldn’t. Anyone knows how to make it happen?
Hi Damian,
Try plotting them as negative numbers and then use a custom number format to make them appear as positive numbers. So, select your y-axis, and right-click (or CTRL-1 keyboard shortcut) to the Format Axis menu. In the Number menu, go to the Custom section. You’ll see that the default format is ‘#,##0;-#,##0’ (no quote symbol). Change this to ‘#,##0;#,##0’ (no quote symbol) and you should be set (though you may need to delete or much with the x-axis.
Jon
Hi Jon! It worked for me very well! You are awesome, thank you!
Format the axis, check “plot values in reverse order.”
Hi,
How do i highlight a specific point on the Y axis in a different font color than the rest? Example 80% is the targeted percentage I need my teams to meet. How do I change just that 80% on the axis to lets say Bold and Green?
Michelle –
You would use Jon’s original approach, which puts a data point with a custom data label (your Bold and Green 80% target) along the axis.
I just figuered it out. I was putting in 80 versus 0.8. The following worked:
[Black][=0.8]0%
Thanks
Is there a way to autoscale my dynamic graph to make my primary max to be 100x that of the max for my secondary axis? For accurate analysis purposes Mn samples are supposed to be analyzed if they are 1/100 of Fe samples.
See file
The article was rather catching and interesting enough to get all possible nuances to
recall. I do enjoy reading the material and
the writing mode of the author, etc as I did when finding https://deanrenfrosays.com/. I advise you
to write such kinds of articles every day to give the audience like
me all the essential information. In my view, it is better
to be prepared for all the unexpected situations beforehand, so thanks, it was pretty cool.
How I can remove the bottom number “0” from the Y-Axis.
Mohsin –
Use a number format that suppresses zero. A number format has up to four parts, separated by semicolons: positive values, negative values, zero values, and text. Apply this number format to hide the zero:
0;-0;;
This format puts a hyphen where there is a zero:
0;-0;-;