This article was written on June 13, 2008 by CyberNet.
Today we’re taking a look at Google Spreadsheet Functions you won’t find in Microsoft Office. If you haven’t played around with Google Docs before, you probably haven’t realized the long list of functions that are available. We’ll be covering the following functions: Import Feed, Google Finance, Google Look-up, Import HTML, and Data Sort.
Before we get started, we wanted to mention that there aren’t really functions that can do things like this in Microsoft Office, but that’s not to say that it’s impossible to do. These functions are nice because all it takes is a simple formula and very little effort to get the results that you want.
If you have a favorite function in Google Spreadsheets, let us know in the comments below…
1. Import Feed
We’re not going to get into this one at great lengths because Digital Inspiration wrote a great article about how to turn Google Docs into an RSS reader and feed aggregator. It’s actually fairly simple to do. This function might be useful if you want to embed feeds from another site into yours. And because the spreadsheets are so customizable, you have quite a bit of control over the layout and how everything will look.
More info here
2. Google Finance
Another great function allows you to monitor your portfolio right from a spreadsheet in Google Docs. If you decide you’ll want to use the Google Finance function, you’ll probably want to take a look at the help page where Google explains how to do it. Essentially what you’ll need to do is enter the following in a cell on your spreadsheet:
=GoogleFinance("symbol","attribute");
So for example, if I wanted to follow the price of Microsoft stock, it would look like this:
=GoogleFinance("MSFT","price")
Different attributes that you could choose from include price, volume, etc.
3. Google Look-up
You’re probably familiar with Google Lookup as a way to quickly get information when you’re performing a search. You can also use Google look-up in a spreadsheet with the Google Lookup function. There are two parts to the formula that you’ll need – the entity and the attribute. If I wanted to know the population of Chicago and keep that information in a spreadsheet, the entity would be “Chicago” and the attribute would be “population”. I’d enter the following into a cell on the spreadsheet:
=GoogleLookup("chicago","population")
Once I entered the above information, the cell displayed that the population of Chicago was 2,871,499 and then had 2005 in parentheses so that I knew when the data was from.
The population of cities is always changing, and my spreadsheet would update when necessary, with the new information.
One last note, when you hover over a cell that has used the Google Look-up function, you will be able to see the source that Google used. Just note that you can’t use this function more than 250 times in one spreadsheet.
More info here
4. Import HTML
The Import HTML function is nice because it allows you to perform you own calculations on data you find on the web. It takes a little effort to do, but it could be worth it. Examples of data that you could import include a list or a table.
Maybe you find a table full of information on Wikipedia that you’d like to run calculations on or format differently. All you have to do is provide the URL to where the data is, know whether it’s a table or a list ( provide the tag), and then examine the source code (or just play around with the formula until you get the desired results) to see how many times the tag appears on the page before it’s used for the data you want.
Here’s an example. I found a table over at Wikipedia that displays weather averages for Chicago. It’s in table form (they used the table tag <table>), and then I looked at the source code to find that it’s the 4th table on the page. To get this information into my spreadsheet, I’d enter the following into a cell:
=ImportHtml("http://en.wikipedia.org/wiki/Chicago","table",4)
I included the URL for the location of my table, the structure (the fact it’s a table and not a list), and then the index of 4 which tells Google to look for the 4th table html tag, and it worked.
5. Data Sort
Last but not least is Data Sort. This is useful because instead of manually having to sort your data, you can set-up a function so that whenever your data changes and you add new information, Google Docs will resort it for you.
Imagine having three columns of data (located in columns A, B, and C respectively): in the first column is the person’s first name, the second column is the person’s last name, and the third column is the person’s phone number. Off to the side of the data (or on a new sheet) you could enter:
=SORT(A:C,2,TRUE,1,TRUE)
That says to sort the data in columns A through C, with the 2nd column (last name) being the primary sort option. By specifying the “TRUE” immediately after the “2″ it says to sort that column in ascending order, whereas “FALSE” means to sort in descending. Any columns that you don’t specify in the “SORT” function will be left unsorted, which in this example is what happened to the 3rd column (phone number).
Since this sorting function leaves the original data untouched you don’t have to worry about it changing things around on you. Ryan used this feature extensively in a spreadsheet he shares with our family that contains the addresses and contact information of everyone. Instead of explaining to people how they can manually sort the data on the spreadsheet, he just created a few different sheets (a.k.a. tabs) that each have the list of contacts sorted in different orders. If they want everyone sorted by last name they just have to flip to one sheet, or if they want to view the list by first name they open up a different sheet. To them it looks like he copied and pasted the information into several different sheets, but in reality he just uses this sort function to duplicate the contact information. Clever, huh?
More info here
Copyright © 2014 CyberNetNews.com
While 
