Master Google SHEETS FUNCTIONS

In this post, we’re going to look at how we can identify duplicate rows and also extract both unique rows and duplicate ones using a single formula. We’re going to look at different ways to do this and how we can use one formula which will extract a list of duplicate rows and and also… Continue reading How to filter duplicate rows in Google Sheets

If you’ve used functions for a little while, no doubt you will have seen or used VLOOKUP. It’s a really popular function and one of the first that most people start using but it does come with some limitations. Fortunately, we have a new function on the blocks which doesn’t have these limitations and does… Continue reading Step-by-Step Guide to Mastering the new XLOOKUP Function

Google have recently added two useful new functions to Google Sheets, CHOOSECOLS and CHOOSEROWS, which allow us to easily show certain rows or columns we want from a data range. Let’s see how they work with a couple of examples. choosecols Here, we have some questionnaire feedback on a group of teachers and the academy… Continue reading How to use CHOOSECOLS and CHOOSEROWS

The QUERY function is in a category all on its own. It’s an extremely powerful function that will let you filter, sort, group, pivot, basically extract data from a table and present it in numerous ways. At first it can look daunting, with its own language and syntax, but once you dip your toe into the… Continue reading Google Sheets Functions – QUERY

In this post, we’re going to look at how to use INDEX and MATCH to look up data and see how it can be more powerful than the commonly used VLOOKUP. In a previous post, we looked at how we can quickly look up tables for certain information, using the VLOOKUP function. This function is… Continue reading Google Sheets – INDEX and MATCH (VLOOKUP alt.)

In this post we’re going to have a quick look at rounding in Google Sheets, by using the ROUND, ROUNDUP, and ROUNDDOWN functions. The syntax is very easy, we tell the function the number we want to round and then to how many decimal places. In cell A1 we have a number and in B2… Continue reading Google Sheets Functions – ROUND, ROUNDUP, ROUNDDOWN

Following on from my post on the basic date functions, let’s look at some really useful functions that work with dates, namely: WEEKDAY, WORKDAY, NETWORKDAYS, EDATE and EOMONTH, plus we’ll see an example with the CHOOSE function. With these we’ll: Find out the day of the week of a particular date Work out a deadline date Work… Continue reading Sheets Functions – WEEKDAY, WORKDAY, NETWORKDAYS, EDATE, EOMONTH

Lots of people know about and have used Google Translate either on their phones or on the Google website but what they often don’t know is that there is a built-in function in Google Sheets, which will allow you to translate from one language to another, and even automatically recognise the language and translate it. So,… Continue reading Google Sheets Functions – GOOGLETRANSLATE, DETECTLANGUAGE

In this post, we’re going to look at some of the basic date functions and in particular, how we can extract parts of a date or a time.  We’ll cover: NOW, TODAY, DAY, MONTH, YEAR, HOUR, MINUTE, and SECOND. Example 1 – Getting the current date and time We can add the current date and… Continue reading Google Sheets Functions – NOW, TODAY, DAY, MONTH, YEAR

In this post we’ll look at how we can remove duplicates from a set of data, with one simple formula using the UNIQUE function. We’ll also use the SORT function to put the unique list in order and use this in a drop down menu using DATA VALIDATION. Finally, we’ll look at counting those entries… Continue reading Google Sheets Functions – UNIQUE, COUNTUNIQUE, SORT

In this post, we’re going to look at how we change the format of text to suit our needs, using the functions PROPER, UPPER, LOWER, and TRIM. It’s particularly useful when working with text that has come from, for example, a form, a different computer system, or indeed someone has typed in on your Sheet.… Continue reading Google Sheets Functions – PROPER, UPPER, LOWER, TRIM

In this post, we’re going to look at how we can count things and add things up depending on certain conditions that we set. There are four functions we’ll look at here: COUNTIF, SUMIF, COUNTIFS, and SUMIFS. Here I’ll show you the various aspects of the functions through some practical examples. Example 1 – COUNTIF:… Continue reading Google Sheets Functions – COUNTIF(S) & SUMIF(S)

Here’s how to look up values in tables in Google Sheets using the really useful VLOOKUP function (Vertical Look up). You give it something to look for in the table and it gets the value you’re after from a different column in that row. As always the best way to show what it does is through… Continue reading Google Sheets Functions – VLOOKUP