This final lesson on shaping data in Excel is brought to you by the St. Louis Federal Reserve Bank, Federal Reserve Economic Data (FRED) website.  Reviewing the YouTube videos created for this data-shaping exercise, the 4 videos span 45 total minutes.  This is about the length of a college class period. Compared to real-time lectures, the videos are much more focused.  Any single technique used to move 300 CPI datapoints can be done by an experience data nerd in minutes.  Most of the video time was in explanations of the objectives and methods.

But nothing compares with the speed of the FRED data site.  Searching in a browser for FRED will get you to fred.stlouisfed.org pretty quickly. Searching the FRED site for CPI data yield one option, ‘Consumer Price Index for All Urban Consumers: All Items in U.S. City Average.’ The first occurrence is a CPI series that is seasonally adjusted. This will work, but we have been using the CPI, not seasonally adjusted. The difference is some seasonal transactions work better if the prices reflect seasonality.

In fine print under the seasonally adjusted option are the words ‘2 other formats’.  Click on that and select, ‘monthly not seasonally adjusted.’

BOOM! The same 1913 to April 2025 CPI data we downloaded from the Bureau of Labor and Statistics appears in a chart.  …With gray bars for recessions nicely illustrated!  This is nearly the graphic displayed on this post.  The difference is the date range.  Click on, and select January 2000, in the start range identified in the red box labeled, ‘#1’.  This one choice delivers the data file we worked on cleaning in the first video.

Clicking on the second red box ‘download’ button, opens a menu that allows data downloading to and Excel file format.  Downloading this file delivers the time-series data format that 45 minutes of videos illustrated how to transform.  It is even faster than the minutes required by an experienced spreadsheet operator.  Even faster than AI.  These days that is heresy, I know. (What is the analogue to artificial intelligence? Natural intelligence?).

But the point of the 45 minute of videos was to expand user technique, not speed.

The main point of this post is, BE SHARP!  The first occurrence of the dataset you are looking for may not be the most efficient.  Whenever I am looking for federal economic data, I start with FRED.  In part, because I am generally looking for data in a chronological, time-series format.  FRED also has state-level and international data for some series.

It is also a good time to review the data-shaping lessons and what we covered.

Lesson #1: Downloading and cleaning a public dataset.  In this case the not-seasonally adjusted, monthly CPI-U (urban) data.  Excel Data Shaping Fundamentals File Prep – What the Function.  The associated video can be accessed within this link.  Important points:

• How to download and clean public data, in this case BLS, CPI-U data.
• An awareness that dataset format, or shape, matters.
• Introducing numeric or value dates versus text dates that need to be managed.

Lesson #2: Moving data, reshaping, by dragging and dropping manually in a spreadsheet.  Excel Data Shaping Fundamentals Cut and Paste – What the Function.    The associated video can be accessed within this link.  Important points:

• Introduction to the ‘Paste Special’ menu and ‘Transpose.’
• Filling data series of consecutive values with three base values and then dragging.
• Reshaping data by dragging (or cutting and pasting).
• Introduction to data validation.

Lesson #3: Reshaping datasets with INDEX/MATCH(). This sophisticated nested function is very powerful and worth learning. Excel Data Shaping Fundamentals Index/Match – What the Function.  The associated video can be accessed within this link.  Important points:

• The INDEX() function – delivering cell contents when row and column indices match.
• The MATCH() function – matching new criterion with historical criterion.
• A text parsing function, LEFT() – pulls specified number of left-most characters.
• A text parsing function, RIGHT() – pulls specified number of right-most characters.
• Navigating data perimeters with End-arrow and Shift-End-Arrow keystrokes.   Very efficient way to move around and for highlighting large areas of the worksheet.
• $-sign anchors to keep absolute cell references absolute.
• Auto fill time-series data with text character dates.

Lesson #4: Reshaping datasets with VLOOKUP().  This is a traditional technique that is reliable and relatively intuitive.  Excel Data Shaping Fundamentals VLOOKUP() – What the Function. The associated video can be accessed within this link.  Important points:

• The VLOOKUP() function – searches a table list vertically.  Search criteria must be in left-most column and in descending order.  There are other versions, HLOOKUP (horizonal) and XLOOKUP (not directionally dependent).
• The TEXT() function – converts a numeric value to text.  In this case a numeric date to a text date version.
• The MONTH() function – pulls the value of the month from a numeric date.  In this case the numeric value of the search criterion was used to identify the ‘find’ column value.
• Navigating data perimeters with End-arrow and Shift-End-Arrow keystrokes.
• Filling dates by typing in 3 consecutive values and dragging lower right corner box to the endpoint.

Lesson #5.  Be sharp! Find best data format in desired data shape.  A good place to begin is with the FRED dataset.  Solid Coding ‘Natural’ Intelligence is Always Start with FRED – What the Function.  There is no video for this post.

Be fierce in your public fact skepticism. In this digital world, accessing public data has never been easier. Feed your passionate motivation to find authentic solutions. There is rarely only one side to a conflict. The worst-case scenario is recreating the analysis will help the skeptical analyst see a bigger world. Best-case scenario is that the initially provoking analyst will learn more and see a bigger world. After a career of challenging other’s analyses, I have found a bigger world far more often than I have changed offenders’ minds. It is a win-win. It is ok if I am the benefactor. It is ok if sometimes I help move the bar. It is all about the journey.