Let Them Eat Cake

When you think of an analogy for how well two things go together, do you choose "peas and carrots"? I often think of the old-school Reese's commercials where someone's chocolate was always ending up in someone else's peanut butter. But I doubt that anyone ever tells you that "X and Y go together like Excel and GoogleDocs."

It's an uneasy partnership, to be sure, but there is no reason to think that these two tools can't be used together. I was thinking about this recently while looking at some templates for school administrators. Some use Google Forms to capture information (for example, from classroom observations). The templates developed from these have all sorts of analysis built from the data collected in the forms. There are definite advantages with using the Google Forms to collect data. You only need wifi and an Internet connection---no special apps, no worries that Excel doesn't run on a tablet, and it can be platform agnostic. The disadvantage is simply that Google Spreadsheets can be a bit fussy to work with...and, the results are often crude and visually unappealing.

But why can't you have your cake and eat it, too (and isn't eating cake better than peas and carrots)? Is it so much to ask that you be able to marry the convenience of a Google Form with the genius of Excel?

I know some of you are saying, "Duh...you just download the spreadsheet from Google and open it in Excel." And you're right---you can certainly do that. And every time something is updated in Google, you have to download a new version of the spreadsheet. But perhaps you didn't know that you can connect Excel with the Google data source...and then just refresh your Excel workbook?

Bake the Cake
For this example, I started by creating a Google Form. The purpose of the form is to capture observations of students. This is just a "proof of concept" deal, so I only included three classes. You could certainly add lots more classes, or if you're an elementary teacher, you might skip this piece and just kick things off with your class list.

This Google Form has five pages. The first is what you see on the right, and depending upon which radio button is checked, the user is sent to one of three pages (Biology, Chemistry, Physics). The final page contains the "submit" button. Makes things a little clunky---I'd rather be able to submit data from each class page, but sometimes you have to take what Teh Googles gives you.

Each class page has a dropdown menu with the class list and another menu that allows the teacher to choose a standard. Finally, there is a space to include a description for the observation.

Lots of possibilities here for teachers. You could add choices for student behaviors...parent contacts...classroom management ephemera (hall passes, pull-outs, etc.). Make a form that suits whatever your needs may be---and don't forget that there are already lots of templates out there for both students and teachers.

Okay, once you've set up your form, you can test it out and have a look at your Google Spreadsheet. I actually did three tests---one for each class period. You can see how the data are organized:

Your final tasks are to Share your document and Publish it. If you don't want to change the settings for this spreadsheet such that anyone with the link can view it (even though that "anyone" will only be you), you will just need to remember to be logged into your Google account each time you want to sync with Excel. Secondly, while choosing to "Publish to the Web" (under the File menu) is not required to sync with Excel, doing so will give you a much cleaner result. After you have published the document, copy the link that appears for sharing the document. You'll need this for the next part of the set-up.

Make the Icing
Now it's time to set up things with Excel. Open Excel and click on over to the Data tab. In the Get External Data section, choose "From Web."

We're going to use this feature to link Excel with our Google Spreadsheet, but keep in mind that you can use this trick to pull data from most websites. Need stock info? Weather data? Sports scores? Excel will be happy to collect that for you.

After you click on the "From Web" option, a pop-up browser window will appear. Paste the link for your GoogleDoc to bring up that spreadsheet. Anything on the webpage that is fair game for pulling into Excel will have a yellow arrow beside it. Click on the arrow beside your data (the arrow will turn green). Then click "Import." Excel will ask you if you want to put the data in cell A1 of the workbook. You can click "Yes" or choose another spot.

Now, when the data imports, you will probably get some extraneous bits. Notice in the sample below that cell A1 has the name/sheet from Google, there are some extra rows (2, 4, and 5), and a footer in row 9. But still, we're pretty clean. Just save your workbook and you're good to go.

Serve It Up

This is where the magic really happens. Keep collecting data using your Google Form. Whenever you want to sync the information with your Excel version, open your Excel spreadsheet. Go to the Data tab and click the "Refresh All" button. Abracadabra! Excel will grab all the new data for you.

You can also check out the "Connections" and "Properties" options if you want Excel to check for new data on a particular schedule or you have other adjustments to make.

Now that your data is nested in the comforting arms of Excel, you can build additional reporting tools or data analysis options within your workbook. Just set up whatever you need on other sheets and let those update every time you refresh, too.

But you don't have to take my word for it, you can try all this for yourself.

• Here is the link for the Google Form. Add some data.
• Then, check out the Google Spreadsheet. You can view all of the data, including what you've added. If you like, you can save a copy to your own account to play with.
• Finally, download the Excel workbook and refresh the connection.

I'd love to know what possibilities you see for your classroom or school. Bon appetit!