Home > Tips and Tutorials > October 2003

October 2003 Tip of the Month

| 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.

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.

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

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.