How to Transpose a Data Array in Microsoft Excel

Transposing data in Microsoft Excel instructs the program to return a vertical range of cells as a horizontal range or vice versa. “Transpose” must be entered as an array formula, which is a formula that calculates multiple values and returns a single result. You will use “transpose” to change the vertical and horizontal orientation of an array on a worksheet. Here are some steps to transpose a data array in Microsoft Excel.

Instructions

Difficulty: Moderate

Things You’ll Need:

• Computer
• Microsoft Excel

1. Open Microsoft Excel from your computer. This may be in your Start menu. Microsoft Excel is usually located in your Microsoft Office folder.
2. Click on a new document, or open an old one. You will draw the information you need to transpose a data array from here.
3. Select a range of data with a similar size to the size of the range containing the data, but with rows and columns reversed. In this example, we will call the rows and columns you need A1 through B4.
4. Type the following formula in the formula box: “=Transpose(A1:B4).” This formula creates a transposed link to cells A1:B4 in your Excel document.
5. Select the range A5:A7 starting with the formula cell. Press F2 and then press Ctrl+Shift+Enter. If the formula is not entered correctly (as an array formula) the single result will be 1.
6. Hit “Enter,” and you will see the transposed data appear in the selected cells. If you don’t see this right away, wait a second to make sure Excel is functioning properly. Try it again if necessary.

Tips & Warnings

• You must enter the formula as an array. To enter a formula as an array formula, press “Command+Enter.”
• Use the rows and columns recommended here as a sample. You can use any rows and columns you want, but to get a feel for the process, use these cells first.
• Always save your work as you are progressing with it. Microsoft Excel does have an Auto-Save feature, but there is no guarantee that your work will be saved up-to-date.
• Do not leave out the parentheses in the formula. You cannot transpose a data array without using them appropriately.

Who Can Help:

• Find Computer, Electrical and Telephone Services Here. Compare Up to 4 Quotes, Free Service & No Obligation.
• Find Electrical Services Here. Compare Up to 4 Quotes, Free Service & No Obligation.