Creating Great Spreadsheets with Google Gadgets

July 19, 2011

When it comes to interesting uses for spreadsheets, Google Spreadsheets has some tricks up its sleeve that even Excel doesn’t offer. One of these is the capability to create maps with data that’s stored in a worksheet through a Google Gadget. (Coincidentally, a similar feature was available in Excel 2000 and earlier versions; however, it was subsequently removed and became the stand-alone application Microsoft MapPoint.)

In Google Spreadsheets you start by adding data listed by region, using ISO country codes or U.S. state codes. The codes go in the leftmost column; in subsequent columns, you add the data to show on the map, one column for each map. You then insert the Heatmap gadget, select the data, and choose the map to use–the data will be plotted as colours on the map.

Google Spreadsheets Gadgets, such as the Heatmap, offer useful tools for mapping business data. You can also make use of a map gadget to show, for instance, where your business offices or distributors of your products are located, by marking them on a Google Map. To do so, place the address to map in one column and any tool tip text to display when the user hovers their mouse over that location in the adjacent column. You then insert the map gadget and select the cells containing the data. At any time, if the data in the worksheet is updated, the map will redraw automatically. Combine this flexibility with the capability to publish a Google Spreadsheet to the Web, and you have a great tool for providing location-based business information in a graphical way.

Yet another feature of Google Spreadsheets that you can harness for business use–and has no direct parallel in Excel is the Form feature, which allows you to create a form inside a spreadsheet to collect data from a user. You might use it for an invitation for which you’re collecting replies, for example, or a customer survey form. (My blog contact page uses Google Forms)

Once you have created the form, which can contain multiple-choice questions, checkboxes, and the like, you publish it to the Web. As users complete the form, the data is automatically added to the spreadsheet, allowing you to aggregate data from surveys or to gather replies to an invitation all in one place and without your input. (Although you can create a form in Excel, it offers you no simple way to publish it online and automatically collect the data.)
Need to know more?

Categories and tags

Do you like what you see? Hire us!