Google Sheets Functions – OFFSET

Sometimes we spend time setting up beautiful spreadsheets only for us to have to add rows or columns afterwards, which then messes up our formulas and we have to change them. In this post, we’re going to look at a couple of examples of the OFFSET function, which will help us create more dynamic formulas. What we mean by this is that the formula will adapt to changes made to the spreadsheets, quite often where rows and columns have been added.

Example 1 – Creating dynamic ranges to maintain an average formula

Here we have some marks for some students. (To those who have been following my posts, honestly, I’m not obsessed with exam marks, they just make good examples!)

In cell B6 I’ve added an AVERAGE function to work out the average of the marks.



But I now have another student to add who’s done the test. I add a row and insert the student’s details, but as you can see this hasn’t changed the average figure.


If we look at the formula, it hasn’t changed despite there being an extra row.


We can solve this by using the OFFSET function in the AVERAGE one.


In cell E6, I’ve added the following formula:


OK, so what’s happening? Well let’s look at the syntax of the function to understand it better.


The OFFSET function has 3 main parts:

cell reference: this is the cell you start from

offset rows: this is the number of rows you move to; positive numbers move down and negative numbers move up. In other words, a positive number increases the row number and a negative one decreases it.

offset columns: this is the number of columns you move to

There are 2 other optional parts, height and width, but here we’re not going to use them.

So going back to our formula:


The OFFSET function starts at E6 which is where our total is.

Then moves up one row to E5 as there’s a -1 in the second part.

It doesn’t move from the column, as there’s a 0 in the third part.

So, this returns the cell E5.

Now we just add the AVERAGE part. It takes the range from E2 to the result of the OFFSET function, which is E5 (E2:E5).

As we can see it returns the correct average like we saw earlier.


Now let’s add the extra student and see what happens. Ah-ha, the average has changed from 8.5 to 8.8, which is what we want.


Looking at the formula, we can see that it has changed subtly, the start cell reference is now E7 (the total) and it’s still moving one cell up, so returns the cell E6. This means the range is now E2:E6, which is what we want.


We can add or delete rows and the average will always be correct, without having to manually change it.

Example 2 – Dynamically calculating the sales for the last X months

Here we have a company’s sales from January to June. The sales manager wants to be able to find out the sales for the last X months. Here he adds the number of months he wants to look back from the last month, e.g. in cell D1 he writes 3. Then in cell D2 it tells him the total number of sales in that period, which in this case is 2,100 (700+600+800).


So, how did we do that? In cell D2 is the following formula:


Let’s break it down and start with the OFFSET function.

B2: This starts from cell B2 (the first month’s sales).

COUNT(B2:B)-D1: Then it counts how many rows (months) there are from B2 to the end of column B. Then it takes away the number of months we want to report back, in this case 3. So it offsets by 3 rows (6-3), so starts from cell B5.

0: It doesn’t move columns.

D1: The height is the figure in D1, i.e. 3 rows. So it takes figures that are from B5 to B8 (i.e. 3 rows).

1: It returns just that one column.

So, when we change the number of months in cell D1, it returns the new number of sales. In this case, the last 2 months total 1,400.


This has the added benefit, like we saw in example 1, that when more rows are added it still works. Now, the sales manager has added the sales for the month of July. As we can see, the last 2 months now add up to 1,800.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


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 out how many working days there are between two dates
  • Easily set up start and end of the month dates
  • Work out how many days there are in a month
  • Work out how many working days there are in a month

Example 1 – What day of the week was a particular date?

What day of the week was 1st January 2000 on? No, I couldn’t remember either. Let’s use the WEEKDAY function to quickly find out.

In cell A1 I’ve written the date, then in cell B1 I write the following:


This returns the day number for that date, where Sunday=1 and Saturday=7. So, we can see that 1st January 2000 was in fact a Saturday.


Personally, I find using Sunday as the first day of the week a bit confusing, but you can change which day is the first one by adapting the formula. Let’s make Monday the first day of the week. This time, after the date, add a comma then “2”. This makes Monday the first day.


As we can see in cell B2, it now returns “6”, as Saturday is now the sixth day of the week.


You can make any day the first day, just by changing the number in the formula.

Example 2 – Returning the day of the week as text not as a number

