#Using transpose function in excel how to
#Using transpose function in excel manual
#Using transpose function in excel free

Then, press the Ctrl + C buttons so the cell range goes into a copy mode like this. First, highlight the cell range which is the result of your TRANSPOSE formula. The way to paste value that is quite easy. What if you want a static result that isn’t going to change whatever you do to your transpose source, instead? One way to get a static transpose result is to paste value your TRANSPOSE formula writing. That means when you change the content of the cell range you input into TRANSPOSE, your TRANSPOSE result will change too. If you use TRANSPOSE to transpose your cell range, you will get a dynamic result. Get a Static Transpose Result 1: Paste Value That is because we combine our TRANSPOSE and IF formulas in the way that we have discussed before! Here is the implementation example of the TRANSPOSE and IF formulas combination in excel.Īs you can see there, we manage to keep the blank cells blank in our TRANSPOSE result. That is to say that we want to keep the cells’ content as they are if they are not blank.Īfter we write an IF like that in our TRANSPOSE, we press Ctrl + Shift + Enter and we will get our result! Don’t forget to highlight the result cell range first so we can get our TRANSPOSE IF result in full. We input a blank too as the IF true result to say that we want to keep the blank cells blank.įor the IF false result, we input the cell range itself. We declare this in our IF by inputting the logic condition where the cell range is equal to a blank (the symbol for the blank in excel is empty quotes ( “” )). If the cells in the cell range are blank, then we want to keep it blank.

#Using transpose function in excel free

Feel free to ask any questions in the comment section and please give me feedback.Quite simple, isn’t it? We just have to make a logic condition for the blank cells in the IF we write in our TRANSPOSE.

I hope all of the methods described above will be effective to transpose a table in Excel. Our operation is done, see the screenshot below for the outputs. ➤ Type pk in the Find What box and type = in the Replace with box. A dialog box named Find and Replace will open up. ➤ Click and hold the bottom right corner of the border and drag it to the right until it completes 6 columns. Step 1: As we want to convert Row 4 to a column, we’ll use the row’s cell references along a new column with some same unique characters before the cell references. Here we will use a cell reference to transpose.

#Using transpose function in excel manual

It’s like a manual method but pretty simple and time-saving. Method 5: Direct Reference to Transpose a Table in Excel The table is transposed now, see the image below. A new window named Power Query Editor will appear. Power Query is one of the best methods to transpose. Method 4: Using Power Query in Excel to Transpose Look the Pivot Table has been transposed. ➤ Press the Values menu and select Move to Row Labels. ➤ Press the Product menu and select Move to Column Labels. ➤ Go to the bottom of the Pivot Table field. ➤ Press OK, a Pivot Table field will appear. Or you can select the other option if you want it in a new sheet. In this method, I will use the Pivot Table to Transpose the data. Method 3: Use Pivot Table to Transpose a Table in Excel ➤ End the function with “ )” and hit the Enter button. ➤ Soon select the range of the table by dragging with a mouse or by typing manually. Here we shall do it with the Transpose function. Using a function is one of the easiest methods to perform a task in Excel. Method 2: Insert Transpose Function in Excel Table

#Using transpose function in excel how to

Read more: How to Transpose Columns to Rows In Excel Now see the table has been transposed to the new location. ➤ Press Ctrl+Alt+V, the Paste Special box will pop up. ➤ Active a new cell where you will transpose the table. ➤ Press Ctrl+C, a dancing rectangle will appear at the border of the selected range of cells. In the following table, I have used 4 columns with the headers of Product, Price, Quantity, and Total and 6 rows with the headers of some fruits names.īy using the Paste Special option here, I’ll transpose the table that means the rows and columns will be exchanged. Let’s get introduced to the dataset first. 5 Suitable Ways to Transpose a Table in Excel Method 1: Use Paste Special Tool to Transpose Table in Excel