Google Sheets for Intermediate Users - The Complete Course, by Technology for Teachers and Students on YouTube. 33 min 21 s.
- Video length
- 33 min 21 s
- Time to do it
- 45 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Open your spreadsheet and select the column or range you want to control. Click Data in the menu, then Data validation to open the sidebar.
In the sidebar, pick a rule such as Dropdown, Number or Text contains. Enter the allowed values or range, choose what happens on invalid input, and click Done.
To clean your data, select the range and use Data, then Trim whitespace to remove stray spaces. Use Format, Number to fix inconsistent number or date formats.
Remove duplicate rows with Data, then Data cleanup, then Remove duplicates. Check the reported count before confirming.
Highlight problem cells with Format, then Conditional formatting. Set a rule such as text contains or greater than, choose a fill color, and click Done.
Build dynamic formulas with functions like IF, IFS, VLOOKUP or XLOOKUP. Wrap references in IFERROR so errors show a blank or message instead of #N/A.
Use absolute references with dollar signs, like $A$2, when copying a formula down a column so the lookup range stays fixed.
Share the sheet with the Share button and set each person to Viewer, Commenter or Editor. Protect key ranges with Data, then Protect sheets and ranges.
Select your data table, click Insert, then Pivot table. Choose whether it goes on a new sheet or an existing one.
In the pivot table editor, drag a category field to Rows, a number field to Values, and a filter field to Filters. Change the summary from SUM to COUNT or AVERAGE as needed.
Tips and common mistakes
Data validation only blocks new entries, so run it before you paste in outside data.
Conditional formatting rules apply to the selected range only, so check the range box before clicking Done.
Pivot tables do not refresh automatically when source data changes, so edit and reapply the pivot when numbers look stale.
Test a formula on two or three rows before filling it down the whole column.
Questions
Do data validation rules stop people from pasting bad data?
No. Pasting usually bypasses validation, so clean pasted data with Trim whitespace and Remove duplicates afterward.
Why does my pivot table show the wrong totals?
The Values field may be set to COUNT instead of SUM, or the source range may have changed. Check the summarize-by setting and the data range.
Can I stop someone from editing one column but keep the rest open?
Yes. Use Data, then Protect sheets and ranges, select that range, and set permissions so only you or chosen people can edit it.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