The above example’s great, but it requires you to think of what the number represents. Wouldn’t it be better to return the actual name of the day? Well, that’s easily done by adding the CHOOSE function to our WEEKDAY one.

I write the following formula:


This carries out the WEEKDAY function, finds the day number, then looks down the list of days. So, for the 1st Jan 2000, it will move along 6 spaces down the list and then choose the entry there, which of course is Saturday. Note, each entry needs to be in quotation marks.


The entries can be anything you want. For example, the other day I used this to return the days in Spanish despite using a sheet that was English-based, as we have both English and Spanish speakers using it.

A fun one to do in class with kids, is to find out what day of the week they were born.

Example 3 – Find out the date a number of days from a given date

In this example, a team have 90 working days to finish the project. I want to find out want date that is. To do so, I use the WORKDAY function:


This takes the start date in cell B1 and then adds 90 working days, and returns the end date. So, I can quickly see that they need to finish by 22nd March.


What about the Christmas holidays I hear you cry?! Well, WORKDAY can exclude a list of dates, such as holidays. In range D2 to D4, I’ve listed the Christmas and New Year holidays.


Back in our formula, I need to state where those holidays are, so I just add them after the “90”.


This time I see that the deadline’s moved out a little. Note, it’s only moved by one day, as out of 3 holidays, only one falls on a work day. In the UK, the weekend ones would in fact move to the Monday, but I just wanted to keep the example simple, and in other countries weekend public holidays don’t always move.


Example 4 – How many working days are there between two dates?

I’m looking forward to my Christmas break already and I want to know how more working days there are until I finish for Christmas. In cell B1 I put today’s date, in cell B2 the end of term date.


In cell B3, I write the following:


This takes the two dates and works out how many working days (Mon to Fri) there are. As we can see there are 26 days.

Ah, but in Spain we also have two public holidays on 6th and 8th December, I’m not working then.


We can exclude those from the total by modifying the formula:


This now works out the number of days and subtracts the number of days in range D2:D4. So, it turns out there are only 24 working days. Excellent!


Example 5 – Easily adding start of the month and end of the month dates

In the table below, I want to record how many students have had classes in each month. I need to include the start of the month and end of the month dates as I’m going to use them to filter a master list. Now, I could type in the dates, but with a long list this would be laborious. Instead I can use the EDATE and EOMONTH functions to do it for me.


In cell A2 I write the first date I want, in this case 1/10/2016. then in cell B2, I want to add the end of that month. I do this by writing the following:


This takes the date in cell A2 and then gets the date of the end of the month and as it’s the same month, I write a “0”. If I wanted the next month (i.e. 30/11), I would write “1” and so on.

On the next line I want to add the start of the next month (1/11). So, I write the following:


This takes the date in cell A2 and adds a month to it, keeping the same day of the month, i.e. 1st. In cell B2, I copy the same EOMONTH function as before, i.e. from cell B2.

Now for all future rows I can just copy this row and paste it below. So, for example, cell A6 is =EDATE(A5,1) and cell B6 is =EOMONTH(B5,0).

You can use EOMONTH to return the end of the month of future months, just by changing the 0 to a higher number. For example, in cell B2 =EOMONTH(A2, 3) would return 31/1/2017.

Example 6 – Working out the number of days in a month

In a salary sheet I need to know how many days there were in the month. Every month I type in the month in cell B1 and in cell B2 it tells me how many days are in that month, which I can then use in other formulas to calculate my teachers’ salaries.


In cell B2, I write the following:


This gets the date in cell B1 and gets the end of month date, then subtracts the start of the month (B1) then adds one so it starts with one and not zero.

As you can see it rightly, worked out that in 2016 February had 29 days.

Example 7 – Working out the number of working days in a month

In the same salary sheet, I also need to know how many working days there were in that month. This time I need to combine the NETWORKDAYS function with the EOMONTH function. I write the following in cell B3:


This gets the start date from cell B1, gets the end of the month date from the EOMONTH function and works out the number of working days in between.

So, it correctly worked out that there were 21 working days in February 2016.


There are two additional functions similar to WORKDAY and NETWORKDAYS, these are WORKDAY.INTL and NETWORKDAYS.INTL. These add the extra option of stating how many days of the week are non-working.

eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)



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, in this post we’re going to look at the functions GOOGLETRANSLATE and DETECTLANGUAGE and you’ll see how easy these are to use.

Example 1 – Translating from one language to another

I created a system where I work, where teachers can report problems in their classrooms via Google Forms on their mobiles. We’re in Seville, Spain, and some teachers can’t speak Spanish very well, so I needed a system that would allow them to report the problems in English and then it would translate it into Spanish, so that the maintenance and IT guys, who don’t speak English, can understand the problems and act on them.

To do this, I used the GOOGLETRANSLATE function. To show this, let’s look at a simple example below. In cell A2 and I have the problem reported by the teacher in English, then the translation will appear in cell B2. In cell C2 I’ve written the formula that has been used.


The GOOGLETRANSLATE function has 3 parts. First, the source text (here in cell A2), second, the source language (here English), and thirdly, the target language (here Spanish). Note, you have to use the codes “en” for English, “es” for Spanish (español). As you can see, it translated the original sentence fine.

Example 2 – Detecting a language

The above example works fine for our English teachers, but in our department we also have French, German, Portuguese, and Chinese teachers and not all of them speak good Spanish. So, how do allow them to use this in their own languages? This is where DETECTLANGUAGE comes in. This will look at the text and determine what language it is, then return the code for that language.

So, in cell A2 we have some text in English. in cell B2, we have the DETECTLANGUAGE function I’ve written in cell C2. As you can see, it’s correctly returned that the text is in English.


Example 3 – Detecting a language and translating it

Having the DETECTLANGUAGE function return the language is useful but really we then need it to combine the GOOGLETRANSLATE one to automatically change from the various languages that the problems could be written in, to Spanish.

So, in cell B2, we add the formula you can see in cell C2. Basically, we’ve replaced the source language part with the DETECTLANGUAGE function. So, it looks at the text in cell A2, then uses DETECTLANGUAGE to determine which language it is, then translates it to Spanish.


Here’s the same but with the problem reported in French. Notice that the translation is slightly different, as the “El” is missing this time. As we’ll see in the next example, Google Translate is good but at the moment it is still a long way off being perfect, as translating language is extremely complicated.


Example 4 – Automatically translating board vocabulary

Another use of this is in the classroom, where in foreign language classes it’s usual to record unknown vocabulary on the board during the lesson. I sometimes use a Google Doc and share it with the students, but here we can use a Google Sheet and it can translate the words automatically as I add them.

To do this, I have a Google Sheet with two columns. In column A I type the English words  and phrases that come up. In column B, I have a GOOGLETRANSLATE formula copied down lots of rows, so that as I type in a word, the translation appears in the cell next to it.


I use the same formula as in example 1, except that I’m going from English to Spanish, but also I wrap it up in an IFERROR function, just so that empty cells don’t produce an error message, instead they just leave the cells in B blank.


If you know some Spanish, you will notice that in fact some of the translations aren’t that good, or at least will depend on the situation. The individual words are ok, but the last two phrases aren’t what I wanted. For example, “to have a coffee” is “tomar un café” but it could be with the word “para”, if I say, “I went to the bar to have a coffee”. The last one, should translate as “hacer turismo”, as it’s come up with a translation that says “to go and see landscapes”. So, as you can see it needs to be used with care, but this can also be used as a discussion point with your students.

Example 5 – Having a conversation where neither person speaks the others language

This is a nice example where two people can have a conversation, despite not knowing each other’s language. In this case, it was two children in different countries, using a Google Sheet to communicate with each other.

In the white cells they type their conversations, one line at a time going down the page. English on the left and the other in Spanish on the right. In the yellow part are the translations. The translations are good enough for them to communicate. They could of course use this to help them learn each other’s language.


In column B, I’ve written this formula down the rows:


And in column C, I’ve written this one:


Again I’ve wrapped them up in the IFERROR function, so that it removes error messages.

In the GOOGLETRANSLATE we can also omit the source language and target language and let Sheets do it automatically. For example, in example 1 this function will look at cell A2, it will detect the language and then translate it to the language your Google account is in. This can be fine in many cases, but I prefer to be more prescriptive, just to avoid it coming up with strange results.


Also, we’ve only looked at single words and short sentences, but this works with long texts too.

Google Translate doesn’t translate all of the 6,000+ languages in the world, but it does cover the most common ones. Here’s a list of the language codes. This is continually being updated so new ones may not be on here.

Language Code Language Code
Afrikaans af Irish ga
Albanian sq Italian it
Arabic ar Japanese ja
Azerbaijani az Kannada kn
Basque eu Korean ko
Bengali bn Latin la
Belarusian be Latvian lv
Bulgarian bg Lithuanian lt
Catalan ca Macedonian mk
Chinese Simplified zh-CN Malay ms
Chinese Traditional zh-TW Maltese mt
Croatian hr Norwegian no
Czech cs Persian fa
Danish da Polish pl
Dutch nl Portuguese pt
English en Romanian ro
Esperanto eo Russian ru
Estonian et Serbian sr
Filipino tl Slovak sk
Finnish fi Slovenian sl
French fr Spanish es
Galician gl Swahili sw
Georgian ka Swedish sv
German de Tamil ta
Greek el Telugu te
Gujarati gu Thai th
Haitian Creole ht Turkish tr
Hebrew iw Ukrainian uk
Hindi hi Urdu ur
Hungarian hu Vietnamese vi
Icelandic is Welsh cy
Indonesian id Yiddish yi

In summary, it’s a great way to automatically translate within a document.

eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)

Google Sheets Functions – NOW, TODAY, DAY, MONTH, YEAR

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 time to our sheet with the very simple function, NOW.

Type the following:


This will add the current date and time in the cell.


This doesn’t update every second, but by default will update every time there’s a change made on the sheet. We can alter this by changing the recalculation settings. Go to “File” then “Spreadsheet settings”.


Under “Recalculation”, click the drop-down menu that says “On change”.


Here you will have three options. Choose the one you want, then click “Save settings”.


Example 2 – Getting today’s date and using it in calculations

Similar to above, we can get today’s date by using the TODAY function. Type the following:


This adds today’s date in the cell.


We often use this function in calculations. For example, let’s find out how many days are left until Christmas day.

In cell B1 I have Christmas day and in cell C1 I type the following:


This just subtracts Christmas day from today’s date and returns the number of days, which at the time of writing, there are 41.


We can improve the returned result by adding some text to show what it is.

We start with the same formula as before, then add an ampersand and within inverted commas, we add the text we want.


As we can see, this adds the number of days to the text. This will count down every day.


Example 3 – Extracting the day from a date

Sometimes we want to extract a particular part of the date, to find out some piece of information. As an example, here we have the number of students that signed up for courses. The courses start either on 1st of the month, or on 15th. The Marketing department want to know which is more popular, the 1st or the 15th. From the data, it’s difficult to see.


So, first we need to highlight which days are the 1st and which are the 15th. We can do this, by adding the following DAY function to column C. In cell C2 type the following, then copy it down the rows.


This has extracted just the days from the dates in column A.


Now we can add up those which are the 1st and those which are on the 15th.


To do this we use a SUMIF function (see my post on this). Write the following in cell F1:


This looks in range C2:C13 and checks to see if there are any “1s”, if there are it adds up the number of students in column B, that correspond. Similarly, we do the same for “15ths”.


We can see above that the 15th has more students.

Example 4 – Extracting the month or year / Find out how old someone is

Similar to the DAY function, we can also use the MONTH and YEAR functions to extract the month and year from a date.

In cell A1, I have a birthday. In cell B1, I’ve added the MONTH function you can see in cell C1. This returns the month as a number from 1 to 12.


We can do the same with the year. In cell B2, I’ve added the YEAR function you can see in cell C2, and this returns the year.


Let’s find out how old this person is. In cell B3, I’ve written the formula you can see in cell C3. This gets the year from today’s date and then subtracts the year in cell A1, i.e. 2016-1973. It then returns the number of years.


Similar to example 2, we can add some text to the number to make it more meaningful, by using the ampersand.


The same applies for extracting times from a date and time. We have the HOUR, MINUTE and SECOND function which will extract the different parts of a time.


As you can see, by themselves these functions are limited but when combined with other functions, they allow you to work with dates and calculate what you want.

eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)

Google Forms – Quizzes

In this post we’re going to look at the quiz option in Forms. Since its release earlier this year, I’ve been using it with various classes mainly to check their progress, and it really is a simple but highly effective tool, which I recommend everyone to use.

To start let’s just look at the 4 main areas you will need to use in order to set up your quizzes.

forms8-33 forms8-36

Settings>Quizzes – This is where you tell Forms that your form is a quiz and this then opens the quiz options. You can determine when the respondent sees their mark and whether they can see their answers corrected, the correct answers and the points allocated per question.


Settings>General – Here you can decide if the respondent will automatically receive a copy of what they’ve filled out or whether you give the option to do so in the form.


Questions – The bulk of the work is here, where you tell Forms which answers are correct and set the points per question. You can also set up automatic feedback for correct &/or incorrect answers, including links to websites or documents.


Responses – This is where the analysis happens after your respondents have filled out the quiz. You can see summary information for all the responses, including averages, range, problematic questions, and a summary per question so you can see which areas are causing the most problems. It also allows you to look at individual responses. Here a little summary of the main areas:


Setting up a quiz

Click on the Settings cog at the top of the screen.


There are 3 areas, General, Presentation, Quizzes. Click on “Quizzes”.


Then click the toggle switch “Make this a quiz”.


This will then open the options below. Under “Release mark” you have the choice of either allowing the respondent to see their mark as soon as they submit the form or you may decide to send it to them at a later date.

The latter is useful if you have questions which require your review first, for example, they are written answers, or you may decide to let everyone know their scores at the same time once everyone has filled in the form. To do this, the email collection is turned on, so that the score can be emailed to them.

The second part is controlling what the respondent can see when they see their marks. You can show them the answers they got wrong (“missed questions”), the answer key (“Correct answers”), and how many have been awarded per question.


Whilst in Settings, click on the “General” option. If you chose to release the mark later in the Quizzes menu, “Collect email address” will automatically be selected. However, even if you choose to release the mark immediately, you can manually select this open.


Underneath you have the “Response receipts” option. This allows the respondent to receive a copy of the form filled out with their answers in their email. Clicking in this opens 2 further options, the first “If respondent requests it” adds a question at the end of the form, to give the respondent the option of receiving the filled out form, and the second does it automatically, so doesn’t add a question.


Click “Save” once done.

If the “If respondent requests it” option is selected, the respondent will see this option at the bottom of the form:


They just click on the toggle switch if they want a copy.

Setting up the questions in the quiz

Now it’s time to tell Forms which questions are correct, etc. Click on the “Questions” tab. Here we can see that the email address collection has been added to the top of the form. You can’t move this.


I add a question as normal (see my post on adding questions). As you can see, at the bottom of the question, you have “Answer key”. Click on that to enter edit the answer key.


It’ll prompt you to choose a correct answer or answers. Just click on the answer that is correct.


You can also set the points value for the question. Annoyingly, this is always defaulted at 0. (Personally, most of the quizzes I’ve ever made are 1 point questions, so I think it would be better if the default was 1 point, or in the settings a global default setting could be set.)


You’ll then have your correct answer highlighted and the points value. If you select the wrong answer, click it again to remove it.


Adding feedback to your answers

You have the option to add automatic feedback to your questions. Click on “Add Answer Feedback”. You have 2 options, you can leave feedback for incorrect answers and/or correct answers.


To add feedback just type in the box, where it says “Enter feedback”.


You do the same for the correct answers, just by clicking on the “Correct answers” tab.


Not only can you leave text feedback but you can also add links. This is particularly nice, if you want to direct the student to some further reading related to the question or to some extra help.

Click on the link icon. There are two parts: “Link to” is where you paste in your link, and “Text to display” is where you add the name of the link, i.e. what the respondent sees to click on.


Here I’ve pasted in a link to a Google Doc with an explanation of this particular grammar point. The link can be to anything, YouTube video, a website, images, etc. Click “Add” then “Save”.


It shows you that this question has a link added to it.


Reviewing the summary of responses

Once you receive some responses, you will of course want to review and analyse them. Forms provides two main ways, either looking at summary of all the responses, or looking at the individual responses. Let’s look at the “summary” first of all.

Click on the “Responses” tab. Then if not already selected, click on “Summary”.


Under Insights, you’ll see the average (mean), median and range of the responses. Then underneath, a graph showing you the spread of the results. So, very quickly I can see that the majority of those who did my test, didn’t do particularly well, as I was hoping that most would get 4 or 5.


Under that Forms automatically highlights the most problematic questions, i.e. which ones respondents got wrong the most. Here I can see the last two questions caused the most problems, so immediately I can see where I need to focus my attention on in a future class.


Then you have the list of respondents, their total scores and when their scores were released. This is ordered by the release date, which I don’t find that useful, I would prefer it to sort by score. However, this is a nice, quick summary which will help you identify who needs help.

From this table, you can click on a line to go directly to that individual’s responses, again to see where in particular they need help.

Sending emails to respondents with their responses and the answer key

If the email setting was selected earlier, you can also send the students or particular students, a copy of their responses along with the correct answers and feedback, by clicking on “Release Scores”.


This opens up the Release Scores dialogue box. In here you can add a message to the respondents and then click on who you want to send the emails to. By default, all of the respondents are selected. Then click “Send Emails and Release”.


You can do this whether you selected early that they can see their result immediately or later.

Finally, you can also look at the summary of each question, to see not only which ones are causing problems but also what alternative answers the respondents are choosing. For example, in question 4, a lot of my students have chosen “a lot” instead of “a lot of”, so I can see that I need to remind of when we use one and not the other.



Reviewing individual responses

To see each individual’s response, click on “individual” near the top. It shows you each response in chronological order. It shows their email address, the response number, gives you the possibility of printing it, and the possibility of deleting it. To navigate through them, either click on the arrows or double-click on the number in the box and type in the response you want.

Below that it shows you the score for that respondent, if the score has already been released to them or not, and the option to release the score (similar to above).


Underneath it shows the questions, with whether they got them right or wrong, plus it shows the correct answers, any feedback that you set up, and any links that you added earlier. This is exactly how the respondents see it, when the score is released.




You also have the option of adding individual feedback, by clicking on “Add individual feedback” under any of the questions. This can be useful if you haven’t set up any automatic feedback or if you have questions that can’t be automatically corrected, for example, a piece of written text. Type in your feedback and if you want, you can add a link like we saw above. Then press “Save”.


How can respondents see their score?

If the “release mark immediately” option was selected earlier, when the respondent submits the forms, on the confirmation page, they will have the option “View your score”.


Clicking on that will take them to the form filled in with their answers, and depending on the options you chose, will have the answer key and feedback.

The other way I mentioned above is that they receive an email when the scores are released. This is the email they receive:


It contains their total and then when they click on “View”, it takes them to the form filled out with their answers and depending on the options you chose when setting up the quiz, the answer key, feedback and any links you added. this means that they have a permanent copy to review, which is better than only seeing it when they submit the form.


I’ve been using these quizzes since they were introduced a few months ago, and once set up they are extremely useful in quickly identifying where my students need extra help. In most cases I’ve found that as all the analysis is done within Forms I don’t need to set up a Sheet to analyse the data, which saves me a lot of work. However, with longer quizzes the initial set up can take a bit of time, especially if you’re adding feedback and links, but if you reuse this quizzes or share them with colleagues then they are definitely worthwhile.

eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)

Google Sheets Functions – UNIQUE, COUNTUNIQUE, SORT

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 by using COUNTUNIQUE.

We´ll look at:

  • Using the UNIQUE function to list unique occurrences in a list
  • Using UNIQUE with SORT to sort the unique list
  • Making a drop down menu from a list
  • Making an alphabetical drop down menu from a list
  • Using COUNTUNIQUE to count how many things you have in the list, whilst ignoring duplicates
  • Using UNIQUE to look for unique occurrences with 2 or more criteria

Let’s use a list of books as an example. Here I’ve got a list of the copies of books we have. In reality this is close to 2,000 books, but we have multiple copies of most of the books.


Example 1 – Using the UNIQUE function to list unique occurrences in a list

I want a list of what books we have without having the duplicates included in it. In a spare cell, I write the following formula:


This looks at the list of books and returns 1 instance of each one. In this example, we have 9 different books.


