Ms. Excel Transpose Function

One of the ways of converting rows to columns or columns to rows in MS Excel is the use of the transpose function. It helps a lot when you want to either append a column or row to an existing row or column respectively with same dimension. However, do cell values change in the new location when they are changed in the previous location? The answer is NO. To achieve live changing cells in a transposed data table you need to customize the transpose function.

Customizing MS Excel Transpose Function

A combination of the transpose MS Excel Function and Control, Shift + Enter gives a quick way of creating dynamic changing values.

Procedure in MS Excel

Assume you want to automatically transpose the following columns to rows. That is transform product and cost columns to product and cost rows. Also we want to ensure that changes in the column values result to same changes in the row values.

  1. Highlight a new cell range location of two rows and six columns say A9 to F10 (A9:F10).

2. In the formula bar type the following formula =TRANSPOSE(A1:B6) and press ctrl Shift + Enter simultaneously.

A transposed cell range appears in cells A9:F10 as shown above. When you change cell values in the previous cell range, the changes are effected in the transposed cell range. With this kind of handy trick, you are sure that you will minimize repetitive data entry process. This helps you increase your workplace productivity by reducing time spent on data entry. In the long run, it also facilitates the creation of dynamic information systems that help capture dynamic changing variables in real life application.

For this and more,sign up to an upcoming Advanced Ms.Excel Training:  <See More Details>


It does not take long to get left behind

Stay relevant | Be informed | Sign up now and receive the latest tips, updates and industry insights...

You have Successfully Subscribed!