Google Sheets Functions – QUERY

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 QUERY pool you’ll realise that things are not so complicated and that with just one function, you can extract and analyse your data with ease.

As always the best way to learn how to use it is through examples, and in this post we’re going to use two main sources of data, some questionnaire feedback, and some data from a HR department, building the complexity up step by step.

Analysing questionnaire feedback

Here we’ve used a Google Form to collect feedback on the teachers, the classrooms, and admin information at the end of every course in an academy. Each row is a student’s piece of feedback. They grade the various criteria from 1 to 5, 5 being ‘excellent’. Below is a snippet of that table of data.


In the next few examples, we’ll see how easy it is to analyse this data, each time with just one QUERY function.

Example 1a – Selecting the relevant data from the master data

The head of studies wants to look at the feedback for her teachers, and she doesn’t need to know the classroom feedback or the admin feedback. So, the info she needs is from column A to H, as shown below:


In cell A1 on a different page, I’ve written the following QUERY function:


There are 2 main parts to a QUERY function, 1) the data range, 2) the query

So, in the first part (in orange) we look at the page called “Questionnaire” and range A1 to column N (note this is an open-ended range as we will receive more entries in the future).

In the second part we tell the function what to select. So, in this example, we want columns A to H. We add the column letters followed by commas. The query part always needs to be within speech marks, so we put it before select and at the end before the bracket.

“select” is one of the keywords within the QUERY language which tells the function what to do. Here are some of the other ones, most of which we will see in the following examples.


Example 2a – Filter by a teacher’s name

Now the head has decided that he wants to look at the feedback of a particular teacher. She wants the following information:


Here’s the QUERY formula I entered in cell A2:


It’s the same as before except that at the end I’ve stated a condition:


This looks at column C and returns anything that matches “Fred”. I.e. it only returns the feedback for Fred.

Example 2b – Filter by a teacher’s name using a cell reference

The head has decided that she doesn’t want to have to change the teacher’s name within the formula every time she wants to look at the feedback of a different teacher, she wants to enter the teacher’s name in cell B1 and wants the formula to update accordingly.

That’s no problem, although the syntax looks a little ugly. Here’s the formula:


At the end, instead of the name “Fred” I’ve put the reference to the cell. In QUERY function you have to use this syntax: ‘”&B1&”‘ basically so it knows it’s a cell reference.


As you can see it produces the same information as before and now if the head wants to see another teacher’s feedback she only needs to change the name in cell B1.


Example 2c – Filter by a teacher’s name and sort the date in descending order

By default, the data is sorted from the oldest date to the most recent, but when there is a lot of data this means that to see the most recent and probably most relevant data, the person has to scroll down. We can remedy that easily by sorted the data by date in descending order, as we can see below:


To achieve this, I’ve used the same formula as before except at the end I’ve added an ORDER BY part:



This orders (or sorts) column A (the dates) and the ‘desc’ tells it to do it in descending order. If you want to tell it to do it in ascending order, write asc.

Carefully note the syntax, as one tiny error will stop this from working.

Also note that, the QUERY results aren’t formatted and the column widths aren’t automatically adjusted. This needs to be done manually either beforehand, or afterwards.

Example 3a – Filter the data between 2 dates

The head also wants to be able to filter the data for a particular period of time, e.g. september 16. As you can see below, the data has been filtered between 1/9/2016 and 30/9/2016.


Here’s the formula I’ve added in cell A2:



The new part is at the end. First, we tell it to look in column A for a date bigger than or equal to the date in inverted commas. Make sure you add the word date, to tell the function that you’re looking for a date and not some text.


Then in the next part, we add ‘and’ to tell it to look for 2 criteria. Then tell it to look for a date less than or equal to the date in inverted commas.

Note that with dates you need to write the date in the following format:


Even if the date format in your sheet is different as it is in my one.

So to summarise, it gets columns A to H, and returns rows that meet the 2 criteria, i.e. 1/9/2016 to 30/9/2016.

Example 3b – Filter between 2 dates using cell references

