When cleaning data files in Excel, one never moves away from manually pushing data around.  Manually entering data by hand, sorting, and/or dragging and dropping in a new location; they all take time.  But for small amounts of data, a manual operation may be quicker than programming the spreadsheet to do it for you.

Even in this simplified example of reshaping the Bureau of Labor and Statistics (BLS), CPI data, there are lots of data handling tricks and hacks that support the major data shifts in this exercise.

To get the CPI data in the  downloaded format to a vertical column in chronological order, or time-series data, the following steps will occur:

1. Copy and transpose the table so column headings are years and the rows are months.
2. Create a new series of numeric data by filling blank cells.
3. Drag the first few columns of CPI table down to align with new month row headings.
4. Repeat dragging blocks of CPI numbers until all the dates are filled.
5. Data is ready to use.

Copy and Transpose

Insert a new blank sheet either by clicking on the ‘+’ on the right side of the worksheet tab row or right click on the current sheet tab and select insert.

Highlight the entire table of monthly CPI values from 2000 to April 2025.  Copy it to clipboard.  Go to Cell A1 of then new blank sheet.  To paste the data, the best way to learn all the options in Excel is to right click in Cell A1 and select ‘paste special’ just below the paste icons in the pop-up menu.  This brings up a menu of many options.  When formulas are not needed, I check the ‘values’ box.  Only the numeric or text characters will be pasted.  Then at the bottom right of this menu there is a transpose box.  Check that option also.  ‘Transpose’ flips the table’s original rows become columns, and original columns become new table rows.  Click OK.

Creating Numeric Dates

In the last post, ‘Excel Data Shaping Fundamentals File Prep – What the Function’, the format of downloaded file, months and years that are both formatted as text was discussed.  The CPI months are 3-character words and the years, even though they appear as numbers, cannot be used arithmetically.  They are formatted as text.  These values can be converted.  The conversion option was discussed in the October 14, 2024 post, ‘Magical Conversion of Text to Dates – What The Function’.  For our CPI transformation project, it is easier just to begin a new series.

There are many ways to fill cells in Excel.  My standard fill method it to type three values in three consecutive cells and drag the highlighted cells to the end of the series.  Excel fills them in.  In this exercise, typing, 1/1/2000 in Cell A16, 2/1/2000 in Cell A17, and 3/1/2000 in Cell A18 sets the base cells.  Highlighting these three cells, a small square appears in the lower right corner of the highlighted range.  Left-click in this small box and drag it down to April 2025.  Then release the mouse button.

Dragging and dropping

It is now possible to drag the columns in the transposed CPI table to the dates for which they align.  It is more efficient to bring as many columns as is convenient with the moving data.  This saves moving back to the original table longer distances as one moves down the worksheet, dropping each column in its respective year and month.  It is convenient to pick as many columns that can be seen on a single screen.

Highlighting years 2000 through 2009 – without the column heading year – only the CPI values and then dragging the block down so the first column aligns with the new dates (B16) for 2000.  Then release the block.

Highlight the block again, beginning with the column 2001 (through 2009) and drag to the next set of months and release.

Continue dragging and dropping for all 25 years.  It is not sophisticated, but in relatively little time the data gets transposed without having to use higher-level coding.

Validation

It is profoundly simple to copy errors than look correct in Excel.  Always look for ways to validate your work as you go.

• Check that the procedures have delivered the intended action.  (Are all the months in the rows? Are all the years in the columns?)
• Do the columns of data that are being dragged, or cut and pasted, align with January through December for each year.
• Do you end up with extra parts?  Or are data blocks misaligned.
• Are the headings matching with the data.

Look for ways to validate your data as you go.

In the associated video of this information, there are a few more tips and tricks discussed.  It is difficult to document every step, but it is worthy of leaping into the objective of sharing all that can be.  Excel functions on objects.  Every identifying component is an object that serves as a container of settings.  These include cells, worksheets, charts, and ranges.  As the implications of object boundaries evolve, activating a range in a formula allows dragging a highlighted range as an alternative to typing or reentering the entire range.  Shortcuts are valuable, until they aren’t.   It would be very difficult to cut and paste in a file with 1,000 rows or columns.

The next What the Function post will cover the INDEX/MATCH() function to reshape data.  It is on the other end of the data toolbox relative to sophistication.  But it is so powerful that it is worthy of attention.

Topics covered in this post:

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