fbpx
Search
Close this search box.

Use arrow keys to select small, unreachable chart series [quick tip]

Share

Facebook
Twitter
LinkedIn

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.

chart-with-a-very-small-series

Now, usually to select the smaller, unreachable series, the steps I follow are,

  1. Select the chart
  2. Go to Chart Format ribbon and select the series name (as shown below)

using-chart-format-ribbon-to-select-items

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:

  1. Select one the taller, more prominent series
  2. Press either up  or down arrow keys few times to select the smaller series
  3. Done!

A quick demo of this feature.

use-arrow-keys-to-select-chart-elements

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.

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

24 Responses to “Use arrow keys to select small, unreachable chart series [quick tip]”

  1. Gonzalo says:

    Nice! I have been clicking as you all this time... Bright new day ahead of me!!

  2. Andy Pope says:

    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.

    • Chandoo says:

      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. 🙂

  3. Utkarsh says:

    I use zoom to select unreachable chart items. But its nice idea to use keyboard.Thanks Chandoo for simple but time saver tip.

  4. David B says:

    I select the series name in the legnd

  5. guitarthrower says:

    I have always used a right-click on the legend entry to get at formatting for the series.

  6. 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

  7. Oxidised says:

    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...

  8. Hui... says:

    @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

  9. Nikki says:

    Thanks I always struggle with this - going to be very useful 🙂

  10. K-Li-Ch says:

    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.

  11. […] 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 […]

  12. Mirdul says:

    It is really helpful & nice..!

  13. Rick says:

    This no longer seems to work in Excel 2016 🙁

  14. Patricio says:

    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?

  15. faye 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!

  16. En says:

    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

Leave a Reply