As we saw earlier we can replace the actual dates in the formula with cell references.


The only thing is that to do this we need quite a complex looking formula. Here’s the formula I’ve added in cell A2:


Here’s the formula broken down into it’s component parts:




To replace the actual date we need to use the following formula after the word ‘date’:


This gets the date in cell D1, puts it into the correct format. The same goes for the second date:


If anyone reading this knows of simplier syntax to do the same thing, I’d love to hear it, as I couldn’t find on-line a better way to do this, but I’m open all ears!

Example 3c – Filter between 2 dates and by teacher

Finally, the head wants to filter the feedback between the two dates, by a specific teacher, and order the feedback by date in descending order, as you can see below:


As you can see the formula is getting pretty long, but you can also see that it’s made up of parts and you can extract what you want by adding extra parts.


Here’s the query part broken down:


It gets columns A to H.


Finds rows where the date is greater than or equal to the one in cell D1.


And that also is less than or equal to the one in cell F1.


AND where the name in column C is the same as the name in cell B1.


Then order the results by the dates in column A in descending order.

Example 4 – Filter against various criteria

Here the admin manager wants to use the questionnaire feedback to see how good the information is that is given to the students when they sign up and how well the service was in the office. He particularly wants to know if there was any low feedback in any of the areas under his control, so wants to know if the course info, payment info, or office service was rated less than 3 by anyone.


To create the table above I’ve added the following formula in cell A1:


Here I’ve selected 5 columns and notice that I’ve put column B at the end. This shows that when selecting the columns, you don’t have to have them in the same order as the original data. This is extremely useful at times.

In the second part, I set the criteria, i.e. he’s looking for values which are less than 3 in each of the columns, L, M, and N. To include 3 different criteria, I’ve used the ‘or’ keyword, so that it will return rows if any one of them have a value of less than 3 in it.

As we can see in the table above, it’s found 3 results where the course information was rated poorly, and we can see from the level that it was related to level B1, so clearly some work is need there. Plus, there is one incident where the service in the office was rated poorly, which may need investigating.

Example 5a – Returning the averages of data

The director of the school wants to know if there are any classrooms that are rated more poorly than others. He wants to see if the average rating is different for any of the classrooms. In the table below, we can see that clearly, there is a problem with class A1, as it is rated poorly and much lower than the others.


To do this, I’ve added the following formula in cell A1:


This time I’m interested in the classrooms in column I and the average of the scores given in column J. First, I select column I, then select the average of column J, then I group them by classroom, in other words by column I.

We can return the average, count the number of entries, return a maximum or minimum in that column, or sum up the entries, using the following syntax:


Quite often these work with the ‘group by’ keyword, to be able to return the results.

Note, there is an empty row in row 2, as the data range is looking below the original data and into empty rows, and it will return one. This can be eliminated by stating the exact range of your data as we will see next, but the downside is that if more data is added the range will have to be updated.

Example 5b – Returning the averages of data and ordering them

Following on from the example above, we can adjust our returned information by sorting the feedback by the lowest to the highest, i.e. in ascending order. Here we can see class A1 is the lowest rated.


Here’s the formula I wrote in cell A1:


The first part is as before, then it’s followed by:


This orders the results by the average of column J (in column B), in ascending order.

Example 6 – Pivot information using QUERY not pivot tables

To finish off this first part, let’s look how we can pivot the information to see the averages of 2 criteria for each teacher. We want to look at the “is clear” and “is organised” categories. If you’re familiar with pivot tables, this works in a similar way, but with the bonus of doing everything right within the QUERY function.

Here the data has extracted the information below. It looks like Fred’s class organisation may need improving a little.


To get this output, I’ve written the following formula in cell A1:


This selects the average of column D (“is clear”) and the average of column E (“is organised”) as the criteria, then pivots it by teacher (column C), so that we see an average of each criteria for each teacher.

We could look at every criteria per teacher, just by adding the average for each criteria column, e.g. avg(F), avg(G), etc.

Analysing a HR database

