Perform Amazing Feats With These Useful Google Spreadsheet Functions

Ads by Google

gspreadsheets   Perform Amazing Feats With These Useful Google Spreadsheet FunctionsMany who know me may know that I’ve always had a “thing” for spreadsheets and data. For years now, I’ve offered the occasional VBA article with a few tips and tricks to do funky things in Excel, like automatically sending emails from inside of Excel, exporting tasks from Outlook to Excel, or otherwise using Excel to manage your life.

I am ashamed to admit that I’ve only recently decided to start experimenting with Google Spreadsheets, and what I’ve discovered is that whatever allure or power Excel had over me due to the amazing things you could do with VBA scripting, is completely overshadowed by the awesomeness of the sorts of things you can do inside of Google Spreadsheets.

After playing around with Google Spreadsheets for just a few weeks, I can barely contain my excitement. Many of the things I’ve discovered one can do, I plan to cover in future articles. However, as a quick preview let me say — since Google Spreadsheets is an online application — it is far easier to integrate it with many of the online tools and services that you already use, like data from websites, information from other Google Spreadsheets, information from (or to) emails, and a whole lot more.

Honestly, I feel like a kid in a candy store here.

6 Amazing Things You Can Do With Google Spreadsheet Functions

In Spreadsheets, there’s an entire library of Google Spreadsheet functions that you can use in intricate ways to perform impressive feats of data manipulation the likes of which you’ve never seen before. If you thought the power of Excel functions were cool, you really haven’t seen anything yet. You can treat the functions in Google Spreadsheets almost like mini-scripts in themselves, nesting one function within another as many times as you like in order to produce some really cool output information.

Ads by Google

I’ll cover how to do that in this article, as well as creating intelligent conditional formatting rules, creating output charts and in-sheet bar graphs, and much more.

Fun With Google Spreadsheet Functions

A good majority of functions that are used in Excel, can also be used in Google Spreadsheet. However, there are some neat little functions that are entirely Google specific, and one of those was actually mentioned by Ann Smarty years ago – adding current time to the sheet.

You can do this using the usual Text(Now()) function, but the problem there is that it only updates the displayed time when you edit the sheet, not whenever someone views the sheet. The better alternative is actually the GoogleClock() function.
googlespreadsheets1   Perform Amazing Feats With These Useful Google Spreadsheet Functions
This always shows the current date and time – which is really useful for sheets where the viewer needs quick access to the current date/time to either fill out forms or do calculations with data from the sheet.
googlespreadsheets21   Perform Amazing Feats With These Useful Google Spreadsheet Functions

The example spreadsheet I’m going to use in my examples in this article is one where I’ve imported data from Google Adsense. This was done simply by exporting an Adsense report into CSV and importing that into this Google Spreadsheet.

If you’ve never used conditional formatting, it’s a really useful method to quickly highlight important information at a glance. You can get to the conditional formatting feature by right clicking on a value or a column, and clicking on “Conditional Formatting”.
googlespreadsheets31   Perform Amazing Feats With These Useful Google Spreadsheet Functions
There are lots of things you can do in here, between controlling text color or background color based on a range of comparisons. In this case, I want to quickly identify articles that have a high clickthrough rate, and call attention to the ones that are performing very poorly. To do this, I set up the conditional formatting to change the background to dark green when the number is greater than 4, change the background to light green when it’s between 2 and 4, and red text (bad) when it’s less than 2.
gspreadsheet   Perform Amazing Feats With These Useful Google Spreadsheet Functions
Now, at a quick glance, I can see which articles are stellar performers when it comes to getting a great ad clickthrough. Another thing you can do to make decisions based on multiple pieces of complicated data is to create an equation and place it into a logical statement.

The “IF” function lets you perform a logical comparison. In this case, I want to check whether the number of ads clicked is low, and the eCPM is high. I use the average in each column as a baseline. You can do multiple calculations like this, and “AND” multiple comparisons like this:

=IF(AND(C3<AVERAGE($C$3:$C$502);E3>AVERAGE($E$3:$E$502)),”TRUE”,”FALSE”)

This basically embeds an “AND” function inside of an “IF” function, with two “AVERAGE” functions embedded inside of the AND function. The last two parts of the “IF” function let you define what to fill into the field if the conditions are true or false. In this case, I just use “TRUE” or “FALSE”.
googlespreadsheets5   Perform Amazing Feats With These Useful Google Spreadsheet Functions

So, here’s the function in action. As you can see, when there are just a few ads clicked, and a high payout, I’m alerted to that line with a “TRUE” result.
googlespreadsheets6   Perform Amazing Feats With These Useful Google Spreadsheet Functions
Now I can try to figure out why those articles have such a phenomenal payout, and try to do more of the same to increase revenue! You can do a lot of cool stuff with embedded calculations combined with logical comparisons. But for many spreadsheet aficionados, this is really what spreadsheets are all about, right? What’s new?

Well now, let’s move on to the other cool things you can do with Google Spreadsheets.

Playing With Text

In my opinion, the power of spreadsheets comes out when you start dealing with text. If you’re accustomed to programming, then you know that you can use functions like “Left”, “Right” or “Mid” to extract sections of strings in order to manipulate them and build them into new strings.  Well, Google Spreadsheets gives you the same ability to do these kind of things with functions by the same name – although they perform a little differently.

