Easy Excel Lessons – Transposing Data and Converting Text to Columns

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:


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:

Are you enjoying learning Excel with The Excel Club?  Do you know we have a free basic Excel Course?  Do you know we also have a free Power BI introduction Course?

learn Power BI

Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox

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

%d bloggers like this: