I need some help with a chart I’m making in Excel. The chart plots the PPI on the primary axis and I’m trying to add recession bars to the background of the chart using a secondary axis. The problem is that the recession bars are not in the same order as the PPI data. For example, the PPI data goes left to right (1985 to 2015) while the recession bars are going right to left (2015 to 1985). I cannot figure out how to flip the recession bars so that they also go from 1985 to 2015 right to left.
Any Excel chart gurus who can help me with this? Here is the Excel file:
I’m not sure why this should make a difference, but it seems that the problem is that your dates are not in Excel Date format and instead are just strings that look like dates to human eyes.
However, if I make a column that turns your string dates into Excel dates, and then plot the chart with the new dates as the X axis, it comes out right.
You likely already know how to do that, but just in case, what I did was create a column with the formula (example from row 3): =DATE(VALUE(LEFT(C3,4)),VALUE(RIGHT(C3,2)),1)
This turns the string date into an excel date.
Next, I went to format, and created a custom format for those cell values that reads “yyyy-mm”, so these dates would read the same as your original strings.
Then I plotted them, and the order came out right.
It still is odd to me because your strings ought to be sortable and work fine. I dont’ get why the date format should make such a difference here. Even if they are read as categories rather than dates, you would think that everything in a row ended up being put in the same category.
However, changing to date format seems to work - and it’s probably all you need.
EDIT: Once you’ve prettified your chart, you may also want to set the bar gap for your recession bars to be 0%. That should get rid of the gaps between months of continuous recession. It’s under Format Data Series -> Options -> Gap Width
Another option would be to take the Recession series off the secondary axis, remove all the zeros from the series and leave them blank, then manually adjust the scale to your preference.
Thanks bchad! I changed the formula in C3 and it’s working great:
=DATE(VALUE(LEFT(B3,4)),VALUE(MID(B3,6,2)),1)
Now I just need to figure out how to get rid of the white lines in between the recession bars. I’ve changed the gap width and the series overlap to 0% but that did not fix the issue. Any other ideas?
I think I had this problem before. You may need to set the bars so that they have the fill color you want but the *line* color should be set to None. I think it draws bars with a border color that can mess up the solid look even if the gap width is 0.
An alternative to None is to set the line color to be equal to the fill color, but then you’ll need to change both the line and the fill if you change your mind on what the recession bar color should be. So None is a little better.
ADDED: This worked on my copy of your sheet when I tried it.