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

Posted on April 30th, 2015 in Charts and Graphs - 17 comments

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.

 

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

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

Leave a Reply