Thursday, October 2, 2008

I Still Love/Adore/Worship Google: Spreadsheet LookUps

Despite a few stumbles in the recent past (especially the offensive Knol, for which I won't even provide link), the Google kids astound me on a regular basis. There is so much, coming so quickly, I don't even have time to explore a new, disruptive/transformative tool or service before they provide another. And yet, getting under the hood, finding the deep features or integration gems within these tools, is where the real beauty of Google resides.

Here's my latest, which I'm quickly discovering that most of my high-tech friends are equally in the dark over. We should have intuited the possibilities, given that it's GOOGLE providing the service, and realized that search logic would be embedded, but we're not as smart as Google so we never imagined how...or how beautifully.

So,Google Spreadsheets provides the creator (you) with the ability to do a Google Search (Lookup function) on another cell. If I put "George Washington" in cell A1, I can do a dynamic look up of his birthday in A2 with a function =GoogleLookup(A1,"place of birth") . Here's Google's explanation of the function GoogleLookup. After it does the lookup, it also stores the sites where the information was found and verified. Rest your cursor on the response to see them.

Tell me, how cool is that? Wait! It gets better. I can then populate cells for that column with like information. When I click on the cell holding "George Washington", a small blue box appears in the lower right corner. Just as with Excel, you could pull down from that box and populate the column with many, many cells filled with our beloved first president. BUT, unlike Excel, when you hold down the CTRL key as you pull down through the column, Google will populate with other instances/objects/things that are 'like George Washington'.
Give it a try, and depending on how deep down into the column you pull your cursor, you'll see our other beloved (and not so beloved) presidents appear. Hand-enter a number of cells to better narrow the data. (eg: Enter Los Angeles and Phoenix if you want large cities; entering just Phoenix will return cities in AZ. Google chooses its own definition of 'like' and it may not be as same as yours if it doesn't have enough data).

Here's the thing...Google is NOT as smart as you. So you'll have to check the data returned, even if you're specific. I needed to erase "Benjamin Franklin" from the data on U.S. presidents. He's 'like George Washington", but not a president. I knew that, Google didn't.

Same with cities and populations. I created a list of American cities 'like Los Angeles" (SF, NY, Chicago, etc) and then did a LookUp on population. Google nailed it, but instead of population for Philly, it gave me "10,882 sq/miles"). Huh?

It also gave me the correct population for San Antonio, but doing a LookUp on state in another column, it gave me Puerto Rico. Huh? Not like, unlike, except in Google's flat world.

Thus, a thing of beauty but only as smart as the metatags. A great case for the need to infuse information literacy into the expected outcomes of 21st century education. And a great example of why we love Google in emergent learning, but desperately need a digital literacy curriculum that focuses on critical thinking, evaluation, meaning and the notion of ownership over mere acceptance. Teach them to use the information at their fingertips, and then to evaluate the returns.

Here's the demo sheet, if you want to see results in action: Google Spreadsheet w/Lookups and populated columns. You must try it yourself to actually get in there and wander around in the data and functions, though. Publishing takes away the dynamic aspects of seeing the functions, markers, references, etc. And owning and creating your data is much more fun. For those of you watchin the stock market tumble, check out how you can use Google's related GoogleFinance function to look up stocks, current prices, track the tumbles, etc.

Hey, not until I published my spreadsheet did I see that Google adds citation mark and all the referenced LookUps at the bottom of the document on publishing. It justs gets better and better. Now, let's start worrying about a world where Google owns the information channels, tools and data services of the digital age. Somebody start creating applications and services as beautiful as Google's. Please.

2 comments:

aslubin said...

I have tried =GoogleLookup a number of times and have been unsuccessful in getting the control key to fill in additional information I am using an apple system 10.4 and wondering if the command might be different?

colleen said...

I'm hearing that different models of Apple are behaving differently. Someone got their laptop to do the lookup by using the alt key. On an older desktop, it was the apple key.

Apple's idea of innovation is to radically change hardware and software between models, so you'll have to play with your machine to find the right key to hold down.

Sorry, but it's worth it when you find the magic (semi-undocumented) combination!