Anyone who has worked with Excel charts extensively has inevitably encountered the “hard to reach” scenario. That is, the formatting and configuration of your chart makes certain chart elements seemingly unreachable. Today, I’ll show you a forehead-slapping trick that will both give you relief, and make you feel stupid at the same time.
Take a look at this chart. Here, I want to create a “Thermometer” style chart where Org1 and Org2 are stacked on top of the Total Goal series.
In order to do this, I’ll need to place Org 1 and Org 2 on a Secondary Axis.
This is easy enough. I simply right-click on the Org2 series, select Format Data Series, and then choose the Secondary Axis option.
But the Org2 series is now covering the Org 1 series. I can’t seem to select Org 1 in order to complete my chart.
The fix for this problem is easy. Simply click on your chart and go to the Layout tab. There, you’ll find a drop down selector on the far left. This drop down selector allows you to select every chart element you can think of. All I have to do is choose the element I’m looking for – in this case, the Org1 series.
As you can see, the Org1 series becomes selected even though it’s hidden.
After sending the Org 1 series to the Secondary Axis (and applying some formatting changes) I get the effect I’m looking for.
There you have it. If you ever need to select a chart element that is “hard to reach”, you can use the chart element selector on the Layout tab.
AlexJ reminds me of the steps to do the same thing in Excel 2003.
“Select the chart, right click on the command bar area and ensure that the Chart menu is enabled. Same selector.”