Rows to Columns

I have data arranged in rows across a spread sheet, for example B2, C2, D2, 
etc.
How can I change that to a column, for example A1, A2, A3, etc without 
reentering all the data?

Thank you
0
Utf
11/17/2009 4:23:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
797 Views

Similar Articles

[PageSpeed] 55

--Select B2:D2. Copy the range
--Select A1. Right click>PasteSpecial>Transpose>OK

If this post helps click Yes
---------------
Jacob Skaria


"Walter Seaton" wrote:

> I have data arranged in rows across a spread sheet, for example B2, C2, D2, 
> etc.
> How can I change that to a column, for example A1, A2, A3, etc without 
> reentering all the data?
> 
> Thank you
0
Utf
11/17/2009 4:27:02 PM
There are two ways to do this.  Use Jacob's suggestion, or use this:
http://www.techonthenet.com/excel/formulas/transpose.php

That will create an array and 'lock' your rows/columns.  This offers more 
protection so that parts of arrays can't be accidentally deleted.

HTH,
Ryan---

-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jacob Skaria" wrote:

> --Select B2:D2. Copy the range
> --Select A1. Right click>PasteSpecial>Transpose>OK
> 
> If this post helps click Yes
> ---------------
> Jacob Skaria
> 
> 
> "Walter Seaton" wrote:
> 
> > I have data arranged in rows across a spread sheet, for example B2, C2, D2, 
> > etc.
> > How can I change that to a column, for example A1, A2, A3, etc without 
> > reentering all the data?
> > 
> > Thank you
0
Utf
11/17/2009 10:06:01 PM
On Nov 17, 6:23=A0pm, Walter Seaton
<WalterSea...@discussions.microsoft.com> wrote:
> I have data arranged in rows across a spread sheet, for example B2, C2, D=
2,
> etc.
> How can I change that to a column, for example A1, A2, A3, etc without
> reentering all the data?
>
> Thank you

You can use an array formula

=3Dtranspose(a1:A10)

Higlight the activecell and the additional 9  columns (in the example
being 10 rows)

Press f2 to edit the transpose formula and then ctrl + shift + enter
0
Jan
11/18/2009 1:13:10 AM
Reply:

Similar Artilces:

how to I block text not to break across columns or pages ?
I want to keep certain text together and not have it split apart over columns or on a new page: like several lines comprising an address in an address book -- I want to keep it all together. Either apply a style with the Keep With Next paragraph format option to all but the last of the paragraphs you wish to keep together or Use Shift+Enter to make a line break within the same paragraph -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MV...

update the same cell into another column forming a list on opening
hi there, i have made a spreadsheet to enter data and then view the resulting statistics that are generated, i have one cell that changes in value after entering the data, and i would like to keep a record of what values this cell has been, i was after a formula that would update that single cell's value into a column forming a list each time the spreadsheet was opened or the value in the cell changed, like a field perhaps, this would fill up the column with a list of results from which to generate more stats and a graph etc. any help would be great, thank you -- thank...

How do I add column A data labels to a scatter of columns B and C?
I have made a scatter plot using columns B and C as my x and y coordinates. I would like to use the data in column A to label my points. I already have the XY chart labeler 97 installed and selected, but I don't know how to get the data labels to show up on my graph. Thank you! Hi Christina - There are a couple of well written Excel add-ins you can download to do this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Both are free downloads, they install easily, and they integrate into Excel's interface very nicely. They l...

Pivot chart and Total row
Hi All I have a Pivot table and attached Pivot chart (all good so far). However, there are three columns (drop column details here) which are the two columns of data and a grand total column (showing the average of my two data columns) When I turn it into a pivot chart it does not display the grand total column as well (is there a way to do this). I have tried to go into the chart options but these are locked. I can copy and paste the results but this is a bit clumsy. Any suggestions - any help greatly appreciated Kind regards Rexmann PS using excel 2003 ...

Fixed Number of Columns
After manipulating data in Excel I would like to copy and paste the data in a text editor. The data should be in fixed format, for example integer numbers should end in columns with multiples of 5. The text is then used as input to an old fashioned program which requires that the data be aligned I changed the font in Excel to Courrier New and then selected a column width of 5. When I copy the data to a text editor it copies some tab marks and the columns do not align. How can I make things align in the text editor? Vino, You can use a macro: copy the code below, then paste into a module...

