Skip to content

GS Challenge 1 – The Categorized Task List

Welcome to the first Google Sheets challenge article.

In this article, I will give you four different questions within Google Sheets to test your spreadsheet knowledge.

How many tasks will you be able to perform correctly? Click the link below to download the practice file to test yourself and find out!

Google Sheets Challenge File – The Categorized Task List

Question 1

Below is a small task list already started for you in Google Sheets. Your goal for question 1 is to add checkboxes to the eight cells in the DONE column of the task list.

Question 1 – Solution

To add checkboxes to the cells in the DONE column, start by selecting those cells. With these cells selected, go to Insert Checkbox.

You now have checkboxes to work with.

Question 2

For question 2, your goal is to change the format of the dates in the DUE DATE column to dd-mmm-yyyy. For example, 2-Jan should change to 02-Jan-2023.

Question 2 – Solution

To change the format of the dates, start by selecting all of the cells in the DUE DATE column. With these cells selected, click the More formats button in the toolbar, and select Custom date and time.

Look for the date format within the list of options and select it.

If you do not see the date format you are looking for, you must create the date format manually. To create the date format, start by deleting everything in the number format bar.

Next, click on the down arrow and select Day.

Click on the day format, and select Day with leading zero (05).

Enter a dash, then click on the down arrow and select Month.

Click on the month format, and select Month as abbreviation (Aug).

Add one more dash, and then click on the down arrow and select Year.

Click on the year format, and select Full numeric year (1930).

Finally, click on Apply. The dates are now formatted correctly within the task list.

 

Question 3

For question 3, you have a list of categories in F4:F7. Your task is to create a dropdown list for all the cells in the CATEGORY column.

The dropdown list must pull from the categories listed in F4:F7.

Question 3 – Solution

To create the dropdown list, start by selecting the cells in the CATEGORY column. Next, go to Insert Dropdown.

In the Data validation rules panel on the right, change the Criteria to Dropdown (from a range).

Click on the Select data range button, and select the range F4:F7. Then click on Ok.

At this point, you can click on Done. However, I am personally not a fan of the Chip style of the dropdown menu. So to change the style, you can click on Advanced options and then change the style to Arrow.

You can then click on Done. You now have a dropdown menu for the cells in the CATEGORY column.

Question 4

For your final task, you want to make it so any tasks marked as “Done” are automatically greyed out.

For example, your task list should look something like this.

Question 4 – Solution

To pull this off, you need to apply conditional formatting to the task list.

Start by selecting all the cells under the TASK, CATEGORY, and DUE DATE columns. With these cells selected, go to Format > Conditional Formatting.

In the conditional format rules panel on the right, change the Format cells if option to Custom formula is.

For the value or formula, enter in the formula =$D4.

This formula contains a mixed reference. The column part of this reference is an absolute reference. The row part of the reference is relative.

As a result, all of the cells in row 4 will look to the value in D4. All the cells in row 5 will look to D5. Row 6 will look to D6. Etc.

As you can see, the row part of the reference changes depending on the row, but the column letter stays the same.

Also, since the cells in column D contain a checkbox, they will result in either a TRUE or FALSE value. Therefore, all tasks marked as done will activate the conditional formatting. All unmarked tasks will not.

To finish the conditional formatting, change the fill color to None.

Change the font color to Grey.

And finally, click on Done. Now all completed tasks will be automatically greyed out.

 

And that concludes your first Google Sheets challenge of the week. I hope you had fun trying to solve all the problems and learned something along the way too!

Be sure to come back next week for the next Google Sheets challenge.

Watch the Video

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top