The first step in making your data easier to understand is typically to import all of your spreadsheet data into Excel. However, once that information is in your worksheet, organization can be just as crucial as having the information alone. Unfortunately, this can result in situations where your data is improperly organized or where data is accidentally entered into a row when it should be in a column. Especially if you are working with a lot of data, this can be frustrating. Thanks to a feature in Excel 2013, you can copy a horizontal row of data and then paste it into a vertical column. This can save a ton of time by avoiding tedious data re-entry.
How to paste horizontal data vertically in Excel
To paste horizontal data vertically in Excel, follow these four steps:
1. Open Excel and copy vertical rows
Open the workbook containing your dataset in Excel by launching the program. Click and drag your mouse over the group of cells to select the ones you want to transpose. Copy the dataset once you’ve selected it by pressing “Ctrl” and “C” on your keyboard. Additionally, you can right-click on the cells and choose “Copy” from the menu
2. Select where youd like to paste the data
Next, select where youd like to paste the data vertically. Choose a location on your workbook thats empty. Highlight the precise number of cells in your dataset using your mouse. As an illustration, if there are two columns with five cells each, you would highlight two rows with five cells each. Excel can correctly transpose the data when the appropriate number of cells are chosen.
3. Right-click and find “Paste Special”
Transpose the information after you’ve highlighted the new space for your cells. Right-click the area and select “Paste Special” from the drop-down menu to accomplish this. In the menu, “Paste Special” is listed after “Paste Options.” The “Paste” icon, which is a piece of paper on top of a clipboard, is also immediately beneath this button.
4. Choose “Transpose” and click “OK”
Once you click “Paste Special,” a new window appears. There are several options available in the “Paste Special” window for how to paste your data. The “Transpose” check box can be found close to the window’s bottom. To confirm that you want to transpose the copied cells, click the box. Press the “OK” button to apply your changes. Now, your data should appear vertically in your Excel sheet.
When to paste horizontal data vertically in Excel?
If you use Excel to store and organize data, you might find that you need to change the way your cells are laid out. To display the data more effectively, you might choose to organize it vertically rather than horizontally, for instance. It might take a while to manually retype the data in your workbook if there is a lot of it. You can transpose your cells using a variety of Excel’s methods to prevent this from happening. This can help you reenter the data accurately and save you some time.
How to paste horizontal data vertically in Excel using the transpose function
Following are three steps for pasting data vertically in Excel using the transpose function:
1. Highlight a range of cells
Open Excel and choose the worksheet containing your data. Select a vertical group of cells with the same amount of data as the data you want to transpose in a workbook’s blank space. As a result, Excel can transpose the data while maintaining the appropriate formatting. For instance, if you want to paste 10 cells into a single column, you should first select those 10 cells vertically.
2. Use the transpose function and select your dataset
Use the transpose function after selecting a blank range of cells by typing “=Transpose.” Choose your dataset next, then add it to the transpose function. Click and drag your mouse over the cells in your dataset to accomplish this. You can also type the range of cells manually. In parentheses after the transpose function, type “A2:B10” if your dataset includes the cells A2 through B10.
3. Press “Ctrl,” “Shift” and “Return”
Press “Ctrl,” “Shift,” and “Return” on your keyboard to activate the feature. Typically, youd press “Enter” to insert a function. However, because the transpose function is an array formula, pressing “Enter” results in an error. Use the keyboard shortcuts “Ctrl,” “Shift,” and “Return” to prevent the error. Excel presents your data vertically in the chosen rows after using the shortcut.
Tips for pasting horizontal data vertically in Excel
Follows these tips for pasting horizontal data vertically in Excel:
Select the “Transpose” icon
You can choose the “Transpose” icon to paste your cells vertically instead of using the “Paste Special” button. Right-click to reveal the options drop-down menu and select where you want to convert your data. Click on the “Transpose” icon, which is the clipboard with the arrow on it, beneath the “Paste Options” section. By doing this, your data is vertically pasted into the cells you’ve chosen.
Choose “Paste” in the “Home” tab
You can select a different method to transpose your data when using the first method to paste your data vertically in Excel. Click on the “Home” tab in the toolbar after copying your dataset and choosing where to paste the data. To activate a drop-down menu, find the “Paste” icon and click the down arrow. Select the “Transpose” button, which is below the “Paste” section.
Use the “Convert to Range” option
You are unable to use the transpose feature if you have data in an Excel table. Instead, use the “Convert to Range” option to convert the entire table. Select the “Convert to Range” button from the “Tools” section of the toolbar by finding the “Table” tab there. Additionally, you can change each column in your dataset by using the transpose function.
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
How to Paste Horizontal to Vertical and Vertical to Horizontal in Excel
FAQ
Can you copy horizontally and paste vertically in Excel?
Tip: To access the Transpose option, right-click on the new destination cell after choosing it, then choose the Transpose icon from the Paste Special menu. The same technique can be used with the Transpose option to paste horizontal data vertically.
How do you drag data from horizontal to vertical in Excel?
Tip: To access the Transpose option, right-click on the new destination cell after choosing it, then choose the Transpose icon from the Paste Special menu. The same technique can be used with the Transpose option to paste horizontal data vertically.
