How to switch Columns and Rows Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2197157%22%20slang%3D%22en-US%22%3EHow%20to%20switch%20Columns%20and%20Rows%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2197157%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20use%20a%20formula%20%3DData!J%243%2C%20but%20when%20I%20spread%20it%20downwards%20it%20goes%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DData!J%243%3C%2FP%3E%3CP%3E%3DData!J%243%3C%2FP%3E%3CP%3E%3DData!J%243%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20its%20correct%2C%20but%20I%20wanted%20it%20to%20be%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DData!J%243%3C%2FP%3E%3CP%3E%3DData!K%243%3C%2FP%3E%3CP%3E%3DData!L%243%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20%22spread%20it%22%20downwards%20and%20change%20the%20letter%20instead%20of%20the%20number%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2197157%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I'm trying to use a formula =Data!J$3, but when I spread it downwards it goes

 

=Data!J$3

=Data!J$3

=Data!J$3

 

I know its correct, but I wanted it to be like this:

 

=Data!J$3

=Data!K$3

=Data!L$3

 

Is there any way to "spread it" downwards and change the letter instead of the number?

1 Reply

@ThiagoCFP 

One option is to use the TRANSPOSE function.

Let's say you want to refer to J3:Z3 (17 columns).

Select 17 cells in a column, enter =TRANSPOSE(Data!J3:Z3) and - if you don't have a recent version of Microsoft 365 - press Ctrl+Shift+Enter.

 

Another option: let's say the first formula will be in a cell in row 2, e.g. D2.

Enter the formula =INDEX(Data!$J$3:$Z$3,COLUMN()-1)

Fill down.

If the first formula is in row 5, use COLUMN()-4 etc.