Google forms are fabulous for gathering information, but sometimes, the data in the spreadsheet can be difficult to view. In this post, I’ll share a simple-to-implement formula that will make it much easier to deal with (and even print) form-fed data!
The Original Data:
When you collect data into a Google spreadsheet from a form, it will look something like this:
If you have a large amount of questions (and especially if some of them are essay questions), it requires a lot of side-to-side scrolling to view the data.
Transpose Formula to the Rescue!
By using the “Transpose” formula, you can easily make the data look like this:
Notice that the information is now vertical instead of horizontal, making it much easier to view individual responses.
How to do it:
Transpose Formula
Changes data from rows to columns
- Create a new sheet.
- Enter the following formula in cell A1 of Sheet2:
=Transpose(Sheet1!A:H)
Note: Adjust A:H to the desired range of columns. If you don’t want the timestamp to show, enter B:H. - Press the enter key and watch the data fill the sheet!
Hi,
When I try to do this, I managed to rename the sheet1 fine but now the error message keeps saying, “Error: Result was not automatically expanded, please insert more columns (990).” Any ideas?
This is what I have put in: =Transpose(sheet1!A:AH)
I think that is a bug that will hopefully be fixed soon. In the meantime, try adding a few more columns first and then try the formula.
You can also download the Google sheet into Excel and use the Paste Special: Transpose
Will this still work if I want to use flubaroo to grade the spreadsheet contents?
I am trying to use this on the response page of a form. I have renamed the response page “Sheet1”. When I go to sheet 2 and use the formula =transpose(Sheet1!B:H) I get an error message. Any ideas?
Hi Jan,
The formula looks correct, so I’m thinking it has something to do with the sheet name. Make sure that there are no spaces before, after, or within the sheet name on the sheet tab and in the formula. If that doesn’t do anything, try renaming the sheet something simple like “test” and then changing the formula to =transpose(test!B:H) and see what happens.
Please let us know if you figure it out.
– Tammy
Found and used this formula this morning. Thanks, Tammy.
I discovered that if the sheet has a space in the name (i.e., Form Responses), I had to put a single quote around the name for the formula to work.
Great, and thanks for sharing.
When I tried your formula, it gave me this message:
error: Unknown sheet name
What is the sheet name?
Hi Allison,
In the spreadsheet, you will have two sheets (tabs at the bottom of the spreadsheet). The first sheet will have the data in a traditional format with the data in rows. The second sheet will contain your formula and will “transpose” the data into columns instead. In my case the sheets are named Sheet1 and Sheet2. If your first sheet is named something different, you’ll need to adjust the formula accordingly -– or just rename your first sheet to Sheet1 and then it should work!
– Tammy
Awesome! So simple it’s perfect! Thanks for sharing 🙂
Thank you for this great tip! I just wrote a post about using Google forms to collect information. You ae correct that having to read the rows are often very difficult. This is one I will use immediately.