Making a correlation matrix by dragging

Question

This is Sheet1

	A	B	C	D
1	4	3	2	1
2	1	4	3	2
3	2	1	4	3
4	3	2	1	4

This is Sheet2

	A	B	C	D
1	=PEARSON(Sheet1!A1:A4,Sheet1!A1:A4)	=PEARSON(Sheet1!A1:A4,Sheet1!B1:B4)	=PEARSON(Sheet1!A1:A4,Sheet1!C1:C4)
2	=PEARSON(Sheet1!B1:B4,Sheet1!A1:A4)
3	=PEARSON(Sheet1!C1:C4,Sheet1!A1:A4)
4

The question is how can I fill up this matrix by using the drag ability for formula so that I only have to write one block (e.g. A1 in Sheet2) =PEARSON(Sheet1!A1:A4,Sheet1!A1:A4). I have tried OFFSET and INDEX, but those are both confusing and the solutions I found from various forums did not work for me.

Answer 1

That works! Wow. That is amazingly helpful.

Please, mighty excel wizard, explain to me exactly how you are using the OFFSET function.

Answer 2

Assuming there are 73 observations of 300 variables, enter into A1

=CORREL(OFFSET(Sheet1!$A$1,0,COLUMNS($A$1:A1)-1,73),OFFSET(Sheet1!$A$1,0,ROWS($A$1:A1)-1,73))

Replace the two instances of 73 with the number of observations for your data set.

Drag down to A300.

With A1:A300 selected, drag across 300 columns to column KN.

Answer 3

Have you tried using the Correlation tool of the Analysis ToolPak?

Choose Data (ribbon) > Data Analysis > Correlation ...

Answer 4

I don't mean to be rude, but I will be blunt. That isn't exactly helpful. I want to be able to drag down to get the input you suggest, and then drag across. My actually data set is ~300 columns. I do not want to do that manually...

Answer 5

Here's a possible partial solution:

Enter the following formulas in the leftmost column.

=PEARSON($A$1:$A$4,A$1:A$4)

=PEARSON($B$1:$B$4,A$1:A$4)

=PEARSON($C$1:$C$4,A$1:A$4)

=PEARSON($D$1:$D$4,A$1:A$4)

Select the four cells containing the formulas, and then drag the fill handle in the bottom right corner to the right.

When I entered the four formulas into cells A6:A9 and copied them to columns BCD, I got: