Google Workspace and Software Development
If you have landed on this page you may have discovered that dates and times don’t always work as you might expect with the Google Sheets FILTER function.
In this tutorial, we are going to cover how to use FILTER on dates, times and date and time stamps (DTS).
You can follow through the tutorial in its entirety or use the table of contents below to get directly to what you need.
Continue reading “Google Sheets FILTER function: Dates and Times”
For whatever reason, sometimes we just need a list of alphanumeric letters and numbers like this “0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ” to run down a column or across a row, or just all packed into one string of text in a cell in our Google Sheet.
I most commonly use an alphanumeric list to index data or use an alphanumeric string in the random assignment of a value.
In this tutorial, we will cover 4 ways to achieve this. Some approaches have different benefits than others. While two of the approaches even use the new Google Sheets LAMDA function.
If you just want to dive in, copy the formula, and get back into your own project, click on the link to the main numbered headers in the table of contents below. You can copy the formula into your project using the copy symbol in the dropdown menu of the formula bar and selecting the formula you want.
Each formula type can be:
The How it Works sections are not mandatory reading but might be helpful if you plan to extend the formula or are simply curious.
Oh, and number 1 is my favorite.
Continue reading “5 ways to create an ordered alphanumeric list in Google Sheets”
Imagine you have a Google Sheet tab containing a course worth of hundreds of students sorted into classes or groups. After each exam, quiz or assignment, your teacher for each class needs to enter in the grades.
To make life easier for your teachers, we want to create a summary page where we can click on their class and be navigated directly to their group.
We don’t want to have to manually create a link for each location so we are going to write a formula to do the work for us.
Now, the formula is a little complex. So first off, we will generate our list of classes and add a separate section start link for each one. Then we will combine the two together to generate a single formula that gets all the unique sections and provides a link for each one.
If you are playing along, you can grab a copy of the starter sheet here:
Table of Contents
Continue reading “Create links to the first item of each group in Google Sheets”
Have you ever wanted to split a list of Google Sheet rows in two by odd or even? Or had a time when you wanted to display all items in a range by odd or even ids in two separate lists?
You don’t have to do this manually, we can work smart and create a formula for each of these scenarios to easily separate your data.
In this tutorial, we will cover three scenarios:
If you want to play along, you can get a copy of the starter sheet here:
*Contains Easter Eggs.
Continue reading “Displaying Odd and Even Row Data in Google Sheets”
Working with IMPORTRANGE data in Google Sheets can be a little tricky. It may feel at times that it does not play by the same rules as when you are building formulas with data in the same Google Sheet.
In this tutorial, we’ll go through two approaches to filtering and sorting your IMPORTRANGE data by using the FILTER and QUERY functions. We’ll run through some examples of each and look at some of their pros and cons.
Then, we’ll wrap things up with a walkthrough and example on how to build your very own dynamic data dropdown dashboard from IMPORTRANGE data that lets us look at a set of sales by any company from our imported data any sales rep that makes a sale to them.
I encourage you to play along with the examples. You can find a copy of the Google Sheet that we will be importing here:
Click on the ‘Make a copy’ button to create your very own copy of the sales sheet. There are heaps of bonus formulas in there too along with a few fun Easter Eggs for the curios.
Continue reading “Filtering IMPORTRANGE data in Google Sheets”