OK let’s look at a different set of data now. Here we have employee database with some information about them.


Example 7 – Returning average salaries per department

The HR director wants to know what the average salary is per department from the data above. Here’s the end result:


In cell A1, I’ve written the following formula:


This selects the departments (column B), and the average of the salaries (column C) grouped by department.

Example 8a – Listing salaries per employee in descending order

Here he wants to see the salaries per person in descending order, without any of the other information.


In cell A1, I’ve written the following formula:


This selects columns A and C, and sorts column C in descending order.

Example 8b – Limiting the number of results

The HR director actually only wants to see the 5 highest salaries. We can use the formula and add a limit to it, to show the following:


Here’s the formula:


In the last part I’ve added ‘limit 5’. This returns the first 5 rows.

Example 9 – Ordering by more than 1 criteria

Let’s now look at how we can order our results by 2 or more criteria. Here the HR guy wants to see the employee names, their departments and salaries. He wants the data organised by department then by salary, with the salaries going from highest to lowest.


To do this, I’ve written the following formula:


This selects columns A, B, and C (employee, department, and salary), orders first by department (B), then by salary (C). Note the syntax, after ‘order by’ you just add the first column letter, then the second one after a comma.

Example 10 – Relabelling column headers

Finally, let’s look at how we can rename the column headers to something different from the original data. This can be useful, if the original data is from a computer output and the column headers aren’t in everyday English, or you may simply want to change them.

Here I’ve changed the column “Employee” to “Name” using the QUERY function.


To do this, yes you’ve guessed it, I’ve added the following formula in cell A1:


The new part is at the end, (label A ‘Name’). This tells it to rename column A with the word ‘Name’. To add more labels, just add a comma and the column letter and new name.

A couple of final comments about QUERY. Be careful where you place your QUERY function, as you need to make sure that there is nothing in the cells particularly below it, as otherwise it’ll throw an error.

The syntax is very exact, so make sure you notice in the examples, how the punctuation is used.

Despite this being a long post, I’ve only scratched the surface as to what QUERY is capable of. To learn more go to Google’s page on the query language:

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

Baz Roberts (Flipboard / Twitter / Google+)

Google Sheets Functions – IMAGE

In this post let’s look how we can insert images into our sheets. There are two main ways, either inserting the image via the Insert menu or by using the IMAGE function.

Example 1 – Inserting an image from Drive

Here let’s add an image from my Drive. Open the “Insert” menu then click “Image”.


Choose where your image is, in this case, let’s choose “Google Drive”.


Search for your image, and click on the one you want, then click Select.


This will place the image on top of your sheet and won’t affect the cells in any way. You can change the size of it by moving the blue squares on the border of your image.


Example 2 – Inserting an image within a cell using the IMAGE function

An alternative way to inserting images, is to use the IMAGE function, which will insert the image within the cell where that function is. To do this we need the URL of the image.

In the cell I type =IMAGE() then in between the brackets I add speech marks and the URL inside them, i.e. =IMAGE(“”)

Here I’ll add an image from my blog:


This adds the image within the cell. A bit small isn’t it?


This is because it has adjusted the size of the image to the size of cell. To make it bigger we just make the row and/or column sizes bigger.


By default, the image is inserted in “sizing mode 1”. So, what does that mean? Well, there are 4 modes and they treat the images in different ways.

Mode 1 – Resizes the image to fit inside the cell, maintaining aspect ratio.

Mode 2 – Stretches or compresses the image to fit inside the cell, ignoring aspect ratio.

Mode 3 – Leaves the image at original size, which may cause cropping.

Mode 4 – Allows the specification of a custom size.

So, let’s look at the modes 2, 3, and 4 in turn.

Mode 2

Taking the same image and formula, but this time just adding a comma and 2 at the end, will squash the image into the cell, and ignore the original aspect ratio, so it now looks too wide.



Mode 3

This time replacing the 2 with a 3, will insert the image as its original size, but if it is bigger than the cell, it will be cropped, as we can see below.



Mode 4

