ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to vertically transpose (transpose vertical) all data columns in Excel

Updated on July 8, 2012

When working in Excel, we sometimes need to transpose or “flip” all columns of data. Here is a neat trick to accomplish that. Refer to diagram 1 first. We want to flip all the names and birthdates. Next to the birthday column, type in “1” and “2” as shown in diagram 2. Select, or box in, both numbers with your mouse. Then drag the corner down, stopping at the cell next to the final birthday. Consecutive numbers will fill in. Moving on to diagram 3, select the cell above “1”. Within the “Data” tab, sort in descending order. Diagram 4 shows the result. All the columns have been vertically transposed or “flipped”. Column E can now be deleted.

If desired, we can also make this flipping function automated. This by using an embedded program within the Excel workbook. It will work for any data table conforming to the following requirements:

1. Top left cell of your data table must be placed (pasted) into cell “A1” of workbook “Sheet1”.

2. The table must be rectangular. For example, if there are 8 rows and 7 columns, there must be 56 populated cells.

3. Row 1 must contain headers (i.e. column titles)

We will go through how to install the program here. It will involve copying and pasting my code into Excel. Open up Excel, save the new file as “flip.xlsm”. In newer versions of Windows, you will save as type “Excel Macro-Enabled Workbook”. The workbook/spreadsheet should contain three sheets: “Sheet1”, “Sheet2”, and ‘Sheet3”. Right click on the “Sheet3” tab at bottom, and delete it. Do the same for “Sheet2”. “Sheet1” remains. This is where you paste in your table of data. Our program will look for the name “Sheet1”, so do not change it. You will need access to the Developers tab in Excel. To make it visible in the new spreadsheet, do as follows:

1. File --> Options (click)

2. Customize Ribbon (click)

3. Check the “Developer” box and click “OK” at bottom.

4. You will now see “Developer” tab. Click it.

5. Section dealing with macros and VBA (Visual Basic) is now visible at far left.

We will create a macro, into which we will soon paste some code. Over on the left, find and click the tab “Record Macro”. A box pops up. It has been given a default name, such as “Macro1”. Just leave it as is. There will also be a shortcut key entry, make it “ctrl a”. Click OK. At this point, do not press any keys or perform random mouse clicks, or you will actually start recording keystrokes/commands in the macro. Rather, immediately click the “stoprecording” tab, located in same place where “Record Macro” was. We have created a blank routine into which we will now place some code. Click on “Macros” tab and you will see the blank macro we just created. Click “Edit”. The macro will open up in a window, see picture in diagram 5. Place the blinking cursor as shown. In order to do so, you will have to down arrow key until the cursor sits next to "End Sub". Hit Enter key 5 times to move "End Sub" down. Up arrow key 3 times to put the cursor between "End Sub" and lines above it. Now copy and paste in this code:

Dim rightcorner As String, bottomcorner As String, count As Single, sortcelltop As String, rangestr As String

Range("A1").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Columns.AutoFit

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Range("A1").Select

Selection.End(xlToRight).Select

rightcorner = ActiveCell.Address

Selection.End(xlDown).Select

ActiveCell.Offset(0, 1).Select

bottomcorner = ActiveCell.Address

Range(rightcorner).Select

ActiveCell.Offset(1, 1).Select

rightcorner = ActiveCell.Address

ActiveCell.Value = "1"

count = 2

Do Until ActiveCell.Address = bottomcorner

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = count

count = count + 1

Loop

count = count - 1

count = -count

bottomcorner = ActiveCell.Address

ActiveCell.Offset(count + 1, 0).Select

sortcelltop = ActiveCell.Address

rangestr = "A2:" & bottomcorner

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(sortcelltop), _

SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort

.SetRange Range(rangestr)

.Header = xlGuess

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

ActiveCell.Offset(1, 0).Select

Selection.Copy

ActiveCell.Offset(-1, 0).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Selection.EntireColumn.Delete

Range("A1").Select

Close the program box and re-save the Excel workbook, keeping it open. Your data table can/should be copied/pasted into “Sheet 1”, paste at cell “A1”. Hit “ctrl a” and the program will run. That’s it, hope it works well for you.

Comments

    0 of 8192 characters used
    Post Comment

    • drpastorcarlotta profile image

      Pastor Dr. Carlotta Boles 5 years ago from BREAKOUT MINISTRIES, INC. KC

      Very, very informational! Thank you! Voted-Up!!

    Click to Rate This Article