Sort data in Google Sheets on a mobile device

-UPDATE- As of November 2016, their is a “Create a filter” option in the menus for Google Sheets on an iPad. You can find it by clicking the three vertically algined dots in the upper right hand corner of your spreadsheet. The tips below still apply to using the SORT function, but it not your only choice for sorting and filtering on an iPad now.

If you are looking for the SORT function in Google Sheets’ mobile app, good luck.  Much like the FILTER function in mobile Google Sheets, it has been relegated to the list of functions that must be typed in or found in the list of functions available in Sheets.  

Before you enter your SORT formula, you will want to select a cell in which to type it that will be the upper left most cell for the filtered list. This function write the data below and to the right of your starting point.  Once you find the right cell, enter the command using the following syntax:

=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])

In the formula above, SORT is the name of the function, range is the table of data that you want to be filtered, sort_column is the column by which you are sorting, and is_ascending is a true/false field to determine if you want the data sorted in ascending or decending order.  A value of TRUE for is_ascending would sort the data in ascending order (i.e. 1,2,3 or a,b,c).

Video explanation

Find more information on the SORT function at sheetshelp.com


One column

Let’s start with a simple example in the image below.

one column sort formula

This is a small list of data. For whatever reason, you find the need to sort it.  Above is the data with the formula typed in a cell below it.  Below it the result after entering the formula. 

 

 

one column sort result

As you can see, the sort function creates a new list sorted by the parameters that you specified.  Be aware that the formula still remains in the cell in which you typed it. If you want to keep the data in this new list, you may want to consider copying it and pasting it as values. This will fix it in place even if the original data is changed.

Two columns

Next, let’s sort a table with two columns.  Select both rows, specify that you want to sort by the second row, and in decsending order..

=SORT(a15:b17,2,false)

 

two column sort formula

This will create a new, sorted list.  Again, the filtered data is still dynamic.  If you change anything in the original list to be sorted, the new sorted list will also change.

two column sort result

Three columns

You can also specify a second column by which to sort data and a secondary level of orgainizing data.  The picture below is showing an easy example of this.

three column sort formula

The output of this formula is as follows.  You can see that it sorted first by type of animal then by the number in the first column.

three column sort result

Conclusion

Having the sort function available, even if it is not in the menus, can be quite handy when you are working on a mobile device.  For larger sets of data, the sort function can be faster and more accurate than sorting data manually.  Enjoy and happy spreadsheeting to you all.

Filter data in Google Sheets on a mobile device

-UPDATE- As of November 2016, their is a “Create a filter” option in the menus for Google Sheets on an iPad. You can find it by clicking the three vertically algined dots in the upper right hand corner of your spreadsheet. The tips below still apply to using the FILTER function, but it is not your only choice for filtering on an iPad now.

Most spreadsheet users are used to performing all of their functions through the menus with the click of a mouse.  However, today’s mobile versions of these programs offer very few options through their menus.  The FILTER function, much like the SORT function function, is one of those options that lost its coveted menu spot and has been relegated to the list of functions that must be typed in or found in the list of every function available in Sheets.  

Before you enter your filter formula, you will want to select a cell that will be the upper left most cell for the filtered list. This function writes the data below and to the right of your starting point.  Once you find the right cell, enter the command using the following syntax:

=FILTER(range, condition1, [condition2, ...])

In the formula above, FILTER is the name of the function, range is the table of data that you want to be filtered, and the conditions are how you want it to be sorted.

Video explanation

Find more information on the FILTER function at Sheetshelp.com.

One column

Let’s start with a simple example in the image below. one column of data with filter formula typed but not yet appliedThis is a small list of data. For whatever reason, you find the need to filter it. A list this small would be easier to just manipulate by deleting what you don’t want, but it is kept simple so the illustrations can get right to the point.

flitered one column

As you can see, the filter function creates a new list with just the data left that you specified.  Be aware that the formula still remains in the cell in which you typed it. If you want to keep the data in this new list, you may want to consider copying it and pasting it as values. This will fix it in place even if the original data is changed.

Two columns

Next, let’s sort the table based on criteria that resides in neighboring cells.  Select both rows and use the second field of the formula for the sort criteria of b1:b4=”b”.

=FILTER(al:b4,b1:b4="b")

 

a filter based on two columns not applied yet

This will create a new, filtered list with just the rows that contain the letter “b” in the second column.  Again, the filtered data is still dynamic.  If you change anything in the original list to be sorted, the new sorted list will also change.

two filtered columns showing the result of the formula

Three columns

You can also filter a table based on multiple criteria. The picture below is showing an easy example of this. The formula works as an AND statement, meaning that both conditions need to be TRUE in order for the data to be output by the function.

three columns formula not yet applied

The output of this formula is as follows.  The function only kept the data that had a “b” in column B AND a 3 in column C.

filtered three columns

Follow image below for the live Google Sheet with this data

docs share icon

Conclusion

Having the filter function available, even it is not in the menus, can be quite handy when you are working on a mobile device.  For larger sets of data, the filter function can be faster and more accurate than sorting data manually.  Enjoy and happy spreadsheeting to you all.

How to validate and sort e-mails with Google Sheets Mobile

Validating e-mails

If you have a list of e-mails that you need to clean up, you can easily do it from your mobile device. This tutorial was created using an iPad running iOS 8. You do not need to do any fancy coding nor do you need to use a laptop computer. Google’s version of Sheets for the iPad lets you do this albeit the sorting is a bit different than you may be used to. As you will see, you will have to sort the list with a function. The function will create a new, sorted list instead of sorting the existing one. It seems that the menu option to sort a list should exist, but as of early 2015 it is nowhere to be found in the spartan menus that are offered.

First, let’s start with a list of e-mails. Some good and some not so good.  An e-mail must have certain charactersitics including always having and @ sign, having no spaces and having a .com or other domain type at the end. 

first formula for validate

To the right of the column containing the e-mails, the ISEMAIL function has been entered into cell B1.  You can see the formula typed into the rectangle to the right of the blue function box. The syntax of the function is

=ISEMAIL(value)

where value in this example is a reference to where the e-mail resides. Note that this function is designed to work on one cell at a time and does not work for a range. Nor does it accept any more arguments. It only validates the email as good or not good and outputs the result in the cell, that’s all. 

Press the checkmark after you enter the function. You need to get the cell to look like it does below, with a blue box around it and a little green square on the underside of the box.

result of first validate formula with little green square one blue rectangle surrounding cell

This green square is telling you that you can copy to formula to the cells below by dragging it with your finger.

 

formula copied down

Sorting the list

After you have copied the function to the cells where you need it, you have validated all of the e-mails.  This may have been all you wanted to do or you may now want to sort the data by true and false to delete the non valid e-mails.  You no longer need to have the data highlighted. As there is no menu option for sorting, you will have to use a function to accomplish this. Choose an active cell at the bottom or to the right of the data to be sorted to make room for the new list. As you may have guessed, the name of the function is SORT. The syntax is as follows.

=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
  • range – location of the data to be sorted.
  • sort_column – number of the column within the range to use for the sort. Further, it can be a “`range` or a range outside of `range` containing the values by which to sort”. What that means is anyone’s guess, so let’s just ignore that one.
  • is_ascending – `TRUE` or `FALSE` – TRUE will sort in ascending order and FALSE will be descending
  • sort_column2, is_ascending2, … [optional] – Can be used if you want to sort by secondary criteria in additional ranges (i.e. sort by ISEMAIL then sort by e-mail address to end up with alphabetical lists of valid and non-valid e-mails)

sort result

There you are. You now have a sorted list. The next steps are up to you depending on how your are planning to use the resultant list. Enjoy..