Finally, we can control the height and width we want, but to do this we must use mode 4.

As you can see in the formula, after the 4, we add the height (150) and then the width (120).



Which mode you use is of course entirely dependent on what you want to achieve.

It’s important to note that as these images are within the cells, they are affected by any cell changes, rows or columns added, etc.

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

Baz Roberts (Flipboard / Twitter / Google+)

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+)


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 more 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+)

Google Sheets Functions – TRANSPOSE

Here we’re going to look at how we can use the TRANSPOSE function to change our data from being vertical to being horizontal, or vice versa.

Changing a single column or row of data

Here we have the days of the week in a column and we want to put them horizontally, so that each day is in a different column across the page.


In cell C1, I write the following function:


As you can see, it changes the information from being vertically stacked to being horizontally laid out.


This works both ways. If the original data was horizontally laid out, then in cell A1, we could write this function:


This would display the days vertically.

Converting 2 vertical columns to 2 horizontal ones

Here we have the days of the week and some teachers. I want to lay both sets of information horizontally.


This is easy, just include both columns in the range in the TRANSPOSE function.


This will then take column A and put it in row 1, then take column B and put it in row 2.


Converting multiple horizontal rows into vertical columns

Similar to the previous example, we can do the same for more than 2 sets of data.


Include the complete range in the TRANSPOSE function.


And sure enough, it transposes the information from rows to columns.


The above is using the TRANSPOSE function, to do it automatically for you, but remember you can also copy your data and using paste transpose, you can transpose it manually. Just right click, select Paste special, then Paste transpose. See my post on moving and pasting data for more examples.


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

Baz Roberts (Flipboard / Twitter / Google+)

Google Sheets Functions – PROPER, UPPER, LOWER, TRIM

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 when someone has typed in on your Sheet. This is because the capitalisation isn’t the way we want it and the text may contain unwanted spaces, which can cause problems on your Sheet. We’ll cover the following areas:

  • Using the PROPER function to capitalize each word
  • Using the UPPER function to capitalize all letters
  • Using the LOWER function to put words into lowercase
  • Using the PROPER and TRIM functions to clean up text
  • Using ARRAYFORMULA to copy PROPER function to all rows
  • Capitalizing only the first letter of a sentence and putting the rest in lowercase
  • Changing a name to initials

Example 1 – Using the PROPER function to capitalize each word

The PROPER function capitalizes every word in the text, which is useful for correcting the format of names. Here we’ve got my name in various formats.


As we can see the syntax of the PROPER function and indeed the UPPER, LOWER and TRIM functions is very simple, just add the text or cell reference in brackets.


So I write the following in cell A2.


Then I copy that down in cells B2 and B3, and as we can see it’s corrected the format of the name.


Example 2 – Using the UPPER function to capitalize all letters

Here we have a part number which should be in UPPERCASE.


Similar to PROPER you just put the text or cell reference in brackets.


And it changes all the letters to uppercase.


Example 3 – Using the LOWER function to put words into lowercase

Here I want to change my messy name to lowercase.


I just write the following function:



Example 4 – Using the PROPER and TRIM functions to clean up text

One problem with receiving text input from forms, computer systems, etc is that text input can be in different formats and also can have unwanted spaces, which can mess up your formulas and how you use the data.

Here is an example where my name has been filled out in a form, but the users have entered it in different ways.


We can use the PROPER function to tidy up the format of the text, as we saw above, and we can use the TRIM function to get rid of those unwanted spaces, whether they are before the text, in the middle of the text, or afterwards.

So, we wrap the TRIM function up in the PROPER one:


As you can see, it tidies it up well, making it easy to use the data afterwards, or even just to make it look better.


Example 5 – Using ARRAYFORMULA to copy PROPER function to all rows

In the example 1, I copied the PROPER function down into each row. There’s a quicker and better way. Here I have some students’ names and I want to tidy up the format.


In cell B1, I write the following formula:


This looks at everything in column A and places the corrected format in column B, without having to copy down the formula into rows 2 and below.


