Before we go into the many ways of transposing in excel, let’s first explain what transposing is and why we may need to transpose in excel.
What is the Excel Transpose Function
The dictionary definition of transpose means to cause two or more things to exchange places, and in the context of excel, this is exactly what the function does. However, it is specific to changing orientations of a given range or array either from a vertical range to a horizontal range or from a horizontal range to a vertical range.
Why do we need to Transpose
In most cases during the preparation of an analysis or a model, you would have gone a long way building the model before realizing that it would look and feel better if the data is presented vertically or horizontally. The issue however is that you might be working with a very large data set and it may be inconvenient and time wasting to start cutting and pasting the previous data into a new orientation. Reason being that it would most likely take you the same amount of time it took to get to that point, if not longer.
That is where the transpose function comes in. The transpose function helps you flip the orientation of a given range easily and save a lot of time doing it.
How do we Transpose in Excel
They are various ways to transpose depending on the dataset. If the dataset contains no formula, then a simple copy and paste special transpose may suffice but if part or all of the data depends on the use of formula then you would need to go the extra mile and transpose using find and replace function. Don’t worry, we would treat them all in this post.
Paste Special Transpose
In the image above we have the top 5 states by population in the United States on one row and their population on the row below. Now let’s transpose the information to a vertical orientation.
Step 1: Select the range B3:G4 and copy (right click and click copy or press Ctrl + C)
Step 2: Navigate to the new cell where you want the data to begin (let’s take cell C7), right click and select Paste Special
Step 3: Select Transpose in the pop out box and click Ok
Result: You should have a similar result to the below. You can then format by adjusting the columns or rows of the transposed range
Using Transpose Function
Using the same example for this section, however rather than using the copy and paste special transpose method, let’s use the excel transpose function.
Step 1: Select the new range matching the number of cells as the original. In our example, we want the starting cell for the transpose range to be C7 and horizontally we have 6 contiguous cells on two rows. To flip it, we need to select 6 continuous vertical cells on two columns.
Step 2: As the range is selected, begin typing the formula =Transpose(
Step 3: Select the original dataset B3:G4 and close the parenthesis.
Step 4: Press CTRL + SHIFT + ENTER and you should have the result below
You can see in the formula box that the formula is enclosed in curly braces. This is because it is an array formula and array formulas deal with a block of cells and not just once cell like regular functions.
Transpose Using Find & Replace Function
Transposing data containing formulas is a little tricky because excel uses relative references in formulas meaning that if you transpose data to a different region, excel would refer to the cell that is located the same number of columns to the right or left and the same number of rows above or below and this would definitely not work especially if the cell referred to within your formula is outside the range being transposed. How then do we go about this? Let’s see the steps below.
we added two rows to our previous example, the female population estimate from total population and male population estimate from total population. As you can see, these rows refer to either the female percentage split or male percentage split on rows 2 and 3 respectively.
Now we need to transpose the data into a vertical orientation whilst ensuring that the female and male population calculations are still picking the percentage splits on rows 2 and 3. It may look complicated, but it is very easy if you follow these steps.
Step 1: Highlight the cells containing formula. In this case we would highlight the range C4:G8
Step 2: Press CTRL + H to pop out the Find and replace window
Step 3: In the find what command box, type (without quotation marks) : equal sign (=) and in the replace with command box you can put in any thing so far it doesn’t begin with ” = ” . In our example, let’s add a star before the = sign so that excel doesn’t pick it as a formula yet.
After you are done with the above steps, click the Replace All button
Step 4: You should see a notification similar to the notification below. Click on OK to proceed
Step 5: Copy the entire data set from B5:G8 and select a new cell you want to transpose your data to. That cell in our example would be cell B12. Then right click and paste special as detailed in preceding sections
Step 6: Check the transpose box and press enter
Step 7: If you did everything correctly, you should have a similar output to the below
Step 8: Now we need to replace Star Equal sign (*=) with plain Equal sign (=) to enable activation of the formulas. Press CTRL + H and in the find what command box, type in Star Equal sign (*=) whilst in the replace with what command box, type in Equal sign (=) then click on the replace all button.
Result: Click Ok and check that the transposed range is still picking the correct cells.
