# Perform Amazing Feats With These Useful Google Spreadsheet Functions

Many 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 How You Can Make Your Own Simple App With VBA Want to know how to make your own VBA application to solve problems? Use these tips to build your own VBA software. Read More with a few tips and tricks to do funky things in Excel, like automatically sending emails from inside of Excel How to Send Emails From an Excel Spreadsheet Using VBA Scripts Our code template will help you set up automated emails from within Excel using Collaboration Data Objects (CDO) and VBA scripts. Read More , exporting tasks from Outlook to Excel How To Export Your Outlook Tasks To Excel With VBA Whether or not you are a fan of Microsoft, one good thing that can be said about MS Office products, at least, is how easy it is to integrate each of them with one another.... Read More , or otherwise using Excel to manage your life How To Use Microsoft Excel To Manage Your Life It's no secret that I'm a total Excel fanboy. Much of that comes from the fact that I enjoy writing VBA code, and Excel combined with VBA scripts open up a whole world of possibilities.... Read More .

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.

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.

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.

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

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.

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

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.

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.

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

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.

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.

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

Explore more about: Google, Google Docs, Microsoft Excel, Spreadsheet.

> Here’s what such a crazy function looks like.

...Or use the SPLIT() function.

how about removing items from a + to the @ in gmail addresses

like if i have an email address that is name+test1@gmail.com - a formula to turn that into name@gmail.com

I guess it would also need an if statement to ignore if there is no + so it does not run

Assume your email is in cell A1.

=SUBSTITUTE(A1,"+test1","") or

=LEFT(A1,FIND("+",A1)-1)&RIGHT(A1,FIND("@",A1)-1)

to add an if statement for the + symbol you could use the second of the two options above, with an IF statement like this:

=IF(ISERROR(FIND("+",A1)),A1,LEFT(A1,FIND("+",A1)-1)&RIGHT(A1,FIND("@",A1)-1))

Assume your email is in cell A1.

=SUBSTITUTE(A1,"+test1","") or

=LEFT(A1,FIND("+",A1)-1)&RIGHT(A1,FIND("@",A1)-1)

to add an if statement for the + symbol, use the second of the two options above with an IF statement like:

=IF(ISERROR(FIND("+",A1)),A1,LEFT(A1,FIND("+",A1)-1)&RIGHT(A1,FIND("@",A1)-1))

Cool info! Thanks for sharing.

Wow, amazing!

Great Info! Thanks a lot...

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.

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

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

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?

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!