Here’s an example. My spreadsheet includes a list of my topic URLs straight out of Google Adwords, but it’s only a section of the URL that starts with the date. If I want to create a new field that provides just the title, I need to remove the date section. To clean up strings like this, you need to find a common pattern to identify the area you want to strip out. In my case I noticed that the dates all have three slashes “/” in it.
googlespreadsheets7   Perform Amazing Feats With These Useful Google Spreadsheet Functions
All I need to do is find the third slash, and extract the rest of the text after that point. You can find the first incidence of any text inside another text using the FIND function.

=find(find text, text, position)

So, in my case, I have three levels of embedding to get to the last “/” before I can extract the right part of that long string. This is going to get funky, so follow along. To extract the strong after the first slash, you can use the RIGHT, the LEN and the FIND functions.

=Right(A3,len(A3)-(Find(“/”,A3)))

This Google Spreadsheet function returns all of the text starting from the right end of the text, all the way to the position of the first slash. This leaves you with part of the date including two slashes, and the title. To remove the sections with two more slashes, you can embed that RIGHT function into itself two more times. Here’s what such a crazy function looks like.

=Right((Right((Right(A5,len(A5)-(Find(“/”,A5)))),len((Right(A5,len(A5)-(Find(“/”,A5)))))-(Find(“/”,(Right(A5,len(A5)-(Find(“/”,A5)))))))),len((Right((Right(A5,len(A5)-(Find(“/”,A5)))),len((Right(A5,len(A5)-(Find(“/”,A5)))))-(Find(“/”,(Right(A5,len(A5)-(Find(“/”,A5)))))))))-(Find(“/”,(Right((Right(A5,len(A5)-(Find(“/”,A5)))),len((Right(A5,len(A5)-(Find(“/”,A5)))))-(Find(“/”,(Right(A5,len(A5)-(Find(“/”,A5)))))))))))

If you try to follow that function all the way through, you’re almost guaranteed to get a headache. But check it out – it works (see column I).
googlespreadsheets8   Perform Amazing Feats With These Useful Google Spreadsheet Functions
How did I create another function with the URL link to the article? Well, at least that’s an easy task.  All I had to do is use the CONCAT function to glue together two strings – “http://www.topsecretwriters/” and the title from the first column including the dates.

=CONCAT(“http://www.topsecretwriters.com”,A3)

Easy peasy.

Importing Feeds From the Internet

Another very useful function that could potentially be used for some pretty cool uses inside of Google Spreadsheets is the IMPORTFEED function. If you consider the sheer volume of information throughout the Internet that are offered by feeds, just think of what you can do with this versatile function.

How it works is pretty straightforward. Simply fill in the feed details into the function itself, surrounded by quotes.

googlespreadsheets16   Perform Amazing Feats With These Useful Google Spreadsheet Functions
It’s best to do this in the upper left cell of a fresh sheet. This will allow all of the information to cleanly import into that sheet.
googlespreadsheets17   Perform Amazing Feats With These Useful Google Spreadsheet Functions
Depending on what’s included with the feed, you could dynamically perform calculations on the information in additional columns using other fun functions you’ve discovered. Maybe the U.S. census offers a feed of demographics? Maybe you can get a stock feed with the latest data on your top stocks – and have your spreadsheet automatically perform  calculations on them.

The possibilities are unlimited – and much faster and easier than if you were trying to do the same kind of thing in Excel.

Have you dreamed up your own creative uses for Google Spreadsheet? What kind of info do you import off the Internet? What else would you like to see us do with Google Spreadsheets here at MUO? Share your thoughts and input in the comments section below!

Image Credit:Pen and Magnifying Glass Via Shutterstock

Ads by Google

7 Comments - Write a Comment

Reply

Ignacio Cual

Great article.

I would like to know if there is a “randomize” (the contents of a column or row) function in Google spreadsheets.

Thank you

Ryan Dube

Thank you. Well, there’s a RAND function, which gives you a number between 0 and 1, or a RANDBETWEEN function, which lets you define some outer limits other than 0 and 1. Is that what you’re looking for?

Ignacio Cual

Let’s say that I fill a column or row with the days of the week in order. Is there a function which will sort the column’s data (days of the week) in a random order, automatically? I use a spreadsheet to create tests in which I show two columns of data, on one all the words that We saw durign class, and on the other the corresponding definitions. I would like to use this function to make it easier to create that section of my tests, and be sure that none of the definitions are missing and/or repeated, since I originally wrote the corresponding rows. Thank you!

Reply

Gary Mundy

Thing that make you you go HMMMMMMMMmmm. And this is how I will waste my Friday ;~)

Reply

Graham Richardson

Thanks for this article – I am fascinated by the import feeds function – thats well worth a play with.
I have started to use Google spreadsheets more now and once you get used to the way it works compared with Excel it really does grow on you. I do like the pivot tables which allow me to keep a nice updated summary of the information I store.

Reply

null

Great Info! Thanks a lot…

Reply

Nevzat A

Wow, amazing!

Your comment