Locking a column. Excel, 2002.
I don't know if locking is the correct phraseology and perhaps that is why I can't find an answer. In column A are the numbers, 1, 2, 3, and so on and I want these numbers to remain stationary (they are the rank ordering) when I do a sort on another column (say H). -- Regards Ron Badour Select the range that you want to sort, but avoid column A. Then sort your data. If you let excel guess at the range, it'll think that you may want all the contiguous columns sorted. Personally, I would sort that column, too. But I'd fill it with a formula like: =row()-1 (headers...

How to copy rows from Excel without losing the row height format
In Excel 2003. I have a section of a spreadsheet that contains a 25 rows that each have different row height. I want to copy these to a section below, but lose the row height formatting. How do I copy without losing the row height format? Are you copying the entire row? Also, don't forget the format painter and the right-click paste special options. ************ Anne Troy www.OfficeArticles.com "artex boy" <artex boy@discussions.microsoft.com> wrote in message news:4DA6D00F-8957-4569-94C5-EDB2AE6F683D@microsoft.com... > In Excel 2003. I have a section of a sp...

How does one print the columns from a Visio ERD diagram?
I see them but can't print them. ...

Excel 2000... Missing row and column designators.
Ho do you mean 'Tools - Options - View' and check 'Row/Column headings' -- Regards Frank Kabel Frankfurt, Germany "Clarence" <carowe@cnyconnect.net> schrieb im Newsbeitrag news:2ee7401c46c21$a45aef40$a501280a@phx.gbl... > ...

Why are my columns and rows reversed when I open Excel
When I open my Excel spreadsheet the columns, rows and scroll bars are reversed?? What's happened? Tools|options|International Tab Uncheck that "view current sheet right-to-left" Deb Web wrote: > > When I open my Excel spreadsheet the columns, rows and scroll bars are > reversed?? What's happened? -- Dave Peterson ...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

Macro to find a row and delete it and the 9 rows above it. #2
Thanks alot that did the trick. This forum and you guys are life saver -- Rusty_n ----------------------------------------------------------------------- Rusty_nl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=671 View this thread: http://www.excelforum.com/showthread.php?threadid=31376 ...

Is there any way to use the MATCH function with more than 1 column
For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 215, but my data set spans more than one column. My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) returns an #N/A error. What can I do? Do you want both the row and column ? Two separate values? -- Gary's Student "Mr. Snrub" wrote: > For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value > 215, but my data set spans more than one column. > > My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) > returns an #...

Columns in Money 2004
Is there any way to change the default columns in the register view ? I really dont want the flagged column, and would like to include a column for categories. Is it possible to do this ? In microsoft.public.money, VerticalCobra wrote: >Is there any way to change the default columns in the register view ? I >really dont want the flagged column, and would like to include a column for >categories. Is it possible to do this ? No. Try Ctrl+T to toggle to multi-line display and back. ...

Retrieving the Top 10 of the Average of Two columns, but displaying a third
I'm new to Excel programing, so please bear with me. I have an excel spreadsheet with 3 columns. Column1 Column2 Column3 -------------------------------------- John Doe 5 3 Jane Smith 2 9 Bill Smith 4 2 What I need to do is take the average of Column 2 and 3, find the highest average of the entire list and display Column 1 as the final result. In other words: I need to create a Top 5 List of the people with the highest grade average. I don't care much for displaying the grade, I just need the top 5...

how do i "de-dupe?" rows by a column value?
I have a complex operation to perform. My spreadsheet has rows that indicate items (control number in column A) linked to titles (control number in column B). A title can have multiple items in the spredsheet. I want to number the titles and assign random numbers to them so that I can get two random samples. I also want to have my random numbering retained in the spreadsheet as a whole, so I can see all the items assigned to my samples. How can I "dedupe" column B, assign a random number to each title control, then transfer that data to my "undeduped" spreadshe...

