How to Copy Paste Vertical to Horizontal in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a powerful tool, and it helps users to perform different tasks easily. Users create datasets to represent data. The datasets consist of rows and columns. Sometimes, users may need to copy vertical columns to paste them into horizontal ones in Excel. Today, we will demonstrate two easy and quick methods. Using these methods, you can copy vertical data and paste them to horizontal ones in Excel effortlessly. So, without further delay, let’s start the discussion.


How to Copy Paste Vertical to Horizontal in Excel: 2 Methods

For clarification, we’re going to use a daily Sales Report of a particular fast food shop. Columns B, C, and D of this dataset contain the names of Salesmen, Items, and their corresponding Qty.

copy paste vertical to horizontal excel

Now, we’ll use this dataset to copy vertical data and paste it in the horizontal direction in Excel.
Here, we have used the Microsoft Excel 365 version. You may use any other version according to your convenience.


1. Using Paste Special Option

The easiest way to copy a vertical column and paste it to a horizontal row is to use Paste Special command in Excel. It also keeps the exact formatting while changing the vertical column. So, you don’t need to apply any formatting later. Let’s follow the steps below to see how we can use the Paste Special option to do the task.

📌 Steps:

  • At the very beginning, select cells in the B4:D10 range.
  • After that, press the CTRL button followed by the C button on the keyboard. It copies the selected cells in the range.

Using Paste Special Option

  • Then, right-click on cell B12 where we want to paste the copied data.
  • Here, we can see a context menu. Next, select Paste Special on it.

Immediately, the Paste Special dialog box opens.

  • At this time, make sure to check the box of Transpose at the bottom of the box.
  • Lastly, click OK.

Working on the Paste Special dialog box to copy paste vertical to horizontal in Excel

Finally, you can see the data in horizontal rows in the B12:H14 range.

Using Paste Special Option to copy paste vertical to horizontal excel

Read More: How to Copy Horizontal and Paste Vertical in Excel


2. Applying VBA Code

Have you ever thought of automating the same boring and repetitive steps in Excel? Think no more, because VBA has you covered. In fact, you can automate the prior method entirely with the help of VBA. So, let’s follow along.

📌 Steps:

  • First of all, right-click on the sheet name VBA.

Instantly, it opens a context menu.

  • From the menu, select the View Code command.

Applying VBA Code

Immediately, the Microsoft Visual Basic for Applications window appears. Also, we can see a code module inserted for Sheet3 (VBA) on the right side of the display.

Presently, copy the following code and then paste it into the module.

Public Sub Copy_Paste_Vertical_to_Horizontal()
Range("B4:D10").Copy
Range("B12").PasteSpecial Transpose:=True
End Sub

VBA Code to copy paste vertical to horizontal in excel

Here, we create a sub-procedure with the name Copy_Paste_Verticla_to_Horizontal first. Then, we copied all the cells in the B4:B10. Then, we pasted them in cell B12 which is the starting cell of our pasted data. Also, we gave the status of Transpose as True. Then end the sub-procedure.

  • Now, Run the code using the green-color play button.

Executing the Code

  • After that, return to the VBA worksheet and see the data arranged horizontally.

Applying VBA Code to copy paste vertical to horizontal in excel


How to Change Vertical Text to Horizontal in Excel

Here, we have another dataset in our hands. It’s the report on Monthly Sales of ABC Company. This dataset concludes the names of the Sales Person, the names of the Product, and their consecutive sales amount for the months of January to May of the year 2022.

How to Change Vertical Text to Horizontal in Excel

Here, all the texts in the headings are in vertical alignment. Now, we’ll change these vertical texts to horizontal ones in Excel. It’s simple and easy; just follow along.

📌 Steps:

  • At first, select the cells containing the headings in the B4:H4 range.
  • Then, press CTRL + 1 on the keyboard.

Selecting Cells and opening Format Cells dialog box

Suddenly, the Format Cells dialog box appears before us.

  • Firstly, go to the Alignment tab.
  • Secondly, write down 0 in the box of Degrees.
  • Lastly, click OK.

Making 0 degree Alignment

Magically, all the vertical texts get changed to horizontal ones in the blink of an eye.

Changing Vertical Text to Horizontal in Excel


Using Keyboard Shortcut to Paste Horizontal in Excel

Wouldn’t it be great if there was only a keyboard shortcut to copy vertical data and paste it in the horizontal direction in Excel? Well, you’re in luck because this exists. So, without further delay, let’s dive in!

📌 Steps:

  • Initially, select any cell inside the range. In this case, we selected cell B4.
  • Then, press the CTRL and A buttons simultaneously on the keyboard.

Using Shortcut to Paste Horizontal in Excel

Actually, the prior shortcut selects the whole range (here, it’s B4:D10).

  • After that, press CTRL + C on your keyboard.

This command copies the entire selection.

Next, go to cell B12 where we want to paste the data.

  • Currently, press the CTRL + ALT + V keys altogether.

Pasting Data

Immediately, the Paste Special dialog box pops up.

  • Here, press the E key on the keyboard.
  • Then, click OK or press ENTER.

And, you can see the data in horizontal rows.

Using Shortcut to Paste Vertical to Horizontal in Excel


Practice Section

For doing practice by yourself we have provided a practice section like the one below in each sheet on the right side. Please do it by yourself.

Practice section


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

This article explains how to copy and paste from vertical to horizontal in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo