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.

google lookup function-2.png

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.

import html google docs.png
More info here

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

There Are 12 Comments

  1. Data Sort, lol are you kidding. You really don’t know Office or even Excel.

    • They are not kidding. They know the difference between a function that can be used as formula in a cell and an operation that must be performed by clicking with your mouse. But it seems you don’t.

  2. Hehe, and how many features exists in MS Office, you won’t find in Google Spreadsheet? I think, 5 would not be engogh… :roll:

  3. 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:
    [dailydoseofexcel.com]

  4. Three Google functions worth mentioning are the =ARRAYFORMULA(), =CONTINUE() and =EXPAND() function.
    [documents.google.com]

    More: [groups.google.com]

  5. 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.

  6. 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.

  7. fred zhang wrote:
    Data Sort, lol are you kidding. You really don’t know Office or even Excel.

    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.

    Steve wrote:
    Hehe, and how many features exists in MS Office, you won’t find in Google Spreadsheet? I think, 5 would not be engogh… :roll:

    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.

  8. CoryC wrote:
    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.

    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.

  9. hey!!!!!!!!

    im looking for the function of leaves
    where is it????????????????? :evil:

  10. Google kicks Microsoft’s @ss with easy to use and powerful tools that all of us use everday and some of you people are going to whine that Microsoft has it all??? Are you serious? How much did you pay Google this year? People are nuts.

  11. Rudra Mani Sharma

    Hi,

    I was surprised to see the functions of google spreadsheet. As an excel user I always preferred excel over google but now I’ve started thinking seriously about latter. I was struggling with sort function but it is quite clear after reading your post. Thanks a lot..

    I am also struggling with import range function can u pls help me?

    Thanks in advance

    With Regards
    Rudra Mani Sharma

Leave Your Comment


Message is the only required field.
Emails are not published.