When copying Excel formulas, can you swap around the way Excel treats columns & rows?

Confessedly the above title is a bit vague. This problem likely has a simple solution, but it's a bit difficult to explain in a sentence. To elaborate...

Essentially, for some work recently, I was confronted with something akin to the following table, ready-made, with which to conduct data analysis:

Data Table T Test - Copy.xlsx

On the face of it a very simple task to simply do T tests and averages for all the values in Tables 2 and 3 and copy them into table 1. A handy formula, easily copied into all the requisite rows, should have enabled me to do this quite quickly.

Unfortunately, whoever made the three tables I was presented with had helpfully failed to appreciate that when copying formulas down (unless I use the $ sign, of course), Excel changes the rows in the formula; whilst when copying formulas across, Excel changes the columns. This posed a challenge because tables 2-3 are organised in the opposite way to table 1.

So I'm looking for a simple way to reverse this tendency of Excel? I did try to search for solutions online at the time, but sadly I could find none which I (as a beginner user!) could understand clearly enough to actually apply to the task at hand!

This is a non-urgent issue, but any help would be much-appreciated.

Thanks for this - much-appreciated. Having read a bit more around the issue, I now understand these functions and they can solve a lot of my issues!

However, unfortunately it doesn't entirely address everything. You might be better able to understand the problem if you look at the attached spreadsheet, which is an updated version of the earlier one:

Data Table T Test - Updated.xlsx

As you can see, I've updated all the columns I can using your rule. The averages before and average after - both for all participants, participant type 1 and participant type 2 - have been filled in with the formula.

But I encounter a problem when I try to conduct a T-Test. This is because a T-test, to return an accurate value, needs to know how many participants you are working with for each value. This means when telling Excel to calculate the T-test you have to drag and select the exact data array you want.*

I'm thus working with several arrays of data and need to alter the INDEX / ROWS function so that it selects several arrays of data and works its way along all columns of the table. You had no reason to know this so perhaps you assumed I could just work off the same averages as before - to the best of my knowledge (someone correct me if I'm wrong), you can't really do this with an Excel T-Test.

So the question I'm asking is: how do you alter the INDEX / ROWS function so they select several arrays of data, rather than just a single data point? It's clear enough that I need to fill in the column_num section for the INDEX function rather than just row_num, but I still can't get my head around using the function to read multiple arrays of data (which may not be possible). My best guess is in the attached spreadsheet. I also need T-Tests not just for all participants, but Participant Type 1 and Type 2 as well - this means working the function around these issues as well.

Hope that makes sense.

* Granted, excel does also have a data analysis tool pak which allows you to do the T-test without using an Excel function, and you can type the number of participants and the averages into a text box. This is of course great when doing one T-test for a single study, but it's not helpful when - like with me - you're being asked to do a load of separate T-Tests.