- All right, if you want to use Google Sheets
to create a task list and have it be interactive,
that's what we're going to show you how to do right now.
And we're just going to start out,
we're gonna have three columns here,
one is just going to be the status,
so this is where you'll come in and check things off,
and then next column will have the actual task itself
so we're going to type in what you want to do,
and then the third column,
and this is based on some user comments
on a previous video that we had on making a task list,
some people wanted a timestamp to see when it was completed.
All right, so for the status we just want a simple checkbox,
so it'll be a yes or a no,
so left-click on Insert up at the top,
come down to Checkbox, and you select that.
What Google Sheets does is it places
the little control in cell B3, and when you click on it,
what it does is it's actually changing
that value from FALSE to TRUE,
so there's two possible values in B3 right now,
unchecked or checked, which translates to FALSE and TRUE.
All right, we'll leave that unchecked for now,
we're just going to select a few more cells here,
go back to Insert and click Checkbox again.
And we'll say, you're a simple person, right,
you have four things that you really want to do,
so you're focused, let's not say simple, let's say focused.
So your first task is,
in the morning you want to get some fresh air.
All right, the next task that you want to do
is you want to eat breakfast.
This has overflowed the column a little bit
so I'll come up between C and D
and just double left-click on the separator,
and that will auto-fit the column
to the longest piece of content, all right.
And one last thing that we should probably do
is make it a little bit more obvious to the eye
that these are headers and they're not part of the area
that you're going to be working in
so we'll make those bold and we will center align them,
and then we'll go to the cell borders
and put a bottom border on these three cells, all right,
so I think that looks nice, right, we're ready to go.
Now the functionality that we want to put in
is once you check a task off,
we want this cell to get a strikethrough
and the background color to just turn gray,
just to make it look like it's been done.
So we would do that through conditional formatting,
and when you do conditional formatting
the first step that you want to do
is select the cells that you want to apply it to.
You can go down a little bit further
if you think this list might get longer,
you can actually apply it to the entire column if you want,
but we're going to keep it simple
and just apply it to C3 through C6.
Okay, so go to Format and Conditional formatting,
and the Apply to range is already filled out
because you started with it selected,
so we can skip over that,
and then the rules, if you look over at the left
it's already trying to apply conditional formatting,
and this is a way that you can kind of watch it
as you build out the formula to see when it's working again.
But what this is doing,
it's prefilled to format it if it's not empty,
so all of those cells have something in them
and they're getting formatted
with a background fill of green.
We want to change that formatting to a custom formula,
click Custom formula is,
and you get this empty rectangle here
where it's asking for your formula.
All right, we'll click in there,
and notice that the rectangle
that surrounds it is blue right now,
that means that what you have in there is not an error.
But we don't have anything in there
so that's not very helpful,
we'll click an equal sign and it turns red,
so it's saying, I think you're typing a formula right now
but I know it's not done
because it doesn't give me any result.
Okay, that's expected because you're not done,
and we're going to type in a formula,
and what we want to do with these formulas,
when you have a range selected in conditional formatting,
they're always written as if you're in the top left cell.
So in this case, if we look at our range, that's C3,
and what we want to do here
is we want to see if B3 is checked.
So our formula is going to say B3,
and when it's checked, the value is TRUE.
You don't need quotes around this TRUE,
you would if you were looking for a certain word,
but TRUE is just a value of TRUE or FALSE,
it's a boolean variable,
which means that it's only one of two values,
and TRUE is an actual value, it's not a string,
so you don't need quotes around it.
The outline of the rectangle has changed from red to blue,
that's saying that this formula's working.
And when you look over here though
you don't see much going on, right, you can't tell,
so let's put a checkmark in one of these boxes,
and that did conditionally format this cell,
if this box was checked.
So we can tell that this rule is working
but what we want to do is we want to make it be crossed out,
so we're going to add the strikethrough,
and then let's say we want to change the fill color
just to an indiscrete kind of gray,
and let's even change the font while we're in here,
let's change the font to just another shade of gray,
all right, so that really looks like it's done, right?
Now the next thing that we want to do
is based on some user feedback that I got,
we had created an interactive checklist before
and people were using it but they wanted to see the time
at which they completed the task.
So I was able to find a kind of a useful formula for this,
I'll put a link to that video in the description,
just to give the person credit,
that they came up with something
where you don't need a custom script,
you just do it with a couple of steps.
So what I'm going to do,
I already have it in this other worksheet,
I'm going to grab it
and we're going to paste it in the other worksheet
and we'll talk about what's happening.
All right, so we'll talk about this formula,
but there is one thing that you need to do first,
to go up to File and go to your Spreadsheet settings,
click on the Calculation tab
and you need to turn Iterative calculation to On,
make sure that's On
and just change the Max number of iterations to 1.
All right, so save that setting,
that will only apply to this particular spreadsheet,
and what that's doing is,
this formula that's in D3 creates a loop,
and you don't want Google Sheets
to just do that loop infinitely,
you only want it to calculate at one time,
so this formula will give you an error
if you don't uncheck that.
So let's double-click in this cell
and talk about this formula, what this does,
it says, hey, if B3 has a checkmark in it,
what I want you to do, if this cell, D3, isn't blank,
run the now function and return that value,
otherwise don't do anything.
So I'll capitalize NOW just to make it look a little more,
all right, a little bit more formal,
but the NOW function, we'll look at this help,
it returns the current date and time as a date value,
date and time, so we'll look at what that does.
First we'll copy this formula down,
and it's blank now, right,
but if you check one of these checkboxes,
it puts the time in.
If you don't want this to show the time
and you only want it to show the date
you can change NOW to TODAY, which is a similar function,
but it's just a little,
what it returns is just a little bit cleaner,
and you have to uncheck this and check it again,
but that's how to add a timestamp
to this interactive checklist.
And there is one more item,
I got several comments on this other video
of people asking if you can make it auto-sort
and move what's done to the top or the bottom.
Well, there's no way to do that
without writing a custom script
but what you can do if you want to do it manually
is just select the table,
go to Data and Create a filter,
and then you get these controls on each of the headers
where if you click on them,
what you can do is Sort from A to Z,
and that will sort the status of these checkboxes.
Z to A will put them at the top,
A to Z will put completed tasks at the bottom.
So next we're going to go a little bit deeper
on how to use conditional formatting,
how you can highlight an entire row
based on just the value in one cell.
Thanks for watching, I'll see you in that video.