Here is a fairly annoying problem.
Imagine a chart showing both sales & customer data. Sales numbers are large and customer numbers are small. So when you make a chart with both of these, it looks something like below.
Now, usually to select the smaller, unreachable series, the steps I follow are,
- Select the chart
- Go to Chart Format ribbon and select the series name (as shown below)
But this is a long process with significant click tax.
Here is a simpler alternative. Use arrow keys to select the series you want.
Here is how it works:
- Select one the taller, more prominent series
- Press either up or down arrow keys few times to select the smaller series
- Done!
A quick demo of this feature.
So go ahead and use ’em arrow keys to select & format any element in your chart.
Bonus tip: How to know which arrow key to press?
- After selecting the taller series, look at formula bar.
- It should read something like this:
=SERIES(Sheet1!$B$5,Sheet1!$A$6:$A$17,Sheet1!$B$6:$B$17,1) - Notice the last parameter.
- If it is 1, that means the other series is 2 (or 3 …). So you press UP arrow to increment.
- If it is 2 (or 3…), that means the other series is 1. So you press DOWN arrow to decrement.
Bonus Bonus Tip: How to select any individual data points in the series?
Use LEFT or RIGHT arrow keys to select individual data points in a series. This is an easy way to add data labels or change color of one particular data point.
How do you select unreachable chart elements?
I admit. I have been using the chart format ribbon to select unreachable items until today. But once I realized that we can use arrow keys, I feel empowered. While I am not a keyboard shortcut fanatic, I do believe that if there is a faster way to do something with keyboard alone, we should embrace it.
What about you? How do you select chart items? Please share your tips in the comment section.
More tips: on formatting charts, on keyboard shortcuts and quick Excel tips.
24 Responses to “Use arrow keys to select small, unreachable chart series [quick tip]”
Nice! I have been clicking as you all this time... Bright new day ahead of me!!
This is a bit old now,
http://www.andypope.info/tips/tip003.htm
Try xy-scatter with both sets of error bars applied.
Arrow up to get Y error bar
arrow down to get x error bar. but once there you can not arrow down any further.
Thanks Andy for the link. I am sure this feature has been around for many years. I just learned about it yesterday while playing with charts. 🙂
I use zoom to select unreachable chart items. But its nice idea to use keyboard.Thanks Chandoo for simple but time saver tip.
I select the series name in the legnd
I have always used a right-click on the legend entry to get at formatting for the series.
Thanks, Cool tip. I often forget myself when I am creating reports with graphs and try to use the arrow keys to move the position of the graph on the page! Of course this doesn't work and I have always found this a bit annoying. Now I can see that this really is a feature and not a bug 🙂
Up until now I have always right clicked on the legend to select the hard to get series, but I think I will use this in future.
Thanks
Nice tip!
Begs the question though, if the series is hard to reach, then it must be hard to read!! Hence a not-so-good chart.
So I guess the point of trying to reach this series (or element) is to re-format it to make the chart more presentable. For example, moving it to the secondary axis or changing chart type for that series...
You are right. That was the point of selecting such elements on the chart.
@All
Once you have a series selected using Chandoo's technique you can then use the Right/Left arrow keys to select individual points with the series
This is great for highlighting or formatting individual points, adding a label to a single pointy or changing the marker of a single point
Thanks for another discovery.....
@Hui, This is awesome !
@Abhilash
and it's been available in Excel since day .
Thanks I always struggle with this - going to be very useful 🙂
In my two series chart:
- Select the first series
- Up arrow key selects the second series
- Other Up arrow key selects Chart Area
- Other Up Arrow key selects Plot Area
- Other Up arrow key selects Legend, and so on with all chart elements.
[…] select, if you want to format it. I've always used the Ribbon commands to select them, but Chandoo shares a keyboard shortcut, that is much […]
It is really helpful & nice..!
This no longer seems to work in Excel 2016 🙁
Just use [ctrl] + [up/down arrow]
Thank you!
I used the arrows all the time, but they have stopped working. Well, they didn't stop all together, they do function, but I can't switch chart elements with them any more.
Any idea why?
Try ctrl + arrow keys as Stephane says
Help! How do you do the same trick in Excel for Mac? This functionality has been so helpful in Excel for Windows, but I'm unable to find the translation to Mac's Excel. Very frustrating. Really hoping you can help uncover this translation mystery!
How does this work with the new 'Waterfall' charts in office 365 Excel? Arrow keys are not working. When using Ctrl+Arrow, the whole chart is moving. In the waterfall chart, it is very important to select data point to 'set as total'. Can anybody find and help. Thanks