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 »
How to get WeekNumber function in Google Spreadsheets
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 »
How to get letter in alphabet based on number in Excel and Google Spreadsheet
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:
=mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ";A1;1)
Of course, you can extend and modify this with your own letters to suit your particular needs.
Removing the last number of characters from a string in Excel and Google Spreadsheets
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 »
Use Excel INDIRECT to dynamically refer to worksheets
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 »
How to import Google Analytics data into Google Spreadsheets in 3 simple steps
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 »
Making SUMIFS, COUNTIFS, & AVERAGEIFS functions in Google Spreadsheet
Update Dec 2013: In the new version of Google Spreadsheets SUMIFS, COUNTIFS, and AVERAGEIFS are already built in. If you didn’t activate the new version yet: read on below!
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 »
Using ImportHTML to scrape Facebook Usage numbers
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 »