Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Google Forms: How to deal with formulas in the spreadsheet

Google Forms: How to deal with formulas in the spreadsheet

If you have ever used a Google Form where the data goes into a spreadsheet you may have noticed the tab where the Form data goes acts a little funny.   For the most part I like to leave the “Form Responses” tab alone and use other tabs to write my formulas and organize the responses.

Method 1: Duplicate the sheet.  Click on the little arrow on the tab and choose duplicate.  This allows you to change the data, move columns, write formulas, delete data you didn’t want, etc… without messing up the original data.

This is my method of choice if I am done collecting the data, I find I frequently want to go back and look at the original data set, but like being able to totally mess up the spreadsheet.  If I do mess it up, I can always make a new copy of the data and start over.

Method 2: Link to the “Form Responses” information on another tab.  If you create another tab and put =’Form Responses’!A1 into the new sheet, what is in cell A1 from the form responses tab will show up.

You can then insert columns, delete columns or rows, or can just change the data without destroying your original values.  What you type on the new sheet does not change the values in the “Form Responses” tab so you can play with the data without risking messing it up.

YouTube video

Method 3: The problem with using =’Form Responses’!A1 is that you can not sort that data since it is mirroring what is on the Form Responses sheet.  The other problem is if a new response is submitted into the form it does not go to the next row on the Form Responses sheet it inserts a new row.  This is an important distinction.  If the form simply put the next persons responses on the next line, let’s say row 10, then on the new sheet you created =’Form Responses’!A10 would show the timestamp for that form submission.  However, since it INSERTS a row, what WAS row 10 is now row 11.  Spreadsheets automatically adjust your formulas when you insert rows or columns so your formula =’Form Responses’!A10 is automatically changed to =’Form Responses’!A11. Meaning that 10th row on Form Responses will not show up on your other sheet ever unless you redo the formulas.

This is the same reason why when you write a formula on the Form Responses sheet in a blank column that when a new response is submitted the formula does not appear next to that response.

YouTube video

Solution: Use =importrange(“spreadsheet key”,”tab range”)

While I usually use =importrange to bring one Google Spreadsheet data into another spreadsheet, you can actually use this within the same spreadsheet.

Create a new tab.  In cell A1 write the formula =importrange(”
The URL at the top of the spreadsheet contains the spreadsheet key. It is a long string of weird numbers and letters.  It is NOT the whole URL.  Make sure you put this in quotations.

Comma

In quotations put the name of the tab, an exclamation mark and the range of cells.

=importrange(“0AiqrAI5UxSJNdGs2a1dZbkRuMWozak5Sanc5Wm95LWc”,”Form Responses!A1:C300“)

Even though I may only have 20 responses in my Form Responses I will still write my import range to be from 1 to 300 so that as new responses come in I do not have to modify the formula.

Using the import range method when new responses are submitted, they will be referenced on the new tab and the formulas you write will still work.

YouTube video

32 thoughts on “Google Forms: How to deal with formulas in the spreadsheet

  1. Wow, I scoured the internet for someone who could show me how to solve this problem. And you did it with a video! Very helpful, thanks (I’m making a spreadsheet for a babysitting coop, and I couldn’t figure out how to use formulas with the form data).

  2. Method 3 is exactly what I need, but I can not seem to make it work. I’m copying the formula directly from above, then pasting in my spreadsheet “key” (from the = to the #) in place of yours (“0AiqrAI5UxSJNdGs2a1dZbkRuMWozak5Sanc5Wm95LWc”). I’m wondering if something might have changed with Google.

    Thank you so much for any help

  3. Have you worked with arrayformulas? It’s contrary to your desire to stay out of the form tab but it would solve the problem you initially show regarding new entries not carrying over your formula.

    Try this in E1 for another way to get the calc to handle new/insert rows.

    =arrayformula(IF(U1:U=”male”,”this is for a boy”,”this is for a girl”))

    if you want to be clean with it you can add a null for rows not yet populated

    =arrayformula(IF(U1:U=”male”,”this is for a boy”,IF(U1:U=”female”,”this is for a girl”,””)))

  4. This is fabulous. I’ve been working on a solution that you’ve described in ‘solution 3’ by using arrays and it got really messy. Thanks so much 🙂

  5. Hi Alice,

    I was using method #3 – to display the data – as I wanted to add a drop down column in order to manually set a value for each entry – the problem is that when a new line is added via the form – everything moves down one – EXCEPT my drop down boxes. Is there anyway to have this data shift down too? Thanks in advance for your help.

  6. Alice- The Import Range function no longer seems to circumvent the INSERTED row issue. I think something changed with Google. Please tell me I’m wrong or there’s another answer! THANK YOU!

  7. Hi Alice,

    Great video. I’m in need of help. I have a google form and that is where we get date to do estimates. I created a separate spreadsheet for calculations on the responses sheet. It already has all the formulas and calculations. My problem is everytime someone fills out the form it still skips the rows so the date is not being populated on the Calculation sheets. I tried using this but it’s still the same. Hoping for your response.

      1. I’m trying to calculate an estimate on the Calculations sheet and I’m taking data from the original sheet. It has a lot of calculations maybe that might be the problem?

  8. Hi, I found that i had ti use this “docs.google.com/spreadsheets/d/1a_OAz-O5nJnd4ENVvcxs66zuHy-VDdNNbQtn4ML07Bw/edit#gid=###### ” to get the ImportRange to work on my sheet 2.

    However, the formula on my Form Responses 1 did not copy over the sheet 2, just the value and not the formula…any insight into this problem would be great…

    Awesome video and excellent teaching!

  9. Cool, thanks.

    I was taking form inputs and added an extra column to that sheet to do some calculations. Unfortunately, those formulas got wiped when a submission was made.

    Simply made a new sheet, then did the formulas on the new sheet.

    You’re a star! Thanks again.

  10. Thank you so much! My head was spinning until I read your blog. Your solution worked like a charm!! Really appreciate you posting this information.

  11. Still having issues on form submit. All my formulas are pushed down on every form submission. Tried import range into another sheet but formulas get pushed down there too! Any ideas? I’ve been trying to solve this for weeks.

  12. You are awesome!!!!!!! I’ve been searching for this function to make some really neat things happen in my music room in several different form experiments! Today you’ve helped me my half day activity of entering student observations about violin and guitar similarities/differences (Entering it myself in class discussion for now, but in the future a flipped classroom experience…) with the end result as a word cloud. I’m trying to avoid all the in between steps that make it work, mainly replacing spaces with an underscore so that the word cloud generator keeps phrases together. Now that new form entries are copied over automatically I think I’m one step closer to the slick tool I want! Yay Google!

  13. I am having the same exact problem as Redg Golez. Like the other user I have a spreadsheet for calculations with the values from the response sheet. My problem is that every time someone fills out the form the formula skips a row and then does not point to the correct response row. Any help is much appreciated. Here is a link to a the response spreadsheet: https://docs.google.com/spreadsheets/d/1uA_b5mnHVgBkhKTcj1ALo0BcbAsPCJUlpV0DsOeAJdc/edit?usp=sharing
    It must have to do with changes Google implemented either for their form refresh in January of 2013 or in October 2014, because I created those forms back in 2012 and they did work until I got some reports from users recently.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.