The data you display in charts often takes on new meaning when you display it in the context of other data.
One way to add context is to display charts nearby that show related data. That way, we can ask questions like, “Why did this trend go up and THESE trends go down?”
Another way to add context is to plot the contextual data in the same chart. But when you do this, you often need to peel away the less-relevant content so you can show the most-relevant content without overwhelming your readers.
For example, several months after Covid-19 struck, I noticed that the Federal Reserve Economic Database (FRED) maintains an index of Total Share Prices for 43 countries. So I wondered how the trend in US share prices compared to those in other countries.
When I first created the chart on the left, below, the Chart Area was overwhelmed by content that I couldn’t afford to care about: the legends. In fact, the legends took up so much room that there was virtually no room for the Plot Area. So I decided I didn’t need legends, and I deleted them. And because I never use the Chart Title anyway, I deleted it, as well.
Now I could see more clearly how one point overwhelmed the rest of the chart, as you can see on the right. Checking the data, I realized that the April value for Belgium was about 15 times larger than all of its other values—and was obviously a data error. So I decided I didn’t need Belgium, and I deleted it.
After I peeled away Belgium, I then was overwhelmed by all the colored lines you see here. But without the legends and a much larger chart, I couldn’t tell which line belong to which country in the first place.
So I needed to peel away the color distinctions, as well. That is, I needed to use the same colors for all lines other than the line for the US.
To change those colors as quickly as possible, I first selected the Chart Area by clicking on the edge of the chart object. I held down the Ctrl key then tapped the Down-Arrow key once to select the line with the highest plot order, which had a value of 42. (The plot order is the last argument of the SERIES formula in your formula bar.)
I changed the line color to the color I wanted, held down the Ctrl key, tapped the Down-Arrow key, then tapped Y again (with my Ctrl key still down) to issue the Re-Do command; I tapped the Down-Arrow again, and so on…typing Y, Down-Arrow, Y, Down-Arrow, and so on, with the Ctrl key held down.
Then I set the width of line 42 to 1.75, and repeated the Y, Down-Arrow process to set the width of the other lines to the same value.
Because I had made sure that the column with US data was the last column of data, it was the last to be plotted, and was on top of all the other lines. That is, the data for the US was the line for plot order 42. So I changed it to a thicker black line, as you can see here:
As you can see, share prices in all 43 countries dropped significantly with the arrival of Covid-19. And most rose somewhat in April and May.
The gray vertical line marks the inflection point for all 42 country’s stock prices. From an Excel perspective, the line is an XY (scatter) plot, charted on the secondary axis. You should know automatically that it’s an XY plot, because line plots never can be vertical.
And you should suspect that the XY plot is on the secondary axis because it touches the top and bottom of the plot area. Putting it on the secondary axis is the only way to plot the line like that while still allowing the chart’s Y axis to adjust automatically when a line exceeds the current value of the Y axis.
As usual, I designed this chart to use Power Query and my Productivity best practices to update the chart with one command.
