Kick Your Excel Habit

During the last decade, I worked on many different projects with various companies. Regardless of whether I was helping a pharmaceutical firm speed production of a rabies vaccine, assisting a health department with the containment of infectious disease, or managing post-merger integration of retail firms, there invariably came a time when I needed to collect information for use in activities such as:

• Email migrations
• Computing platform migrations
• Hardware life-cycle planning
• Participant scheduling at educational seminars

Would-be respondents were rarely in the same location, so I needed a way for them to provide me with information in a way that was convenient to them. While it might seem sensible to create a web page to collect the data and then send a link to all users, there are a couple of problems with that approach. First, the necessary infrastructure is rarely available to consultants. Second, most consultants don’t understand how to configure a web server, write an interactive web page, or create the corresponding database that manages the information.

Given these limitations, consultants typically resort to their favourite tool: Excel. They create a spreadsheet, add pretty column headers indicating the fields they’d like to capture and, if they’re savvy, they might even lock the sheet and add some validation rules. When the new spreadsheet is ready, it gets mailed out to 8,000 people and the waiting begins.

That approach certainly works, but it’s pretty terrible for everyone involved. Consultants dislike it because merging spreadsheets is mind-numbing. Clients dislike it because it means they’re paying $300/h for something that could be done by a clever third grader. To mitigate some of these problems, I wrote a Perl script that collected all the spreadsheets in a particular directory and merged them into a new spreadsheet. This was a great solution for me because I know what Perl is, understand how to get it and am a deft Perl hacker. However, for the same reasons that writing a custom web page is a bad idea, so, too, is using Perl: most consultants lack the necessary skills.

By now you might be thinking, “Well, since we can’t set up a web server and don’t know Perl, we’re using the best approach.” You’d be wrong. Before you go all eRoom or Survey Monkey on me, let me show you a new feature in Google Docs that will blow your mind (and save you a ton of time, too). If you don’t want to read the rest of this article, Google has a helpful video on YouTube. Although many firms prohibit consultants from using public web applications as a repository for project files, using such an application to collect the data necessary to schedule conversions and training seems perfectly reasonable. So, let’s get started. Open up your favourite web browser and head to https://docs.google.com/.

If you don’t have a Google account, you can sign up for one now. Otherwise, sign in. When you get into Google Docs, you’ll see something similar to the image below.

Click the New button on the toolbar and choose Form. The form builder window appears, and you can start creating your data-collection form.

In this scenario, I’m creating a questionnaire that IT staff can complete to describe the servers they manage, which will be migrated into a virtual environment. In the first box, you can add a short description to explain to people why this data is being collected. I think it’s best to offer contact information in case people have questions. Then, simply add the fields for each data element you’d like to collect. You can use a variety of widgets including radio buttons, check boxes, text and paragraph boxes and drop-down menus. When collecting data for projects, you should try to constrain the universe of options by using drop down-menus, or radio buttons with an “Other” option. It will help a lot with data standardization and save you a lot of time during analysis. When you’ve finished adding questions, you may wish to pick a theme to give your form some style. I like the Header (Blue) theme.

One you’ve chosen your theme, click Apply to return to your form. Click the save button (top right). At the bottom of the window, you’ll notice a URL. Click that link to open your form in a new window. Nice, right?

Close the live form window, and return to the form editor. Now you’re ready to distribute the form. Click the Email this form button and add all the email addresses to which you want the form sent.

When you’ve added all the email addresses, click Send. Google will invite all the addressees to complete your form. Whenever users complete the live form, the data they enter will be added to new spreadsheet that is created automatically. Now, sit back and wait for the data to roll in.

When you return to the Google Docs main page, you’ll see you your survey called untitled form. You should give it a more descriptive name (I called mine Server Questionnaire). Just right-click the form and choose Rename.

You’ll notice that if you click on the Server Questionnaire, it opens a spreadsheet where all the data is collected. The data is live, so as people complete the form, you’ll see new rows appear in your spreadsheet. You’ll also see the menu bar items Form(3), where the number – 3 in this case – represents the number of responses you’ve received so far. If you click the Form menu, you can edit the form, stop collecting further responses and show a summary.

Take a minute to appreciate what you’ve just accomplished. You created an online data collection mechanism where participants enter data online, and all the data ends up in a central repository. You won’t have to do any cutting and pasting and merging. Is that not just about the best thing you’ve heard all year?

When your new super-efficient system has collected all of the data (or at any time during the collection process), you can use the File button to export your sheet to Excel or several other formats.

Already, I’m sure you can appreciate the power of this tool, but I’ve just scratched the surface in this article. Besides enabling live forms, Google Docs allows real-time editing of sheets, built in instant messaging to help with collaboration and revision control for all changes. And, for the hackers among us, the Google Docs API allows programmatic access to the sheet, so you can build powerful mashups or further automate your activities.

Although you may never use all of the features of this tool, if you take advantage of only the ones I’ve written about here, you’ll incorporate lots of efficiency into your workflow and be on your way to blowing the doors off your client.