Subscribe for Free Excel tips & more!
E-mail:

# Convert Data Horizontal To Vertical

i have a data in columns and would like to convert into rows

e.g.
11 A B C D E F G H
22 1 2 3 4 5 6 7 8
33 2 3 4 5 6 7 8 9
44 3 4 5 6 7 8 9 0

and result should be
A 22 1
A 33 2
A 44 3
B 22 2
B 33 3
B 44 4
C 22 3
C 33 4
C 44 5
and so on ..

plz help

## Similar Excel Tutorials

Change Axis Units on Charts in Excel
You can change the size of the units on a chart axis, their interval, where they start, where they finish, and mor ...
Insert and Manage Page Breaks in Excel
How to insert, remove, and manage page breaks in Excel.  This can be rather annoying and confusing but this tutori ...
Changing Any Element in a Chart in Excel
In this tutorial I am going to go through the Layout tab in more detail and show you how to build up chart layouts ...
How to Arrange Data within Cells in Excel
In this tutorial I am going to look at cell alignment / arrangement. These features allow you to change how data lo ...

## Helpful Excel Macros

Convert Numeric Dollar Values into Text in Excel - UDF
- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt

## Similar Topics

I have data laid out horizontally that needs to be vertical re-using most of the fields, however when I copy and paste the formulas, excel jumps forward the number of cells in the sequence (16), I need to continue out this pattern for 600+ instances

='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!M2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!N2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!O2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!P2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!Q2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!R2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!S2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!T2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!U2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!V2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!W2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!X2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!Y2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!Z2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!AA2
='Horizontal Data'!A2 ='Horizontal Data'!B2 ='Horizontal Data'!C2 ='Horizontal Data'!D2 ='Horizontal Data'!E2 ='Horizontal Data'!L2 ='Horizontal Data'!AB2

='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!M3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!N3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!O3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!P3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!Q3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!R3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!S3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!T3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!U3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!V3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!W3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!X3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!Y3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!Z3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!AA3
='Horizontal Data'!A3 ='Horizontal Data'!B3 ='Horizontal Data'!C3 ='Horizontal Data'!D3 ='Horizontal Data'!E3 ='Horizontal Data'!L3 ='Horizontal Data'!AB3

the only variation is the last column of cells it references the horizontal data, the rest of the data is repeated 16 times. Anything short of a find and replace after a copy paste would make my life easier.

Hi,

I have data in a horizontal format that I would like to convert into a vertical format.

The following is an extract of the data -:

Postcode Trade 01-Apr 02-Apr 03-Apr 04-Apr 05-Apr
AB A 1 1 1 1 2
AB B 1 1 1 0 1
AB C 1 1 1 1 2
AB D 3 3 2 2 4
AB E 0 0 0 0 1
AB F 1 1 0 0 1
AB G 0 0 0 0
AL A 3 3 2 2 4
AL B 1 1 1 1 2
AL C 1 1 1 1 2
AL D 3 3 2 2 4
AL E 6 6 5 4 8
AL F 1 1 1 0 1
AL G 1 1 1 1 2
AL H 1 1 0 0 1

The columns extend right out to 31-Mar i.e one for each day of the year.
----------------------------------------------------------------------------------------------------------------------------------

I have been asked to present the data, vertically, as follows -:

Postcode Date Trade Amount
AB 01-Apr-10 A 1
AB 02-Apr-10 B 1
AB 03-Apr-10 C 1
AB 04-Apr-10 D 1
AB 05-Apr-10 E 2

i.e. each day of the year has to be listed against each category in the Trade column. This makes a total of over 300k rows. I have went through the painful process of copying and pasting (transposing) and I have done all the postcodes and dates.

Would anybody know any way of formulating the amount across from horizontal to vertical? I have looked into a few different formulas to no avail.

Thanks

Hi guys,

I was wondering if there is a way to convert a tri-matrix (output of genetics software "MICROSAT") into a single column in excel. The resulting column needs to have both the vertical id (A-G) and the horizontal id (A-G) for each cell (0-6). Examples follow:

Tri-matrix
id A B C D E F G
A 0
B 1 0
C 1 2 0
D 1 2 3 0
E 1 2 3 4 0
F 1 2 3 4 5 0
G 1 2 3 4 5 6 0

