Figure 1: What we are building today
The Checkbox feature is a new functionality added to the Google Sheets application. They will be particularly helpful for tasks like project/task tracking such as a ToDo List.
Today, I will be showing you how to beautify your Spreadsheet ?ToDo List? application with a combination of the CheckBox feature and the good ol? conditional formatting.
Step 1: Create a blank Google Sheet
After navigating to the Google Sheets homepage, click on ?? blank?
Bonus: Access the homepage by visiting this link: https://docs.google.com/spreadsheets/u/0/ or by typing ?sheets? into Google search bar.
Figure 2: Google search result of ?sheets?
Step 2: Create the Table headings
Figure 3Tip: I center-aligned ?ToDo? after merging cells A1:C1
Step 3: Freeze row 2 ? the row containing the ToDo list column titles
Freezing ensures the title persists even as you scroll. So that, if your ToDo list (or whatever project management sheet you are building) grows long you can easily remember what cell you are working on
Figure 4How-to: Visit View > Freeze > 2 rows
Step 4: Insert ?Checkbox? into Column C starting with C3
Figure 5
Step 5: Use conditional formatting to set the formula
We want our spreadsheet to cross out the ?date? and ?task? on a row when we mark it as ?done?.
Figure 6: Focus on the highlighted sections
As shown in Figure 6, the Checkbox on C3 has a default value of FALSE when it is not checked. But when it is checked it becomes TRUE.
So, altogether, our formatting formula would read:
Format cells if Custom formula is “C3 equals to TRUE” and then we specify the formatting style below.
In this tutorial as you?ll see in the next step (6), I made the ?done? text green and shaded the cell while putting a strikethrough.
Note: By design, every Spreadsheet formula starts with an ?=? sign.
Step 6: Test your formula, clean it up and add some spice!
To test our formula (aka formatting), I added real data.
Figure 7: Addition of real data but it doesn?t work as expected
Here are a few things to note and try;
- The formatted date ?19-May? doesn?t come out by default. You have to set it in your sheets. Here is how: Format ? Number ? More Formats ? More date and time formats?
Figure 8
2. The next date was set as ?current date + 1?
3. The CheckBox was repeated by dragging the ?fill handle? on cell C3 down.
4. I adjusted the ?Apply to range? from 3:20 to A3:B3 because, it was applying across the row and I didn?t want that.
5. Initially, the strikethrough functionality didn?t work on the task column because I didn?t lock the column C. I?m not quite sure why I have to lock it, but if you know please reach out in the comment section
After fixing the 5 things above, I ended up with this:
Figure 9: All working but the colours
I added blue background colour, changed the text colour to white?just to make it pop.
Figure 10
Then, I removed the gridlines (by going to ?view? and unchecking ?gridlines?) and focused on only the section I needed.
Figure 11
Inserted border lines and used dotted lines for the inner lines to end up a bluer version of the final spreadsheet we set out to build.
Figure 12
The End.
Thanks for following this tutorial, if you have further ideas on uses of this checkbox feature, I?ll be glad to hear!
