Method #3 – Simple cell references (dynamic) Press CTRL-Shift-Enter to update the TRANSPOSE array formula.īecause this is a formula, when data changes in the original “vertical” list, we see the same change to data in the “horizontal” list.select ALL the original cells that held the TRANSPOSE function ( D3:O4) and click in the formula bar to return to edit mode.If we use the Fill Series handle to drag the array formula from column M to column O, the additional cells have data, but they are not arranged properly. Step 2: Enter the formula below ( do NOT hit ENTER!!!): =TRANSPOSE(A3:B14)Īs we can see, we did not select enough cells to support the returned data. Step 1 (revised): Highlight a range of cells that you believe can support the returned values (i.e. This will require a small bit of planning on our part before we write the formula. TRANSPOSE is an array formula, and because of this, we need to select beforehand a range of cells that will serve as a landing zone for all the possible answers. If we edit the TRANSPOSE function and click at the end of the formula, we can press F9 to see that the results are being collected and stored in the response. The reason for this is because the formula is attempting to display all the data from the selected range within a single cell. Notice that the formula returns a #VALUE! error. Step 1: Select cell D3 and enter the following formula: =TRANSPOSE(A3:B14) Method #2 – Using the Transpose function (dynamic) If data is changed in the original “vertical” data set, the corresponding entry in the “horizontal” data set will not reflect the change. The downside to this method is that it is not dynamic. NOTE: An alternative to this is to select the data and click Copy, click the destination cell, then click the lower part of the Paste button and click the Transpose. Step 2: Select the destination cell that will serve as the upper-left corner of the transposed data ( D3) and right mouse click and select Paste Special.Ĭheck the box labeled Transpose and click OK. Step 1: Select the data ( A3:B14) and press CTRL-C or the Copy button.
0 Comments
Leave a Reply. |