Column
vertical id, horizontal id, value.
A, A, 0.
B, A, 1.
C, A, 2.
D, A, 3.
E, A, 4.
F, A, 5.
G, A, 6.
B, B, 0.
C, B, 1.
D, B, 2.
E, B, 3.
F, B, 4.
G, B, 5.
C, C, 0.
D, C, 1.
E, C, 2.
F, C, 3.
G, C, 4.

ect.

The tri-matrix I am trying to convert has about 20,000 records.

Any help would be much appreciated.

Cheers,

Dave

Is this possible ro do I need to convert to vertical? If so how do I convert to vertical? Would prefer horizontal autofiltering if possible though'

thanks

If I have a list of emails that are separated by a comma, and I cut an paste them into a spreadsheet, and then do a text to columns function, all of the emails get separated nicely into separate cells. Is it possible to then take those horizontal cells and convert them to vertical cells, so that I could sort them? I.E. Take info in Cells A2 to Z2 and put it into cells A2 to A27.

Hi,

I need to create a multipage with tabs running down the left hand side. I cannot use the normal horizontal multipage control because this would result in an impossibly wide page. Is there a vertical multipage control? I could not find one? Or a way to convert the multipage control to show vertical tabs?

Please bare in mind that this vertical multipage will be shown in a form that already includes a normal horizontal multipage (I am using multipages within multipages too!).

Many thanks for your time and advice.

Dom

I am trying to convert a column of numbers into hours:minutes:seconds.

For example, I have following numbers (original data followed by desired output). I tried using the custom number format but could not get the desired result. Any assistance appreciated.

9 - convert to :09
53 - convert to :53
68 - convert to 1:08
109 - convert to 1:49
121 - convert to 2:01
262 - convert to 4:22

How can I convert a table of veerticle data to horizontal. Assume I have a list from a1:a10 and I want it go horizontabl from b1:k1

Thanks

Hi,

A study was conducted and data was collected on the same measures at different time periods (dates). It is recorded in the vertical column (sample attached). I want to get the data in the row.

What I don't know is, how to convert it in a way that each date takes a different position eg. first date should come under date 1, second under date 2 and so on and all the corresponding data comes against those dates.

Please find attached, sample of data and horizontal row is created to show the format which is required (yellow highlight).

Thank you for the help.

I have a row that is 400 columns wide with values sporadically thrown around in it. Majority of the values are blanks. Is there a way, without using visual basic, to put these nicely into a single column without blanks, using formulas only? There will never be more than 50 non-blank values, thus no more than 50 cells with formulas. I attached a sample file so it's a bit easier to understand what I am after. Thank you!
Horizontal to Vertical Example.xlsx

i have formulas in rows like:
a1 b1 c1 d1 etc to IF1
=TDY!h1 =TDY!h2=TDY!h3 =TDY!h4 ..... =TDY!h239

and i want to convert them to vertical, but when i do, they do not paste in the save formula. i do not want the formula to change, only the 239 cells to go from horizontal to vertical

When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.

Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?

Anyone have any ideas on how to make this happen?

I have a problem that I'm not sure transpose can fix.

I have a database aligned horizontally. It covers 25 items, and then 40 sub-categories of each item. So, every 40 columns, everything starts over with data for the next item. So columns 1-40 cover item 1, columns 41-80 cover item 2, ... through all the items. I want to change it to a vertical format where the subcategories lineup. So if there are 10 rows that track the sub-categories over time, I'd like the data for ITEM 2 in rows 1-10 of columns 41-80 to be placed in rows 11-20 of columns 1-40 (under ITEM 1 in rows 1-10 of columns 1-40), and ITEM 3 data in rows 1-10 of columns 81-120 to go into rows 21-30 of columns 1-40, etc. through all 25 items.

1 item = 40 columns and 10 rows.
Goal is to get it to where instead of them being laid out side-by-side horizontally, they are rather one-on-top-of-the-other vertically.

Rows 1-10 are dates, and the 40 columns are data points I'm looking at for each item. The horizontal layout gives me data by date. I need to have the data organized vertically by the data points (the 40 columns) to run regression analysis in another program.

I'd like not to do it through grunt work and I think it would be helpful to learn how to do this. Any ideas?

Hi

I am new to this forum and new to Macro

I am attaching a file where horizontal row should be converted into Verticle Row

Can you pls help me with it

thanks a lot in advance

Hello Friends,

I am in trouble of converting Vertical Columns to Horizontal Rows. I used Transpose function but I have too many data to perform the Transpose function.

Is there any other formula to do it in simple manner, which can work on large data smoothly??

My data file is attached here with for your reference.

