How to Transpose an Address List from Rows to Columns in Excel

This guide will explain how you can use an Excel formula to transpose an address list from rows to columns.

We will use the ROWS, COLUMNS, and OFFSET function to build a transposed table from a single column address list.

Let’s look into a quick example of an address list that we can convert into a table.

In the example below, our address list is arranged in a single column, with each field stacked over each other. Despite not being in a table format, we can still make sense of the data by providing a few constants. 

example of a single-column address list

 

For example, we know that there are exactly three columns in the final result: Name, Address, and City. We also know that each column should have six rows, not including the header. Given these constants, we can create a formula that can transpose the data into a table.

reformatted address list

 

Before we explain how you can achieve the result above, let’s look into a sample sheet that uses the formula.

 

 

A Real Example of Transposing an Address List from Rows to Columns in Excel

Let’s take a look at a real example of a sheet that converts a single-column address list into a table.

In the example below, we have an address list with three properties. Each property contains six entries. In the range D2:D4, we provided a few properties of our address list. Given these constants, we used an Excel formula to transpose the list in Column A into an actual table.

transpose address list in Excel

 

To get the table in the range C7:E12, we can use the following formula:

=IF(OR(COLUMNS($A2:A2)>$D$4;ROWS(A$2:A2)>$D$3);"";OFFSET(INDIRECT($D$2);(COLUMNS($A2:A2)-1)*$D$3+ROWS(A$2:A2)-1;0))

The formula above is placed in the top-left cell, and the user should drag the formula to fill out the rest of the range. 

The formula first uses the IF function to check whether we should output a value. The ranges $A2:A2 and A$2:A2 increase in size due to AutoFill. If the ranges increase to a value greater than the constants provided in cells D3 and D4, the cell will return an empty string.  In the example above, the cell F13 will evaluate the following formula:

OR(COLUMNS($A8:D8)>$D$4;ROWS(D$2:D8)>$D$3)

Since the number of columns in the range $A8:D8 is greater than 3, cell F13 returns an empty string.

What happens when the ranges are within the indicated bounds? This is where the OFFSET function can help us. The OFFSET function allows us to return the value of a cell that is a certain number of rows or columns away from a reference cell.

In this particular use case, our reference cell will be cell A2. Since we know that there are six rows per column, including the header, we know that adding a multiple of six will give us a different column of the same entry. Similarly, we know that we can add 1 to the row number to proceed to the next entry in a particular column.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out this OFFSET function in Excel, let’s learn how to start writing it ourselves!

 

 

How to Transpose an Address List from Rows to Columns in Excel

This section will explain each step needed to transpose a single-column address list in Excel. You’ll learn how we can convert rows to columns using the OFFSET, ROWS, and COLUMNS functions.

  1. You can follow the example below to create a dynamic sheet for transposing address lists easily. The range C1:D4 will help you set up how the formula will reformat our address list.
    set up sheet to include address list info
  2. Next, fill up the address list properties we need. For this example, we know that we have three columns with seven rows each. We also know that our table starts with cell A2.
    fill in address list info
  3. We can now add the formula shown in the previous section. Make sure that the address list information aligns with the cell references in the OFFSET formula.
    add formula to transpose address list in Excel
  4. Hit the Enter key to evaluate the formula.
    hit the Enter key
  5. We can use the AutoFill feature by dragging the formula until the whole table is captured in the new range. Since we have a check for cells that are outside the range of our address list, we can extend the formula as much as possible.
    final result of transposing address list in Excel

 

 

This formula should be all you need to transpose an address list in Excel. This step-by-step guide shows how easy it is to use the OFFSET function to transpose rows to columns.

This formula is just one example of a clever way to use different functions together in Excel to manipulate data. With so many other Excel functions out there, you can surely find one that suits your use case. 

Do you want to learn more about what Excel can do? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like