Excel users have at least two significant advantages over business professionals who rely on other analytical and reporting tools.
First advantage: We Excel users can quickly transform our data to reveal its hidden patterns.
Second Advantage: We can report those patterns in the context of other data, which helps our managers to see the likely REASONS for the patterns we discover.
The following figure illustrates my point.
Both charts plot the same underlying data: The monthly amount of Personal Consumption Expenditures in the U.S. for goods and services.
In the first chart, there’s no obvious pattern in the data—other than that both measures declined briefly during the Covid recession.
On the other hand, the second chart reveals two hidden patterns.
First, the long label for the Y axis of the second chart tells us that the two lines plot a value related to their percentages of total Personal Consumption Expenditures. But now that we think about it, their relationship necessarily is a zero-sum game. As one line increases (as a percentage of the total) the other line must fall. So that second chart makes it obvious that the two measures have an inverse relationship.
However, that “obvious” relationship is invisible in the first chart, which is the way we’d usually plot data like this.
Second, the chart on the right illustrates a significant change in the pattern: Until the Covid recession, services were taking an ever-larger percentage of consumer spending, and goods were therefore taking a smaller percentage.
But when Covid struck, the ratios of the two percentages flipped significantly—and virtually overnight. That is, spending for haircuts, travel, restaurants, theaters, and so on fell significantly. But the consumption of goods—as a share of the total—rose significantly. Amazon’s business skyrocketed during that time, for example.
Do you see what I mean about the importance of adding context—in this case, the Covid recession—to your charts and dashboards? Without that gray column, we would only be guessing at the reason for the sudden transformation of the two lines in the second chart.
From March through July of 2021, the two lines in the chart on the right seemed to be trending back towards their original relationship. But then—at least through October 2021—spending on goods began to rise again.
I doubt that goods and services will fully revert to their original pattern any time soon, for three reasons. First, as supply-chain problems are corrected in the months and years ahead, there’ll be more stuff to buy, and—we can only hope—for lower prices.
Second, Covid variants, and its lockdowns, might well last for years. And third, Covid has changed the purchasing habits of consumers, habits that could last for years after the virus has been eradicated—if it ever is.
You can download the workbook at this link.
The Staging Table for the Interesting Chart
The figure below shows the left side of the staging table for the second chart in the previous figures.
Cell E2 contains the name of the Table. Because Table names appear in Excel’s Name Manager dialog, I use the “zt.” prefix to make Table names appear in the very bottom of the dialog when the names are sorted alphabetically.
When I begin a new staging table like this, I often set up an Index column like t.Index. In this case, I didn’t use it, but I’ve left it because I might use it in the future.
The t.Date column just contains monthly dates.
The Val1A and Val2A columns contain the values of services and goods, divided by the amount of Personal Consumption Expenditures (PCE) for the month. Allowing for rounding differences, the sum of the goods and services equals total Personal Consumption Expenditures each month.
Cell I1 contains the first month of data for services. And then, beginning in cell I4, the formulas in each row divide the current row’s values for services by the beginning value in cell I1. That is, the Val1B column currently shows the growth index from October 1, 2015.
Val2B, in column J, works the same way, but for goods.
As this figure illustrates, the last four columns in the staging table are the ones plotted by the second chart. (I often use a green fill to indicate the data that my chart references because “green equals graph.”)
The t.DateText column displays the date text every 12 months, on the month of the beginning date. And it displays a null string for the other months of the year.
The t.Val1 and t.Val2 columns display the values from Val1B and Val2B respectively. However, if any of those values equals zero, the formulas in t.Val1 and t.Val2 return #N/A instead. That #N/A causes the two lines in the chart to bridge the gap caused by any missing data—rather than plotting zeros for missing data.
And finally, the t.Recn column returns zero for each month not in a recession and 1 otherwise. The chart displays this column on its secondary axis.
You can download the full workbook at this link. After you do so, you’ll be able to update it monthly with Power Query.
The U.S. Bureau of Economic Analysis (BEA) typically updates this data between 25th and the 30th of each month. But you can run the Power Query update query any time you want. To do so, go to Excel’s Data, Queries & Connections group and then choose Refresh All. Alternatively, you could use the shortcut Ctrl + Alt + F5.
