I am using Assignment tracker default template of Google spreadsheet. The Status column has four default options Done, In progress, ... and Subject column has four options Maths, Biology, ....

The options for Subject could be edited from Subjects tab, but from where can the options of Status be edited?

1 Answer 1


Click cell D5.

Choose Data from the menu and then Data validation from the dropdown menu: Data validation menu item

When the Data Validation dialog appears, the Cell range: box will say Assignments!D5; add to that so that it says Assignments!D5:D.

Criteria will show List of items with a four-item list that is currently being used as the options for those drop-downs in the cells. Change that list to suit your needs. Data validation dialog box

You may also then want to change the conditional formatting to suit your needs. To do that:

Again, select cell D5 (though it could be any).

Choose Format from the menu, and then Conditional Formatting from the drop-down menu.

When the Conditional Formatting dialog appears, the top two items will be Text is exactly 'Not started' D5:D41 and Text is exactly 'In progress ' D5:D41.

Since you will have changed the Data Validation list to contain other items, you can either

a. hover over each of these entries in the Conditional Formatting dialog until the trashcan appears and click that to delete the conditional formatting rule, or

b. click on the rule and edit the text and formatting to match your new list items and preferences. You may need to add more rules. Just use the same range (D5:D41) and Text is exactly, like the other two, filling in your own new item and preferred formatting.

  • 1
    Great answer! Hope you don't mind I added a couple of screenshots and some formatting to make it even clearer. Oct 16, 2018 at 12:00
  • Perfect, Vidar. I'm sure that will help many visual learners along the way. Thanks.
    – Erik Tyler
    Oct 16, 2018 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.