‘Transpose’ to change the alignment of cell contents in Microsoft Excel

[Total: 0    Average: 0/5]

Hello again! today I am going to write about a small but a very useful feature in Microsoft Excel. But sometimes it may be very powerful if you really want to change a series of contents aligned from vertical to horizontal or vice versa in the worksheet.

Pr-requisites;

  • The steps are demonstrated using Microsoft Excel version 2013.

For example, imagine we have a series of values from 1 to 10 entered as below from cells ranging from b3:b12 in a sequence.

Figure 01
Figure 01

Actually the sequence has nothing to do or has no meaning when it comes to a series of values in the above example. But when we consider a longer list similar to a series of city names, a list of countries, a list of surnames, etc instead in a different scenario, actually the ‘Transpose’ feature can play a vital role.

Let’s consider a real world example. Assume you have entered a list of product names in to a series of cells vertically, say you have entered around 500 or more till now. This is what will shock you. Let’s assume the order you entered (data) has to be maintained and we entered them vertically (by accident) and your Manager comes in and asks you to change the contents from vertical to horizontal, shocked?. What is the issue? The issue is, you have entered more than 500 product names in an order and now you need to change it.

Okay, let’s go back to the example now in figure 01 and refer to below steps.

  1. Highlight the values from cell ranging from b4:b12 as shown in Figure 02.

    Figure 02
    Figure 02
  2. Select Excel-transpose-f3from Clipboard section under Home ribbon (or press Ctrl + C) from key board to copy the selection in to the Clipboard directly.
  3. Now move to cell address c3 and it should look like below as in Figure 03.

    Figure 03
    Figure 03
  4. Now select Paste -> Paste Special option

    Figure 04
    Figure 04
  5. The Paste Special Dialog box should appear as shown in Figure 05 below.

    Figure 05
    Figure 05
  6. Mark the Transpose check-box and Click OK button to finish. You will see the below results as in Figure 06.

    Figure 06
    Figure 06
  7. Now highlight and press the Del key to clear the contents in the vertical range of cells.

Hope you learned something new today. Try it out yourself and post your comments or questions here.

Please follow and like us: