Google Sheets: FILTER, QUERY, data validation and protected ranges

Skills you'll practise

• Write FILTER formulas with one or more conditions and predict when they return #N/A
• Write QUERY statements that select, group, sort and label data in the required clause order, and identify why a QUERY returns wrong totals or no rows
• Set up dropdown data validation that rejects or warns on invalid entries
• Choose between a warning and restricted editing for a protected range, and explain why protection isn't a security control

Course outline

1. 1.FILTER: live views of a shared listLesson · 14 min
2. 2.QUERY: summaries, and why totals go wrongLesson · 18 min
3. 3.Data validation: stop bad entries at the doorLesson · 12 min
4. 4.Protected sheets and rangesLesson · 14 min
5. 5.Google Sheets: FILTER, QUERY, data validation and protected ranges: knowledge checkKnowledge check · 14 questions
6. 6.Google Sheets: FILTER, QUERY, data validation and protected ranges: practical exerciseKnowledge check · 1 question
7. 7.Final exam8 questions · passing it completes the course, so people who already know the material can test out

Sources it draws on

The lessons and questions are written from these references, so learners can go back to the original.

• Google Docs Editors Help: FILTER function
• Google Docs Editors Help: QUERY function
• Google for Developers: Query Language Reference (Version 0.7)
• Google Docs Editors Help: Create an in-cell dropdown list (data validation)
• Google Docs Editors Help: Protect, hide, and edit sheets