Hoe to Make Excel Cells Continue Horizonal Not Vertical

Editor's Note:

To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com's Spreadsheet Tips Page.

Dana A. wins an autographed copy of Learn Excel 97-2007 for this question posed during the recent Power Excel Webcast: "Is there a method to link data in a workbook from one sheet to another, when the first worksheet has data going vertically in a column, and the second worksheet has data going horizontally in a row?"

Dana's answer would be easy if it were not for the word "link." If you wanted to paste a static copy of the table, it would be easy to use the Transpose checkbox in the Paste Special dialog to turn the data sideways. To accomplish that maneuver, follow these steps:
1. Select the vertical data.
2. Type Ctrl C to copy.
3. Click in the first cell of the horizontal range.
4. Type Alt E, then type S to open the Paste Special dialog.
5. Choose the Transpose checkbox as shown in Figure 1.
6. Click OK.

Fig. 1

The problem with using the Transpose option in the Paste Special dialog box is that it pastes a static copy of the data. If the original vertical range changes, the pasted values will not update. Notice that the "Paste Link" button becomes "grayed" out when you choose Transpose.

Two solutions are available. The first involves an array formula and works whether you are going from vertical to horizontal or from horizontal to vertical. The second method avoids the array formula but requires a different formula depending on which way you are turning the data.

Using an Array Formula and the TRANSPOSE Function

I like this method because it work in all situations. In Figure 2 (below), the goal is to set up links from the Report worksheet back to the Rates worksheet.

Fig. 2

The first step is to select the entire range where you want the transposed data to go. In Figure 2, you can see that cells C2:N2 are selected. This is very unusual; most formulas go in a single cell, not in a range of cells. Next, begin to type the formula:

=TRANSPOSE(

Then, using the mouse, navigate to the other worksheet and select the range of cells (see Figure 3). It should contain the same number of cells as you selected in the first step, but in a different orientation. (Note that I've used View, New Window and then View, Arrange, Vertical to allow me to see both worksheets side by side in these figures. This is not necessary for the formula to work.)

Fig. 3

Next, type a closing parenthesis in the formula, then hold down Ctrl Shift while pressing Enter. The Ctrl Shift Enter key combination tells Excel to return an array of values from this one formula and to put the results in the selected cells. In Figure 4 (below), you can see that Excel added curly braces around the formula in the formula bar.

Fig. 4

However, some people don't like array formulas. It is hard to remember Ctrl Shift Enter, and you have to know to select the entire range of cells in order to later edit the formula.

Avoiding an Array Formula by Using INDEX and COLUMN

The INDEX function in Excel will solve this problem without using an array formula. This function offers a few different syntax options. To solve the current problem, you would use:

=INDEX(Rates!$B$2:$B$13,Row # to Return)
In cell C2, you would use: =INDEX(Rates!$B$2:$B$13,1);
In cell D2, you would use:  =INDEX(Rates!$B$2:$B$13,2);
In cell N2, you would use:  =INDEX(Rates!$B$2:$B$13,12);

Rather than editing the formula in each cell to change the row number argument, you could use COLUMN(A1) in place of the number 1 in cell C2. Sure enough, cell A1 is in the first column, so COLUMN(A1) returns the number 1. The magic happens when you copy this formula across; the A1 reference automatically changes to B1, then C1, and so on, returning the numbers 1 through 12 as you move from C2 to N2. (Note that the "A1" in this function has absolutely no correlation to the location of your target cells in C2:N2 nor to the source cells in B2:B13. Even if you are entering this formula way out in XFD987654, you will still point back to any cell in column A in order to return the number 1.)

Then follow these steps:

1. Select the first cell in the target range.
2. Type =INDEX(.
3. Using the mouse, highlight the source range.
4. Press F4 to add dollar signs to the source range reference.
5. Type a comma, then COLUMN(A1), then a closing parenthesis.
6. Simply press Enter, since this is a regular formula.
7. Copy the first formula across to the other cells in the target range.

This formula works (see Figure 5, below) when you are converting from a vertical range to a horizontal range. If you need to go from horizontal back to vertical, then you will use a different variation of the INDEX function. In this case, you will specify the source range as the first argument, type a 1 for the second argument to always return the result from the first and only row of the source range, then use ROW(A1) as the third argument to specify which column should be returned (see Figure 6).

Fig. 5

Fig. 6

Bill Jelen is a CFO contributing editor and author of 32 books about Excel. You can win a copy of one of his books if your question is selected for a column. Post your question to the Community Center on the right to enter. Jelen's next Webcast for CFO, on February 22, will cover what-if scenarios and Monte Carlo analysis for forecasting and planning.

waughloctionly.blogspot.com

Source: https://www.cfo.com/technology/2011/01/spreadsheets-the-vertical-horizontal-flip/

Share this post