I find I often want to have a column that is meant to be one or more choices from a fixed set. E.g. a Codebase field in a dev roadmap spreadsheet, which might have choices like {DB, API, JS, CSS, etc.}. A given project could use one or more options.

The natural way to enter that is just as a comma-separated list:

Project   Codebase
Foo       JS, CSS
Bar       CSS, API, DB
Baz       JS

However, that makes filtering and pivoting awkward because each combo is treated as a separate entry. Is there any way around that other than splitting each option out into its own boolean column?


Are there a fixed number of comma-separated lists that would be valid? If so, you could us the Data > Validation option to set up so that the lists are always in the same order, so reporting is more meaningful.

Alternatively, or perhaps even as well as that, you could use additional helper columns to calculate Boolean values based on presence of a given string in the chosen value, using the FIND function.

See an example here: https://docs.google.com/spreadsheets/d/1hhm18rM0_n2YE1n0YT8-ty8o17O7s3kY54GPrSdCsTs/edit?usp=sharing

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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