Transpose Function For Linked Data

As mentioned in my previous post, when we use the Paste Transpose command, the data between the two tables are not linked to each other. Below I will describe how we can Transpose data and with any data that we change in our first table, instantly the data will be changed also in the transposed version of the table.

In order to accomplish this, we count the Rows and Columns of the table that we want to Transpose. For example in the image below the area of the table is A2:E9, which means it consists of 5 columns and 8 rows.

01. Select Table

Now we must select a blank region of our worksheet with the dimensions transposed. Meaning that we have to select and area that has 8 columns and 5 rows as we can see in the image below.

02. Select Empty Cells

Now in the upper left center of the selected area we must type in the formula =TRANSPOSE(A2:E9), where A2:E9 is the address of our actual table.

03. Type In Formula

Once we have typed in the formula, we must press the combination of keystrokes Ctrl+Shift+Enter (which is known as an Array Formula).

Our data has been Transposed, and if we notice in the formula bar, we will see our formula with brackets around it as shown in the image below indicating that it is an Array formula.

05. Formula Bar

In the image below we can see our Transposed data, and any changes that we will make in the original table will also appear in the Transposed version. We cannot make any changes in the transposed version of the table.

04. CtrlAltDelete

You can also check out the video below, describing the steps in order to use the Transpose Function in Excel for linked data:

About officesmart

MVP: Honored with the MVP (Most Valuable Professional) for OFFICE SYSTEM title for the years 2011, 2012, 2013, 2014 and 2015 by Microsoft, for my contribution and commitment to the technical communities worldwide. Microsoft Master Specialist: This certification provides skill-verification tools that not only help assess a person's skills in using Microsoft Office programs but also the ability to quickly complete on-the-job tasks across multiple programs in the Microsoft Office system

Posted on March 26, 2012, in Excel 2010 English, Microsoft Office 2010 English and tagged , , , , , . Bookmark the permalink. Comments Off on Transpose Function For Linked Data.

Comments are closed.