Example 2 – Using UNIQUE with SORT to sort the unique list

I can also sort the above list into alphabetical order by wrapping the unique function up in a SORT function. I write the following function:


The SORT function contains three parts:

range, column to sort (number), is it to be sorted in ascending order

So we put the UNIQUE function in the range part, then tell it’s column 1 (in fact there is only one column), and we put TRUE in the final part to sort it in ascending order, i.e. A to Z.

Here we have the list sorted.


Example 3 – Making a drop down menu from a list

One reason why I often do this, is that I use this list in a drop down menu. So, when I add books to the list, I don’t have to type in the full names every time, I just select the book I want from the list.

To create a drop down list, you need to add ‘Data validation’ to the cell. It’s sounds awfully technical, but really it just means it limits what you can write in the cell, and in this case, it will expect a book name from the list.

Right-click on the a spare cell, then select “Data validation” from the bottom of the list.


This takes you to the Data validation dialogue box. At some point, I’ll write a post on Data validation in more depth, but for now, the main part to use is the box to the right of where it says “List from a range”. Click the little grid in the box.


This asks you to add the range you want.


You can either type it in or just select the cells you want and the range gets added automatically. Click OK.


This has added the range of books, now click OK.


In the cell, you will now see a little inverted triangle. This is the drop down menu symbol.


Clicking on the triangle will open the drop down menu and your list of books. Note, that as I’ve used the original list of books, this has automatically made a unique list of the books, i.e. there are no duplicates. The only thing is, is that it isn’t sorted alphabetically, just in the order the books first appeared in the original list.


Example 4 – Making an alphabetical drop down menu from a list

This is where our sorted list comes in. I right-click on a spare cell and select Data validation as before. This time I enter the range where the sorted unique book list is, in this case in cells E2 to E17. Click OK.


This time, clicking on the triangle, opens the same list but this time sorted alphabetically, which is much more useful and easier to use.


Example 5 – Using COUNTUNIQUE to count how many things you have in the list, whilst ignoring duplicates

This time I want to know how many different books I have in the original list. I write the following formula:


As you can see, it’s correctly identified that there are in fact 9 different books.


Example 6 – Using UNIQUE to look for unique occurrences with 2 or more criteria

All the examples above looked at a single column. UNIQUE can in fact look across multiple columns. In our book example, I’ve just looked at the name of the book, but in fact these books come in different levels and quite often I need to know how many of a specific book and level we have, to make sure there are enough copies for all the teachers.

Here’s the list I’m going to use. It has the book title and the level of the book.


In cell D2 I write the following formula:


This is the same formula as example 2, except that the range now includes column B. This finds unique occurrences where both column A and B together are unique. Therefore, it includes 3 examples of the book “Business Result”, but each one is a different level.


If we want an alphabetical list, then we modify the formula, to add a second sort column:


This sort column 1 alphabetically A to Z, then column 2 A to Z.


One final thing I often do is, to include a range which is longer than the current range or an open-ended range. This means that if I add some new books in the original list, they will automatically be seen by the UNIQUE function, rather than have to edit the range every time a book is added. This is because UNIQUE ignores spaces.

So the formula would be something like this:


Note, that column B has no number as it’s looking at the whole of column B, so if anything is added into that column or in fact column A, it will automatically be included in the search range of the unique list.

eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)

Google Sheets Functions – ISEMAIL, ISNUMBER, ISURL, NOT

In this post we’ll look at how we can check that emails addresses are in the correct format, numbers are indeed numbers, and that website addresses (URLs) are in the correct format. We’ll start with the basic checking of these and then move on to how we can highlight these using conditional formatting.

Throughout this post, we’re going to use the information in the table below. Here we have information relating to some parents of children at the school. We have their email address, phone numbers and the website where they can access their child’s reports.

We wish to check that their email, phone number and website URL are in formats that can be used.


Example 1 – Checking email addresses

In column B, we have their email addresses (obviously fictitious ones).


To check to see if the format of these email addresses is ok, in column E we write the following function:


This checks that the contents of cell B2 is in fact an email address. If it is, it returns the word “TRUE”. If not, it returns the word “FALSE”. We then copy that formula down the rows B3 to B6.