I have data that goes into excel in vertical rows but I need it to go horizontal.

Is there a way to do this without manual changing?

Thanks

Jesse Jutkowitz

I need a solution for the equivalent of a SUMIF combining both vertical and horizontal data. The vertical cells align to the horizontal ones, but they're in a different table.

My attempted formula is: =SUMIF(\$H\$22:\$H\$30,"TRUE",D7:L7)
*note that this is just an example set of data...my real data set is much larger (both rows and columns)

I need to be able to do this without transposing any of my data.

Things I've tried:
- Another option I tried was making D7:L7 a named range and using the transpose function (as an array) within the SUMIF formula above. I received an error.
- I tried using a bunch of IF statements added together (i.e. =IF(H22=TRUE,D7,0)+(H23=TRUE,E7,0)...); this actually works properly, but I get the "formula too long for cell" error when I put them all in (too many characters)

Can anyone help with a solution?

I'm using excel 2003 and windows XP professional.

Thank you!
Lindsay

Sample.xlsx

Hi everybody,

I ran into the following problem. The table with all my raw data is vertical (It's a survey, so this has to stay vertical), the Vlookup table is vertical (I could make this horizontal if it would help), but I want the results to come out horizontal so I can use it for the pivot table.

Now I have to transpose my raw data to a horizontal table before I can use it, because when I drag the formula horizontally, it does not go down vertically in the raw data sheet.

Is there any way to solve this? It is quite a bore to transpore my raw data sheets. I tried using Hlookup, but my raw data has to stay vertical, so it didn't help

Thanks,
Raoel

I have been stuck on this problem and hope someone here can help out. I have a data set that is arranged horizontally in a table and I need it in just a vertical list. All of the searches I have done point to the transpose option under paste, but that doesn't work for this problem. The attached file shows an example of how I would like to have the data arranged. Thanks for the help!

Hi,

I have two spreadsheets and on say Spreadsheet 1 I have two rows (rows 1 & 2) from column A to AE.

On Spreadsheet 2 I want a formula that will copy in column A all the data that is in Spreadsheet 1 from A1 to AE2 and then in Column B do the same for A2 to AE2. So basically converting the horizontal presentation of the data into a vertical column.

I know how to drag formulas down but unsure on the horizontal to vertical dragging.

Any help would be great, thanks.

(Excel 2002)

Lew

Hi Guys,

I have few data which consists of Vertical and horizontal headings with values. These vertical and horizontal headings may vary its position but I need the correct value from the main sheet.

State Not So Good General Good
Mumbai 51 36 74
Dehi 33 27 46
Kerala 35 24 73
AP 34 27 46
Karnataka 56 24 78
Chennai 23 26 54
Kolkata 48 36 76
Gujarat 64 42 43

The above details will be the main sheet which may vary the horizontal and vertical headings.
IN the next sheet I will have some fixed headings which should pull the data from main.

Any help would be appreciated.

Hi Guys,

I need some help. I have a few spreadsheets that I need to convert horizontal table into verticals.
I have tried to use the TRANSPOSE function and it doesn't work for me.
I have attached a test spreadsheet with an intended result tab that will show what I am trying to explain.

Hopefully some of you will have an idea for a macro that will help.

I am trying to figure out if it is possible to have the horizontal borders in a worksheet behind the vertical borders. For example, I have white horizontal borders and black vertical borders. The default is that the white horizontal borders cross-over the black vertical borders. It looks like the vertical border is dashed, as it is interrupted by each horizontal border.

Is it possible to have the black vertical borders cross over the white horizontal borders, in-essence, sending horizontal borders "to back"?

I am using Excel 2007.

How do I convert data in a column into multiple columns with the delimiter being a vertical bar between the data?

This is a sample of the data

|Yes;Unit|x Story|Yes;Location|

I want the macro to select all data in columns D:O and convert it to a number.

Some of the values in the data will be in the format ="5" and some will be =""

So the macro needs to first select the data in the rows (number of rows may vary between macro uses)
Then the macro needs to convert the values from formulas to number values
Then the macro needs to fill in the null values with 0.

I have been searching for a way to select the data based on how many rows there are. When i recorded the macro, it was only for the specific rows 2:200, but there could be thousands of rows on future runs. The macro needs to take this into account. I am thinking i could just select all the columns i need:
Columns("D:O").Select
but then i need the code to convert the formulas to numbers and then insert 0 if the value is null.
I know this is easy but the syntax is killing me. any help would be greatly appreciated!
Nate