# Excel: autofill a column with values from row
**URL:** https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800
**Category:** Factual Questions
**Created:** [August 23, 2011, 5:32pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800 "2011-08-23T17:32:37Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@DCnDC](https://boards.straightdope.com/u/DCnDC)
#### Post date: [August 23, 2011, 5:32pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/1 "2011-08-23T17:32:37Z")
I’m using Excel 2008. I’ve got an extremely basic formula, nothing complicated, just filling in info verbatim from a different sheet. I need to autofill a _column_ on Sheet A with this formula, but the info is coming from a _row_ on Sheet B.
What I need for it to fill in is:
=SheetB!A3
=SheetB!B3
=SheetB!C3
=SheetB!D3
…
but what Autofill thinks I am trying to fill in is:
=SheetB!A3
=SheetB!A4
=SheetB!A5
=SheetB!A6
…
I’m certain there must be a way to accomplish this seemingly simple task but I’m not familiar enough with all of the functions to figure out a formula to make it do this.
---
### Author:  [@Reply](https://boards.straightdope.com/u/Reply)
#### Post date: [August 23, 2011, 6:32pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/2 "2011-08-23T18:32:42Z")
Can you [transpose the row into a column first](https://office.microsoft.com/en-us/excel-help/rotate-data-by-converting-columns-to-rows-or-vice-versa-HP005203138.aspx) or does it have to stay a row?
---
### Author:  [@yoyodyne](https://boards.straightdope.com/u/yoyodyne)
#### Post date: [August 23, 2011, 6:34pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/3 "2011-08-23T18:34:34Z")
If it’s an infrequent thing, you can copy and paste special, clicking the [transpose box](http://office.microsoft.com/en-us/excel-help/rotate-data-by-converting-columns-to-rows-or-vice-versa-HP005203138.aspx).
If you want to use a function, it’s array [transpose](http://www.youtube.com/watch?v=KRSAh-WekUE).
---
### Author:  [@DCnDC](https://boards.straightdope.com/u/DCnDC)
#### Post date: [August 23, 2011, 6:46pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/4 "2011-08-23T18:46:05Z")
I’d prefer it remain a row. It’s arranged that way for a reason. I suppose I could add an extra column, transpose into that and then hide it, but the same problem would arise when the values change, which they will.
If it were just a dozen or so cells I would just manually enter it but I’ve got several hundred.
---
### Author:  [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)
#### Post date: [August 23, 2011, 6:55pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/5 "2011-08-23T18:55:42Z")
So can you transpose and hide on Sheet A and Link to that on Sheet B?
---
### Author:  [@DCnDC](https://boards.straightdope.com/u/DCnDC)
#### Post date: [August 23, 2011, 7:02pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/6 "2011-08-23T19:02:09Z")
No. The source values from Sheet B are going to change, which would necessitate copying, transposing and hiding every time a change is made which will ultimately be more work than just manually entering all of the cell addresses.
---
### Author:  [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)
#### Post date: [August 23, 2011, 7:35pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/7 "2011-08-23T19:35:29Z")
I’ve never used transpose before, but when I just played with it when I changed the original cell it changed the transposed cell as well.
Obviously it’s a bit ugly having it double linked there, but it does seem like the data will be current.
---
### Author:  [@DCnDC](https://boards.straightdope.com/u/DCnDC)
#### Post date: [August 23, 2011, 8:04pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/8 "2011-08-23T20:04:46Z")
It’s not working that way for me.
---
### Author:  [@jjimm](https://boards.straightdope.com/u/jjimm)
#### Post date: [August 23, 2011, 8:06pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/9 "2011-08-23T20:06:39Z")
Use a pivot table.
---
### Author:  [@obfusciatrist](https://boards.straightdope.com/u/obfusciatrist)
#### Post date: [August 23, 2011, 8:24pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/10 "2011-08-23T20:24:59Z")
Will you regularly add new rows to the table? If so, only at the bottom or will they be added to the middle?
---
### Author:  [@DCnDC](https://boards.straightdope.com/u/DCnDC)
#### Post date: [August 23, 2011, 8:28pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/11 "2011-08-23T20:28:36Z")
Not likely. The table is set up exactly how I need it to be, but the data on the table will be changing regularly.
---
### Author:  [@jjimm](https://boards.straightdope.com/u/jjimm)
#### Post date: [August 23, 2011, 8:30pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/12 "2011-08-23T20:30:22Z")
A pivot table can take data from rows and convert to columns, and vice versa. Right-click the table to update the values. Very powerful and easy to use.
---
### Author:  [@hibernicus](https://boards.straightdope.com/u/hibernicus)
#### Post date: [August 23, 2011, 8:58pm UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/13 "2011-08-23T20:58:00Z")
The {TRANSPOSE()} array function does exactly what you want.
The one trick you have to know is, when you enter the formula and select the target cells, instead of hitting “Enter” you have to hit “Ctrl-Shift-Enter”.
---
### Author:  [@Zany\_Zeolite\_Zipper](https://boards.straightdope.com/u/Zany_Zeolite_Zipper)
#### Post date: [August 24, 2011, 12:49am UTC](https://boards.straightdope.com/t/excel-autofill-a-column-with-values-from-row/593800/14 "2011-08-24T00:49:44Z")
If for some reason you don’t want to use one of the transpose functions or a pivot table, you could use what I’ve posted below. However, pivot tables are the best thing in the world once you know how to use them. I strongly recommend you use a pivot table.
You could try using the offset command like so:
=offset(x,y,z)
Where x is an absolute reference to the first cell in the range.
y = 0
z = row(a)- b
a = a relative reference to the cell in which you are writing the formula
b = the number of row a.
If you read the manual for offset it will make more sense.