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.
Transposing Data
This worksheet shows sales for each salesperson by month:
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.
Converting Text to Columns
If you have a cell in your workbook that contains a lot of text, you have the ability to separate it into separate columns automatically if there is a logical character that separates the text (such as a comma, semi-colon, or period). For example, in the sample worksheet, you can see that the Name column contains information that is probably better suited to two separate columns:
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.
Sign Up Now
In return for this Excel Tip –Transposing Data and Converting Text to Columns – I ask that you share this post with your friends and colleagues
Over to you
Did you enjoy this easy Excel Lesson? Are either of the tips new to you? How often do you need to transpose data or convert text to columns? Drop a comment below and you might earn your self some tokens
Don’t have a Hive wallet or a Steempress Account?
I would suggest that you sign up directly for your own hive wallet and sign in to steempress using your wallet. this way all rewards will be paid directly to your wallet within 7 days. You can use this link to sign up now for your Hive wallet
>> GET HIVE WALLET NOW<<
If you sign up using the comments section below you will get a Steempress account. Steempress will hold any rewards you earn until you have a hive wallet.
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
Cross posted from my blog with SteemPress : https://theexcelclub.com/easy-excel-lessons-transposing-data-converting-text-columns/