Amazing things you can do with Google Sheets

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 leastthat’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.