As we can see, it correctly identified that there is a problem with the last two addresses. We can see that the fourth one is missing the @ symbol and the fifth one is missing something like .com, or at the end.


Example 2 – Checking for numbers

This time we want to check that the phone numbers in column C, are numbers and are not text or contain characters that maybe the computer system can’t handle.


To do this we write the following function:


This time it looks at cell C2 and checks to see it’s a number. If it is a number it returns “TRUE” and if not, it returns “FALSE”.

As we can see it’s found lots of problems. In cell C3, the number has a dash. In cell C4 there are spaces between the numbers. In cell C6, it’s obviously got some letters in there.


In cell C5, this number was entered as text and not a number, as it has a single apostrophe before the number to allow the number to start with a zero.


All of these situations mean that the contents of those cells aren’t numbers, at least in the way Sheets sees them.

Example 3 – Checking website addresses (URLs)

This time we want to check that the URLs in column D are in the correct format.


We write this function in cell G2:


This checks to see that the URL in cell D2 is ok.

As we can see, the first two are ok, but the last three have problems. In cell D4, the URL is missing the .com (or similar). In cell D5, the .com has 2 ‘m’s. In cell D6, the backslash is a forward slash.


Note, that the URLs don’t need the http: part nor the www. part to be recognised as genuine URLs. Sheets also automatically highlights correct URLs as hyperlinks.

Example 4 – Displaying different text depending on whether it’s TRUE or FALSE

In the examples above, our functions were displaying either TRUE or FALSE, which fine for a quick check, but don’t really look that good on your sheet. In the next few example we’ll look at how we can improve that feedback.

Firstly, we can change the wording from TRUE and FALSE to anything we would like. For example, let’s report ‘OK’ if it comes back TRUE if it’s an email, and ‘Not OK’ if it’s not.


In column E, we write the following formula:


This wraps the ISEMAIL function in an IF function. It looks at the content of cell B2 and if it’s true, it displays ‘OK’ and if it’s false it displays ‘Not OK’. See my post on the IF function if this is new to you.

Then we do the same for the other functions:



Now in columns E to G, we can see which ones are OK and which ones aren’t. This makes it a little clearer to anyone looking at your sheet.


Example 5 – Adding conditional formatting

Now let’s add some colour to show which are OK and not OK more clearly. Let’s put the OKs in green and the Not OKs in red.


Select the cells you want to colour.


Right click and select Conditional formatting.


This opens the Conditional format rules sidebar. Click on where it says “Cell is not empty”.


Then select “Is equal to” from the options.


In the box type “OK”.


Then select the colour you want by clicking on the fill icon.


As we can see, the first rule is now set up and it’ll fill any cell in with green that is equal to OK.


Click on “Add new rule”. Then repeat the process above, this time selecting red and in the box typing in Not OK.



This is far more visual and effective, especially if you have a long list.


If you want more information on conditional formatting, see this post.

Example 6 – Using custom formula to add colour to cells with the data in it

So far we’ve reported what’s OK and not OK in different cells, but quite often the better way is to highlight the cells themselves that are OK or not OK. For example, first let’s highlight the emails that are OK in green.


Select the emails and right-click and select Conditional formatting as before.


This time, at the bottom of the options, select “Custom formula is”.


In the box, type the ISEMAIL function referring to the first cell in your selection. In this case, cell B2.


Note that this automatically, applies your formula to the whole range you selected.


As we can see it highlights those emails that are indeed emails, i.e. which from the formula return as true.


Example 7 – Using NOT in a custom formula to highlight what isn’t true

Highlighting which emails are correct is fine, but I usually find that you normally want to know where there are any problem ones, as one assumes that the majority will be ok. As you can see in the picture below, we can clearly see which cells need attention.


The process is exactly the same as the previous example, except we’re going to change the formula slightly. Start off with the emails.


Having selected “Custom formula is”, type the following formula in the box:


This wraps the ISEMAIL function in a NOT function. Effectively, what it’s doing is saying if the cell content ISN’T an email then apply the formatting. We then add a red fill.


As we can see it’s highlighted the problematic cells in red.


We can then do the same for the numbers and URLs, selecting each range at a time and entering the following formulas.



This is much clearer and without the need of extra columns.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)