Excel sheet or spreadsheet is a powerful tool for managers and leaders. It allows them to have a better picture of where the business stands in a quarter or how the team is performing. But, preparing a spreadsheet report with data is not easy. To make it worse, every manager or business leader looks at data differently. So if you have created your report with the data laid out horizontally and your boss prefers the data laid out vertically, how do you transpose an excel row to a column quickly even when you’re on your phone or tablet?
This article will focus on the manual and formula-enabled ways to transpose your data from row view to column view. This might just be the trick that will also help you impress your boss with that report.
Also read: MS Office Brings Productivity Tools in a Single Mobile App
Inside This Article
Rows vs Columns: What’s the Difference?
Most of us are still prone to confusing rows with columns. And we want to take it upon us on Cellular News to clarify this for you.
Columns are the cells in a spreadsheet that run from top to bottom. There are 256 columns in a spreadsheet. They are typically labeled with letters in the alphabet. Now, you might be wondering how come there are 256 columns when there are just 26 letters in the alphabet? That is because when the column reaches the letter Z, the progression of columns continues on with AA, AB, AC…AZ. And then it progresses to BA, BB, BC…BZ and so on.
On the other hand, rows are the cells that run horizontally in a spreadsheet. There are 16,384 rows in a spreadsheet and these cells are labeled numerically.
How to Transpose Excel Row to Column
There are a number of ways to transpose a row to a column on an Excel spreadsheet desktop version. However, we want to focus on using Excel mobile app. One limitation of using the Excel mobile app is the absence of the special paste feature when transposing Excel row to column. However, there are still other ways that easy ways and tricks to do the job sans the hassle.
1. Transpose Excel Row to Column Using Copy and Paste
Copying and pasting is a technique that many of us are familiar with. It is a basic knowledge when using office tools on a computer. While this is one alternative in transposing Excel rows to columns, it is not advisable especially on short notice. One reason is that it takes time to get the job done. And, more importantly, the table is not dynamic. So if you change data in a cell on the original table, the changes will not be carried over to the transposed table. Also, this is only applicable to the desktop version of Excel.
However, if you prefer this method, here’s how to do it.
- Copy the cells on the table that you wish to transpose.
- Choose a cell where the copied data will be pasted.
- Right-click on the chosen cell and click on Paste Special.
- Under the Operation section, click on Transpose and then OK.
Easy-peasy. Now all you have to do is remove the original data and adjust the margins of the cells for better presentation.
2. Transpose Excel Row to Column Using Transpose Formula
The transpose formula in Excel is a powerful tool especially on short notice when you have to put data in rows instead of columns. This is not just applicable on the desktop version of Excel, but in the mobile app version as well. Here’s a step-by-step guide on how to transpose an Excel row to a column on mobile.
1. Tap on an empty cell in the Excel spreadsheet and click on fx or the menu button represented by three dots on the bottom of the screen.
2. Scroll down and select Lookup and Reference.
3. Again, scroll down and tap TRANSPOSE.
4. Select the range to want to rotate. In this case, it’s B3 to C6.
5. After the data has been selected, on the right side of the screen, tap the green checkmark
6. You’ll see the data on the original data table automatically laid out on the side in a row view.
7. Adjust the margins for better presentation.
This method makes the data dynamic. Meaning, any changes made to the original table will reflect on the transposed table. However, you can’t delete the original table otherwise the transpose formula will fail. What we suggest is either hide the cells containing the original table or transpose the data to a different sheet. Doing either of these will preserve the formula and the table for presentation.
How to Transpose Google Sheet Row to Column
Google Sheet is Excel’s main competition. However, these two spreadsheet processors offer almost the same features including how to flip rows to columns.
1. Transpose Row to Column Using Paste Special
Paste Special is a feature only available on the desktop version of Google Sheets. It’s a very easy way to transpose a row to a column table on a spreadsheet. Here’s how to do it.
- Select the data on your active spreadsheet, right-click then select Copy.
- On an empty cell, right-click and select Paste Special > Paste Transposed.
Your data will automatically be arranged in a column view.
2. Transpose Row to Column Using Google Sheets on Mobile
Here’s how to transpose a row to a column using a formula on Google Sheets mobile app.
- As always, make sure the data table is ready and accurate
- On an empty cell (or sheet) type in the formula for transpose =TRANSPOSE(
- Next, select the cells containing the data you wish to put in a transposed table, add a close parenthesis, and tap OK.
Download Google Sheets for Android
Download Google Sheets for iOS
Takeaway for Excel Row to Column Feature
Knowing a trick or two when using either Excel or Sheets is key to making reports bearable. Both of these spreadsheet processors are packed with formulas you can use to come up with your reports, calculate your data, and make charts. Knowing how to transpose an Excel row to a column, or vice versa is just part of the tip of an iceberg. There are other tricks and hacks like knowing how to create filters and conditional formatting.
So next time you’re presenting a report, know what view your panel or boss wants to your data presented. Also, remember the formula to use to transpose your rows to columns in either Excel or Google Sheets — it’s the same for rotating columns to rows.
