This Chart of Recessions by Country Uses
Excel Conditional Formats to Do the Work

Sometimes, the best strategy for creating an Excel chart is to not use a chart at all. Here, spreadsheet formulas, plus conditional formatting, set up a spreadsheet to serve as the chart.

by Charley Kyd, MBA Microsoft Excel MVP

A recent news report mentioned that Italy is in recession. Several days later, I read that Sweden has the same problem. Because this information surprised me, I wondered what other countries also are in recession.

The following figure shows the results of my research. I created it in a spreadsheet, using data from the Federal Reserve Board of St. Louis, returned by my Kyd Web add-in. The red bars on the green background are returned by conditional formatting, as shown in this dialog.

The FRB offers several types of data series that indicate recessions by country. For each period for a given country, the data equals 1 if a recession occurred, and zero otherwise. My formulas also returned -1 for periods where data is not yet available. (After March, 2012, in this example.) Then, in this "chart," I use Excel's conditional formatting to set the fill color for each of the three conditions (-1, 0, and +1).

To launch this dialog in New Excel, select the range to be formatted and then choose Home, Styles, Conditional Formatting, New Rule. Then change the Format Style to 3-Color Scale.

You can get a similar dialog in Excel 2003. To do so, choose Format, Conditional Formatting.

You don't need my KydWeb add-in to see the data. This page at the FRB's web site shows United States recessions since 1949. Their chart on that page isn't very useful, but you might find the data worthwhile. Also, the "Recession Indicators" tag on that page shows a variety of recession indicators for dozens of countries. 

If you want to take a closer look at the how I made it all work, I've set it up as Charley's Swipe File #41.