| Tip of the Month Archive |

Converting Blocks of Data to a Single Column

Frequently data sources present information in "block form." This is illustrated in worksheet 'sheet1' where monthly data are available in rows of twelve columns with each row representing a year of data. It is usually convenient to have the data reorganized as a single sequential column of data.

excel screenshot

The data reorganization can be easily accomplished on a second worksheet using the OFFSET function. First fill column A with monthly dates. Then enter the function

=OFFSET(Base_cell,Year(A1)-Year($A$1), Month(A1)-1,1,1)

in cell B1, where Base_cell points to the upper left-hand data position of the block of data. Finally, drag the function in cell B1 down column B to fill in the column of data.

excel screenshot

The same reorganization can be accomplished for quarterly data arranged in individual year rows as illustrated in sheet3.

excel screenshot

In this case construct a date column on a new worksheet using the final month of each quarter (March, June, September and December). Then enter the function

=OFFSET(Base_cell,Year(A1)-Year($A$1), Month(A1)/3-1,1,1)

in cell B1, where Base_cell points to the upper left-hand data position of the block of data. Finally, drag the function in cell B1 down column B to fill in the column of data.


Recently Viewed Series


Subscribe to our newsletter for updates on published research, data news, and latest econ information.
Name:   Email:  
Twitter logo Google Plus logo Facebook logo YouTube logo LinkedIn logo