Spreadsheet tip: Transpose data
The office productivity suite is a collection of software that enables businesses to create documents, spreadsheets, presentations and even forms or databases. There are more than a few different suites out there, and one of the more popular is Google Drive. One of Drive’s most useful programs is Spreadsheet. However, one downside of Spreadsheet is that it appears to lack the features of seemingly more robust products. For example, there seems to be no inherent way to transpose data but this feature is actually included in Spreadsheet.
If you are unfamiliar with Transpose
…a popular feature found in other spreadsheet programs like Microsoft’s Excel, it allows users to switch, or flip, selected rows and columns quickly and easily. While this may seem like a feature that you wouldn’t use too often, it can be a big help, especially when it comes to graphing and creating charts.
If you have ever painstakingly entered information into columns and rows, then gone to visualize it in a chart or graph and realized that it looks terrible, and it would look better if it was reversed, transpose can make this process easier.
How to transpose rows and columns
To transpose rows and columns in Spreadsheet, you first need to have data entered. From there you can:
- Take note of the cells in the first and last row and column of the data you want to transpose. For example, if you have data in Rows 1 to 10 and columns A to F, you will want to note cells: A1 and F10.
- Click on an empty cell in the spreadsheet. It is recommended to pick a cell below the current cells which are filled with the information.
- Enter: =transpose(A1:F10). Note: these numbers can change, depending on the cells you want to select. The : tells Spreadsheet to select everything in between A1 and F10, including those two cells.
- Hit Enter and the data should be flipped.
It is a good idea to be aware of whether you need to flip cells with text, say label cells, as well. Simply select these cells, along with the data cells, and Spreadsheet will take care of the rest.
If you want to copy the data from the new cells into the old cells, don’t simply copy and paste the cells, as this will actually delete all the information
Instead, select the new cells and copy them as you usually would. When you go to paste, right-click on the first cell, hover your mouse over Paste special, and select Paste Values Only. This will paste ONLY the numbers and words, but it won’t take the formatting. You should then be able to go and delete the transposed cells you previously created.
Transpose can be a useful feature, especially if you need to quickly flip data in cells. If you would like to learn more about Google Spreadsheets, or any of the other Drive products, please contact us today.