Excel will add curly braces around the function, and the seven values will appear across your selection.

Because this function will return many answers, you have to hold down Ctrl+Shift while you press Enter.

Unlike INDEX, dollar signs are not necessary in this formula. The trick is that a single function has to be entered in many cells at once.

Copy B2 across to transpose with a formula.Īlternate Strategy: It is slightly harder to use, but the TRANSPOSE function will perform the same task as COLUMN.

Even if the formula starts in column XFA, you will still point to A1 in order to return the number 1. In this example, the first formula is in column B. Gotcha: You need to use A1 as the reference for the COLUMN function no matter where you are entering the formula. Therefore, if you use the formula =INDEX($A$4:$A$10,COLUMN(A1)) in cell B2, you can easily copy it across the columns. As you copy, Excel will take care of the rest. Any time you need to fill in the numbers 1, 2, 3 as you go across a row, you can use the =COLUMN(A1) in the first cell. While this is not entirely amazing, the beautiful thing about this function is that as you copy to the right, =COLUMN(A1) will change to =COLUMN(B1) and return a 2. The formula =COLUMN(A1) will return a 1 to indicate that cell A1 is in the first column. It would be cool if there were a function that could return the numbers 1, 2, 3, and so on as you copy across.

Strategy: You can use the INDEX function to return the nth item from the A4:A10 range.

Dragging the fill handle will fail here.

If I drag this formula to the right, there is no way that it will pull values from A5, A6, A7, and so on.

Below, you can see that the first formula in B2 points to A4. It is not practical for me to use Paste Special, Transpose every day. I need to use formulas to pull this information into a horizontal table. Problem: Every day, I receive a file with information going down the rows.

Excel convert column to row