MyFive: Google Spreadsheet Functions You Won’t Find in Microsoft Office
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.
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.
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?
This is just one of the many MyFive articles that we have done. You can find more of them by visiting our MyFive category, or by subscribing to our MyFive feed. We also have a full feed available if you want to receive all of our articles in your reader!
Tags: Google, MyFive, Google, Google Docs
Related Posts:
- EditGrid Brings Online Data To Your Spreadsheet
- Google Spreadsheets to get Macro Functionality Soon?
- Will Google’s Next Purchase Be An Online Spreadsheet?
- OpenOffice.org Has Released An Update To 2.0.1
- CNET Readers Give Reaction to Google Spreadsheet
Data Sort, lol are you kidding. You really don’t know Office or even Excel.
Hehe, and how many features exists in MS Office, you won’t find in Google Spreadsheet? I think, 5 would not be engogh…
Google have a few very good functions which Excel don’t have. I have help many people over the years in different forums how to do automatic sorting, finding unique numbers etc. Many of those questions involve long and complicated Array formulas (Ctrl+Shift+Enter). So I like Googles new functions and I hope Excel will follow.
A few online spreadsheets offer more formulas than Microsoft. I made a comparison a year ago. Here was the result:
http://www.dailydoseofexcel.co.....ment-25773
Three Google functions worth mentioning are the =ARRAYFORMULA(), =CONTINUE() and =EXPAND() function.
http://documents.google.com/su.....swer=71291
More: http://groups.google.com/group.....umentation
In Excel you can import data from the web. I think Excel refers to it as a “Web Method” (I think..it’s been a while). For a former employer I used that feature to import the companies stock price.
I think the issue with Excel is that there are so many features that it takes a true pro to really understand all of what it offers.
When Microsoft was planning for Office 2007 their research found that 90% of the features that people wanted already existed! That’s what lead to the overhaul of the toolbar and the reason the ribbon was developed.
We realize that you can manually sort data in Excel, but what function in Office will automatically resort data for you without using any VBA? If I’m missing something, I’d love to know. I’m not sure if you read the section on Data Sort and how it works in Google Docs, but with my extensive experience using Excel, I’ve never found a feature that works like this.
That’s true, but from a usability standpoint, not many people are going to use a majority of the features found in Office. The functions we mentioned today found in Google Docs are ones that a majority of users could use — they try to appeal to the masses.
Yes, Excel does let you grab data from the Web, and I’ve actually used that before. It could be tedious to work with though on some websites, and like Google’s importHTML function it pretty much requires that the website use tables to store the data.
hey!!!!!!!!
im looking for the function of leaves
where is it?????????????????