Note, that when using the PROPER function, names which contain a capital letter in the middle of the word, such as, McCarthy, aren’t displayed correctly, in that the second c is also in lowercase, e.g. Mccarthy.

Example 6 – Capitalizing only the first letter of a sentence and putting the rest in lowercase

The PROPER function works well with names, but what about normal sentences? We usually don’t want every word to start with a capital letter.

Here I have a sentence all in uppercase and as it seems that the person is ‘shouting’, I want to correct it automatically and add a capital letter at the start.


This sounds like a simple thing to do, but the formula involved is quite long. However, the way it works is quite simple.


Let’s go through it from left to right. Firstly, it puts the first character to the left in uppercase, i.e. the first letter of the sentence. Then it puts the rest of the sentence in lowercase, by finding out the length of the text and ignoring the 1 character at the start of the sentence.

Here’s the result:


Here’s the formula in case you want to copy and paste it into your sheet:


To adapt it to your needs, just edit the cell reference A1.

Bonus Example – Changing a name to initials

In Spain, names can be quite long as people can have two first names and also two surnames. A common practice in business, is to refer to people in emails and documents by their initials. We can get sheets to display the initials by looking at the full name and returned the first letter of each name.


This example is not strictly to do with the functions in this post, but is an example of how other functions can be used to manipulate names. The formula is particularly long, but in fact, from the second MID function, it just the same formula repeated to analyse each word in sequence.


As we can see it takes the four word name and returns the person’s initials.


If the initial name was in lower case, the initials would also be in lowercase.

With the formula split into 4 component parts, you can see that after the initial LEFT function, there are 3 parts that are almost identical. The only difference is that in the SUBSTITUTE formula, the number changes from 1 to 3.


&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,“#”,1))+1,1)

&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,“#”,2))+1,1)

