transpose row formulas

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » Transpose a row with formulas

Transpose a row with formulas

Resolved · Medium Priority · Version 2016

Fay has attended:

Transpose a row with formulas

I have data in a column where each cell contains performance data (in %) for a specific month. There are formulas in the cells of this column.

I have to create a table in which the rows are years and the columns are months. I want to refer each cell of this table to a cell in the above column.

I can't copy and paste special transpose because I don't just want the values. I want to refer to a cell.

Is there a formula for this or a quick way to populate the table?

Thanks a lot!

RE: Transpose a row with formulas

Hi Fay,

There is a transpose function (here's the link to the page on it: https://support.office.com/en-gb/article/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027 ) but I'm not sure this will solve your problem.

NB: this is an array formula so you need to finish up with ctrl + shift + enter.
If you just do enter it won't work...

Let me know how you get on and if this solves the problem.

thanks
Claire

RE: Transpose a row with formulas

Hi Claire,

Thank you for coming back so quickly.

I've tried the transpose function but it doesn't do what I need it to do. I want to be able to create a formula in one cell and then drag it to the right with the black cross, with each incremental cell referring to one further down from the list. Not sure if it makes sense?

Many thanks,
Fay

RE: Transpose a row with formulas

You can use the Offset function to give a cell relative to another cell.
The rows position states how many rows from the start point.
The columns position states how many columns from the start point.
Positive numbers go down and right. Negative numbers go up and left.

So if you state the top of the list you can work down from there.
If the top of your list is in row 1 then this is easy.

=OFFSET(\$A\$1,ROW(\$A\$1)+1,0)

We're using the ROW function to get the row position of your start point and then adding one to it. As you drag the formula it will increment.

If it's not then we need to fudge a bit...

=OFFSET(\$A\$2,C1,0)
Where you've put the offset value in C1.

Here's the link to the help page: https://support.office.com/en-us/article/OFFSET-function-C8DE19AE-DD79-4B9B-A14E-B4D906D11B66

I hope that's not too mind blowing. Let me know if you have any questions.

Claire

Tue 10 Mar 2020: Automatically marked as resolved.

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

Using Excel's MODE function

Use Excel's MODE function to display the most common value present in a particular range of cells.

The Mode function looks like this:

=MODE(cell range)

As an example, if 35 is the most commonly recurring number in a particular cell range, then the function will display 35.

View all Excel hints and tips