Transposing Data and Converting Text to Columns can be done quickly in Excel. Having the ability to do so, will save you time when you have these types of tasks to complete.
When you are starting out in Excel there is so much to learn and so much that can be easily missed. Transposing Data and Converting Text to Columns are not functions everyone needs and are often overlooked. Even if you don't use these functions often, its is great to add them to your skills as you never know when you will need them.
In this easy Excel Lesson we will look at how you can Transpose data and how you can quickly convert text to columns in Excel.
Let’s say that you want to have the months appear as columns, while the sales associates appear as rows. Making this change manually would be a very time consuming process, but luckily Excel 2013 has a way to swap this data for you.
To transpose your data, you first need to select the data in question (including headers). For this example, select cells A3:E15:
Next, you need to copy this data. Click Home → Copy or press Ctrl + C on your keyboard:
(You could also use the Cut command.)
As you cannot paste transposed data over the source data that you are copying from, right-click on cell G3. From the context menu, click Paste Special → Transpose ():
The copied data and headers will now appear transposed in the new location:
And that's how easy it is to transpose data in Excel. An alternative solution would be use to the TRANSPOSE function.
To convert this text to columns, you first need to select the cells in question. For this example, select cells C3:C6:
Next, click Data → Text to Columns:
This action will open the Convert Text to Columns Wizard. In the first stage of the wizard, you need to choose between delimited and fixed width data types. Delimited data types are those that are separated by characters (as the sample data is). Fixed width can be used when data is separated by one or more spaces. For this example, ensure that Delimited is selected and then click Next:
In the next stage of the wizard, you can select the delimiter type that the selected data uses. Since commas are used, uncheck the Tab box and check the Comma box. As soon as you make this change, the “Data preview” section will show what the data will now look like. Check the Space checkbox, since a space also appears between these two sets of information. Click Next:
In the final stage of this wizard, you have the option of specifying the format this data will be in, where it will go, and more. For this example, leave the remaining settings unchanged and click Finish:
A new column of data containing only the first names of staff will now appear adjacent to the column that you had previously selected:
And thats how easy it is to convert text to column in Excel. There are other alternatives, such as using Power Query, or in this example there are formulas that you could also use to separate out the names.
Don’t have a Hive wallet or a Steempress Account?
Have questions? Please use the Hive powered comments section below and we will do our best to help you. Alternatively, you can contact us with this link.
Like what you see? I do hope that you will share this article across your social profiles