&MID(A1,FIND(“#”,SUBSTITUTE(A1&” “,” “,“#”,3))+1,1)

To adapt it to your needs, just edit the cell reference A1. If you only have 3 names, you can omit the last part. If you have more than 4 names, just add another &MID part, and make sure the number on the SUBSTITUTE brackets goes up by 1 (for example to 4).

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

Baz Roberts (Flipboard / Twitter / Google+)

Google Sheets Functions – COUNTIF & SUMIF

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: Counting the number of instances of a specific number

In this example, and to introduce COUNTIF, we have a list of part numbers of some products and we want to know how many parts we have that are numbered “123”.


In cell B2, we write the following formula:


There are two parts, first you state the range of values you want to look at, then in the second part, you state what you want to look for. So, in this case, we’re looking in cells A2 to A16 and we are looking for the number 123. As it looks down the row, every time it finds the number 123, it adds it to the count. So, in this example, it found 6 instances of the number 123.


Example 2 – COUNTIF: Counting how many values are over a certain number

We can also use COUNTIF to count how many values are greater than or less than a particular number. In fact, it can check against pretty much any type of mathematical condition, an example of which we’ll see later.

Here we want to see how many students got over 60 in a test, maybe to divide the group into a stronger one and a weaker one.


In cell C2, as before we start off with the COUNTIF function, set the range we want to look at, in this case it’s B2 to B9, then set the condition to look for any tests over 60. Notice the syntax of the condition. When we use the operators, like greater than or less than, we have to wrap them up in speech marks.


And correctly, it’s found 5 students with a test result of more than 60.

Example 3 – COUNTIF: Counting the number of times a word or phrase appears in a range

This time we’re going to look for a piece of text. We have a book inventory and we want to know how many books called “English File· we have.


In cell C2, we write the following formula:


Like the operators above, text needs to be in speech marks. Here it’s found 6 books called English File.

Example 4 – COUNTIF: Using a cell reference to find instances of whatever has been written in that cell

So far, we’ve stated the condition within the formula but often we want a flexible formula that can look for any condition we type in a particular cell.

Here we have the same book inventory but this time we give the user the option to type in the book they are looking for in cell C1. Then in cell C2 it will tell them how many books we have of that name.


In the condition part, the important part and the part where most problems occur, is the syntax here. First, we wrap the equals operator up in speech marks like we saw in example 2. Then to refer to a cell we need to first add the ampersand (&) then the cell reference.

Whatever we type in cell C1, the COUNTIF will look for in the range, making this far more flexible than stating it within the formula.


For those who have read my post on CONCATENATE, this is one reason why I use CONCATENATE rather than the ampersand to join cells together, as the ampersand has different uses and can be a bit confusing if you have more complicated formulas with the ampersands doing different jobs.

Example 5 – COUNTIF: Using wildcards to look for broader values

Here we have a list of classes and we want to know how many junior classes we have. The problem is that, there are different levels of Juniors, 1, 2, and 3, so our formulas so far wouldn’t be able to find all these different ones. That’s where a wildcard comes in.


In cell B2, we type the following formula:


The key part here, is the use of the asterisk (*) after the word Junior. This allows it to look for anything that starts with the word Junior and it doesn’t matter what comes afterwards. Hence it will count the number of times there is a class starting with the word Junior. In this case there are 8.

The wildcard can also be used before the word, for example, you could look for all the classes in level 1, by typing “*1”, which would look for anything ending in 1.

Example 6 – COUNTIF: Counting dates

The final example of COUNTIF, shows us that we can also count the number of times a date appears. For example, here we want to know how many appointments we have on 17/10/2016.


Dates like text, need to be in speech marks. It’s also important to match the format. I usually state the day, month and year, even if the dates in the table are formatted in a different way. I.e. if the data just showed the day and month, the formula would still work.


Example 7 – SUMIF: Adding up the number of products sold on a particular date

Now let’s look at SUMIF. This is similar to COUNTIF but the syntax can be slightly different as quite often we’re not looking at just one range.

Here we have some products that have been sold on different days and we have how many of each product has been sold. What we want to know is how many products did we sell on a particular date, e.g. 17/10. In cell E1, the user types in the date they want to find the information for, then in cell E2, it will tell us how many have been sold.


In the brackets, we first type the range where we’re looking for the date, e.g. A2 to A12. Then we add the criteria, in this case whatever’s in cell E1 (the date). Then for SUMIF, we add a third part, this is the range which contains the values we going to sum, e.g. quantities sold (‘sum range’).



So in our example, it’s found 3 dates that equal the 17/10 and summed up the quantities sold on those dates, i.e. 100+300+600 = 1,000.

Example 8 – SUMIFS: To find the quantity sold within a date range

Similar to SUMIF, we have the function SUMIFS, which uses more than one criteria before adding up. Here we have the same information as before, but this time we want to find out how many products we’ve sold between two dates (17/10 to 19/10).


Important: The syntax here is different from SUMIF, and let’s go through it step by step.



We start with the range we want to get the values from, i.e. the sum range (C2:C12).

Then we state the range we want to find the dates in, i.e. A2 to A12.

Then the first criteria we want to use, i.e. the date is greater than or equal to the date in cell E1, in this case, 17/10/2016 (“>=”&E1). As before the operator is in speech marks, and the cell reference has an ampersand before it.

Then we add the second condition. In this example, the range is the same, i.e. A2:A12.

Then we want to look for dates less than or equal to the date in cell E2, in this case, 19/10/2016 (“<=”&E2).

It then adds up the products sold between those dates, 100+300+150+200+600+200+300+450+100 = 2,400.

It looks a fairly complicated formula but it really just has three parts:

sum range, criteria 1 (range & criteria), criteria 2 (range & criteria)

We can add more criteria if we want just by adding another range and criteria on the end.

Example 9 – COUNTIF: Counting how many students are above average

Returning back to COUNTIF, we can use other functions as the criteria within the COUNTIF one.

Here we want to see how many students got over the average mark in an exam. Normally, we would have to find the average of all the marks, then see which marks were above that figure. Well, that’s exactly what the COUNTIF function can do for you.


In the second part, we want to know what marks are greater than the average of all the marks in that range. So, we start off with the greater than operator in speech marks, then like the cell referencing we need to add an ampersand before the function AVERAGE. Then we add the range we want to average.


The average was 69%, and so correctly it’s found that 4 students got an above average mark.

Example 10 – COUNTIFS: Highlighting duplicate rows

In this final example, we can use the COUNTIFS function in conjunction with conditional formatting, to highlight rows on our sheet which have duplicate information.

Here we have a list of classes, their timetables and teachers. Some of the rows are duplicates and we can use COUNTIFS to find the duplicates. We add a fourth column to record if the row is duplicate or not.


In column D we add the following formula:


It works by looking in the 3 columns A, B, and C and sees if there is more than 1 occurrence of the contents of that row. So taking the first row (row 2) as an example:

It checks to see if the content of cell A2, B2, and C2 occurs more than once in the rows below, which it does, i.e. in row 6. So, it states that it’s TRUE that it occurs more than once, in other words, it’s duplicated.

If we look at row 3, we can see that this time that the combination of those three pieces of information are not repeated in the other rows. So, it states that it’s FALSE that it occurs more than once.

We could leave it like that and if we wanted to remove the duplicates or edit them, we just look for those rows which have TRUE in them. However, to make it easier to find them, we can colour the rows to highlight them.

Having selected the contents of the table (range A2:A13), right-click and select “Conditional Formatting…” from the menu.


This opens the Conditional format rules sidebar menu. Click on the drop down menu, which by default says “Cell is not empty”.


Then scroll down to the bottom of the menu and select “Custom formula is”.


Here type in the formula below:



This checks to see if the value in column D is TRUE. If it is, it adds the formatting to the whole line. If you want to know who this works better, read my post on Conditional Formatting.


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

Baz Roberts (Flipboard / Twitter / Google+)

Google Sheets – Alternating colours in a table

When working with tables in a spreadsheet, we often want them to be easy to read and understand. To help us Sheets now has a quick little tool, which allows us to add alternating colours to the rows of our data, making them not just prettier but much easier to read, whilst being really quick to create.

Here we have a plain table of data.


First, highlight the area you want to format (usually the whole table).


Go to the Format menu, then click “Alternating colours”.


This will open the sidebar. Here from top to bottom you have:

The range the formatting will apply to.

Whether you want a different colour for the header of the data or the footer.

A set of default colours to choose from.

The option to create your own default colour combination, which is then saved for future use.

At the bottom, you have the option to remove the formatting.


When you first open the sidebar, by default it adds a grey and white format to your data.


To change this, just select one of the default options.


Here I’ve selected a purple combination. As you can see, it’s easier to read and looks better too.


If you want to choose, your own colours, either select a default one and edit it, or click the plus button to add a new one. Then click on the fill symbols to the right, to change the colours.


By default, this adds a darker header row at the top. You can choose whether you want to have this and also whether you want the last row, the footer, to be a different colour, for example, usual for totals. Just tick the ones you want.


Here I’ve added both a header and a footer.


Once you’ve selected the options, click Done to confirm the changes.


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

Baz Roberts (Flipboard / Twitter / Google+)

Google Sheets Functions – OR & AND

With the IF function we usually check if one condition is true or false, but what happens if we want to check multiple conditions? This is where the OR or AND functions come in handy. With OR we can check if one or other conditions are true, and with AND we can check if all the conditions are true.

We’ll start with how the function works then look at some practical examples of both.

Example 1 – How does the OR function work? And how does it work with IF?

Let’s first look at how the OR function works. Here we have two numbers in cells A1 and A2 and we want to know if either number they equals 10.


In cell B1, we write the following formula:


This looks at the contents of cell A1 and decides if it’s equal to 10, and then looks at cell A2 and again decides if it equals to 10. If either cell has a 10 in it, then it will display TRUE. If there isn’t a 10 in either cell, then it will be FALSE.

So, in the example above, we have a 10 in cell A1, and correctly it displays TRUE in cell B1 meaning that it’s found a number 10 in one of the cells.

This is great, but displaying TRUE or FALSE, often doesn’t help us very much, and what we want is that if it’s TRUE, then the formula does something, and if it’s FALSE, it does something else.

So, quite often we wrap up the OR function in an IF function, to allow us to take action depending on whether it’s TRUE or FALSE. See my post on the IF function if this is new to you.

So we modify the formula like this:

=IF(OR(condition 1, condition 2), Action if true, Action if false)


We add IF and an open bracket before the OR function, then after it, we add a comma and the two actions we want to take. In this case, if the function finds a number 10, it displays the text “At least one is true”, and if it doesn’t, it displays the text “Both are false”.


Example 2 – Checking to see if at least one race time is below the target time

Let’s use the above formula in a practical example. Here we have some athletes who need to have run under 10.5s in the 100m in two pre-competition races, in order to be able to enter the competition. We have the times they ran in both races, then in column D, we add the OR formula to see if they can compete or not.


Here the formula in column D is similar as before, we start with an IF function, then the OR function.  Then we add the two conditions, in this case, a check to see if the value in column B is less than 10.5s or if the one in column C is less than 10.5s. If it finds that the athlete has run under 10.5s in one or more of the races, it states “Yes” they can compete in the competition. If they haven’t run under 10.5s, then it displays “No”.


So, we can see that Paul and John are ok to enter the competition.

Example 3 – Looking at more than two race times

In the example above we looked at two race times, but the OR function can look at lots of different values. Here the athletes have run three races.


The formula in column E is as before, except that we just add a third condition in the brackets.


Example 4 – Using the AND function

The AND function works in a similar way to the OR function. OR looks to see if 1 or more of the conditions are met, whereas, AND checks to see if ALL the conditions are met.

Here we have some students who have to get more than 60% in all three tests on the course, in order to pass the course. If they have, it displays “Passed”, if not they haven’t it displays “Failed”.


So, in place of the OR function, we add the AND one in column E.


Example 5 – Using AND to check if the values are within a range

Here we want to see which groups the students should be in. We’ve already put those who got 80% or more in group 1, and now we want to divide the remaining students into either group 2 or 3. Those who got more than 60% and less than 80% will go in group 2, and the rest will be in group 3.


To do this, we can use an AND function in column C. We look at the values in column B to see if they are more than 60% or less than 80%. If the test results meet this condition, then it displays “Group 2”, otherwise it displays “Group 3”.


Example 6 – Categorising the values based on certain conditions

In the example above, we had already allocated some students to Group 1, but this time let’s divide up the students into 3 groups all automatically.


First, in column C we start off with an IF function, and check for those who have 80% or more. If their test result is 80% or more, it displays “Group 1”. If the test is less, it moves onto the next IF statement.

This time it checks to see if the result is between 60 and 80%, as we saw in the example above. If it does, then it displays “Group 2”, if it doesn’t it shows “Group 3”.


Example 7 – Using functions within an OR or AND function

Introducing the WEEKDAY function

We can also use other functions in the OR function. Here we have some prices where if the work was carried out on a weekend, a weekend supplement is added.

We need to check to see if the date fell on a weekend, then based on whether that is TRUE or FALSE, we add a supplement or not.


In column B, we add this formula:


The WEEKDAY function looks at the date in column A and converts that into a number between 1 and 7. Sunday is 1 and Saturday is 7. So, if it reports a number that is a 1 or a 7, then the date is at the weekend.

In column E we then check to see if column B is TRUE or FALSE, if it’s TRUE we add the supplement, if not, we leave the price as it is.


We could combine the formulas in columns B and E by wrapping the OR function in an IF one:

So, we have if the date in column A is Saturday (7) or Sunday (1), it adds the price (C) and the supplement (D), if not it just displays the price (C).


Just as a little extra, if we wanted to check to see if it is a weekday, we could use an AND function. This checks to see if the date isn’t a Saturday (7) and isn’t a Sunday (1).


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

Baz Roberts (Flipboard / Twitter / Google+)