Can I change line numer or column name
I'd like to change my line numbers to specific salespeople and column names to specific months. Does anyone know if thats possible and how to do it? Thanks Dave, you can't change the ones in excel but you can put your own in row 1 and column A, then you can hide the row and column headers if you want, tools options, view, uncheck row and column headers -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Dave Bonneville&q...

Converting rows to columns on a large scale
I have a workbook of names and addresses. All information is in column A... John Doe 111 5th st. New York, NY Joe Smith 1st St. E Chicago, IL etc. I want to convert this entire workbook to column form as follows, John Doe 111 5th St New York, NY Joe Smith 1st St. E Chicago, IL Is there a way for me to do this without using "Paste Special" and "Transpose" for each individual entry.....or tediously moving each entry one by one? bootsy ...

Drag the column
Let say I have the data and I want to fill in the data in another sets of table. All i need to do is to put"=" and it will copy. What if I want to drag the copy by changing the colum for example I will drag from A1 and rather than it will change to A2, I want to change it to B1,C1 and excetra. I've tried A$1 and $A1 but failed. Please help me (*^*) One way In the start cell, say in B3: =INDEX($1:$1,ROWS($1:1)) Copy down to return A1, B1, C1, etc Success? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemecha...

Summing column that includes #Error values
I have asked this before but it's come back to haunt me. I have a calculated value column in a query that contains some #Error values. This generates an error when attempting to run a query which is a sum on that column. How can I get around this - ie. make it ignore the #Error values when summing stuff? What is causing the errors? MH "will" <will@stream3.co.uk> wrote in message news:ekig4a5nHHA.3968@TK2MSFTNGP06.phx.gbl... >I have asked this before but it's come back to haunt me. I have a >calculated value column in a query that contains some #Err...

Lookup one column and sum another column
Hi all,, Hope someone can help me with the following problem. I would like to look up Column C for a specific value (i.e. 19) and then sum the values in Column A that relates directly to the value of 19 in Column C. Thanks in advance. If you mean SUM() values in Column A that are in the same rwo as number 19's in Column C then try: =SUMIF(C1:C9,19,A1:A9) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk "Maddoktor" <maddoktor@clinic.com> wrote in message ...

re:to show just a few columns
Hi there everyone I would like to know if it is possible to show just a few columns in an advancedfilter. For example, I have a worksheet of 10 columns which I hope to show just 3 of them after advancedfilter. Is it possible, how to ? Thank you for your assistance Regards lalaexcel -- lalaexcel ------------------------------------------------------------------------ lalaexcel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30585 View this thread: http://www.excelforum.com/showthread.php?threadid=515630 Filter and hide the columns you want - then on the...

Hiding rows with VBA
Hi all, In my sheet called "insertsheet I have this VBA: (thanks to some users of Google groups) Private Sub Worksheet_Change(ByVal Target As Range) Const nMAX As Long = 10 Dim nRows As Long Application.ScreenUpdating = False With Me With .Range("B8") If Intersect(Target(1), .Cells) Is Nothing Then Exit Sub nRows = .Value End With .Range(.Cells(1, 5), .Cells(1, _ .Columns.Count)).EntireColumn.Hidden = True .Range(Cells(1, 5), .Cells(1, _ ...

Balance downloaded not correct in left column.
I have no problem downloading any of my account info or transactions. The only thing that doesn't seem to change or update is the downloaded info in the left column. It still reads 10/13/2003 with my balance from that date. Is this a glitch or is it lack of information downloaded from the bank? In microsoft.public.money, dh1605 wrote: >I have no problem downloading any of my account info or transactions. The >only thing that doesn't seem to change or update is the downloaded info in >the left column. It still reads 10/13/2003 with my balance from that date. >I...

Column width after Pivot Table changes
Hi, In Excel 2002 (and all previous versions :|) whenever you move fields around in Pivot Table view if you happen to have one field containing long text strings the column widens out to fit its contents. This is not fun because I have to manually reset the column width so I can see all the columns. Why does the Pivot Table do this and is there a way to keep column widths fixed? Thank you, Steve. Steve, I have the same problem. If my data spreadsheet column widths are set at a 4.75 as an example, I want the pivot table columns to remain the same. I have been trying everything for resolu...