copying vertical and pasting horizontally in excel for mac

Copying Vertical And Pasting Horizontally In Excel Download The Survey

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.

Select the vertical data.

Type Ctrl C to copy.

Type Alt E, then type S to open the Paste Special dialog.

Choose the Transpose checkbox as shown in Figure 1.

Click OK.

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.

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.

In Figure 2 (below), the goal is to set up links from the Report worksheet back to the Rates worksheet.

This is very unusual; most formulas go in a single cell, not in a range of cells.

It should contain the same number of cells as you selected in the first step, but in a different orientation.

Note that Ive used View, New Window and then View, Arrange, Vertical to allow me to see both worksheets side by side in these figures.

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.

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.

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

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

Post your question to the Community Center on the right to enter.

Jelens next Webcast for CFO, on February 22, will cover what-if scenarios and Monte Carlo analysis for forecasting and planning.

Copying Vertical And Pasting Horizontally In Excel Download The Survey

Download the survey results and reveal: Before the coronavirus crisis, nearly 9 in 10 companies were considering expanding into new countries Most executives said their tax departments were not well-equipped to address cross-border concerns Six out of 10 companies were considering or engaged in reviews of their legal entity structures.

However, now Im unable to sort the data and Ive learned because there are reference values.

Any suggestions you have to fix the sorting issue after flipping the data (per your example) I appreciate.

CFO Publishing LLC, a division of The Argyle Group.

Broad Street, New York, N.Y.

Copying Vertical And Pasting Horizontally In Excel For Mac