Occasionally you need to convert a specific string to remove some x number of characters at the beginning of the string. There is a very nifty function you can use for this: … continue reading »
Sometimes you want to have the week number of a certain date in your spreadsheet. In Excel you can use the
WEEKNUM() function for this purpose, however in Google Spreadsheets there is no such function built in.
If you want to get a function that does the same as
WEEKNUM() in Google Spreadsheets, use the following trick … continue reading »
Suppose you have a number between 1 and 26 in cell
A1, you can get the corresponding letter in the alphabet using the following trick:
Of course, you can extend and modify this with your own letters to suit your particular needs.
Sometimes you want to remove some number of last letters from a string in your spreadsheet. For this you can use a very handy function … continue reading »
Sometimes you want to make a reference to certain worksheets dynamically using the Excel indirect function. For example if you have data in the same format split over multiple worksheets and you want to select data from different sheets dynamically.
In this case, you can use the
INDIRECT() function, which is available in both Excel and Google Spreadsheets. I’ll show you how to use it by running through … continue reading »
Almost everybody that owns a website uses Google Analytics to analyze their visitor’s behaviour. While the Google Analytics web interface is useful, sometimes you just need a little more flexibility with your data by exporting it from Google Analytics into your spreadsheets.
In this post I will show you how you can set up a spreadsheet that allows you to import Google Analytics data with the push of a button and make custom graphs about your visitors like … continue reading »
If you’re used to working in Excel, you’re probably using the SUMIFS, COUNTIFS and AVERAGEIFS functions all the time. When switching to Google Spreadsheet I ran into the problem that I couldn’t use those functions as Google didn’t include them. They only offer the single criterion SUMIF and COUNTIF and don’t support multiple criterion functions. Luckily, they do offer the FILTER function, which we can use to solve this problem. In this post I’ll show you how you can increase your Google Spreadsheet productivity by replicating the behavior of aforementioned … continue reading »
Today I’m going to show how to make an nice map visualization of the Facebook Penetration numbers per country using Google Spreadsheets.
For this I am going to explain how to use the ImportHTML function of Google Docs and show you a quick, but very useful, way to create beautiful maps.
Don’t worry, we only need to perform 4 simple steps to get a nicely looking, informative result like shown … continue reading »