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 and a bonus tip 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 scrolling to view the data.

The Transpose Formula: 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

1. Create a new sheet.
2. Enter the following formula in ceel 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.
3. Press the enter key and watch the data fill the sheet!

Inserting Rows (optional)

• If you want, you can insert rows (use the “Insert” menu) within the data to add labels, notes, or formulas.

• Once you’ve entered a label or formula, grab the square in the bottom-right corner of the cell and drag across to the right to fill the label/formula across.

• For printing purposes (see bonus tip below) select all cells and change the alignment to “left” and “top.” (Use the “alignment” icon in the toolbar at the top of the page.)

•Change fonts, colors, styles, and sizes, as desired.
• Resize the rows to change the spacing within the data.

Example:

Bonus Tip
Printing All Records (One per page)

1. Follow the directions above to transpose the data
2. Follow the directions above to insert rows and add labels (optional).
3. Click the triangle at the top of Column A and choose “Hide Column.”
4. Select all columns with data.
5. Click a triangle in the column header area and “resize” the column widths to 725.
6. Note – The number 725 may need to be adjusted for different printers.
7. Choose the print options shown in the screen shot below.

If all goes well, each “record” will print on a separate page:

C