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.
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.
- 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.
Finally, you can see the data in horizontal rows in the B12:H14 range.
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.
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
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.
- After that, return to the VBA worksheet and see the data arranged horizontally.
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.
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.
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.
Magically, all the vertical texts get changed to horizontal ones in the blink of an eye.
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.
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.
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.
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.
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
- How to Copy and Paste a Column in Excel
- How to Copy Merged and Filtered Cells in Excel
- How to Copy and Paste in Excel with Merged Cells
- How to Copy and Paste in Excel When Filter Is On
- How to Disable Copy and Paste in Excel without Macros
- How to Fill Across Worksheets in Excel
- If Value Exists in Column Then Copy Another Cell in Excel
- How to Copy Cell If Condition Is Met in Excel
