Create Conditional formatting
Create Data Validation
Use Filter Views
Make a copy of this Sheet - bit.ly/L2sheet
Use conditional formatting to highlight any letter grade with a D in Bright Pink.
1 > Select All
2 > Format>Conditional Formatting
3 > Select Text is exactly and enter 'D'
4 > Select Bright Pink Color
Add another: Using Conditional Formatting, highlight any scores under 70% in yellow.
1 > Select the Data Validation Sheet
2 > Select column B
3 > Click Data and then Data validation.
4 > Add options for the dropdown menu
Test tip: Create dropdowns with Data Validation, NOT formatting, filters or Data Cleanup.
Practice creating a FILTER VIEW to view only the F's
1> Select the Filter Views Sheet
2> Select Data>Create Filter View
3> Select the arrow (green) in column C
4> Select to Clear all and then reselect only F
Filter - all viewers see only filtered data
1 > Select a column or header row.
2 > Go to Data>Create a Filter
3 > Use Filter in header row to select data
Filter Views - may be chosen by viewer
1 > Go to Data>Filter Views>Create New Filter Views
2 > Select to view only the students who have an F in the Final Average column
3 > Close Filter View and reopen
You have a spreadsheet for several schools over the course of many years with student scores, funding, etc. You want a snapshot to see what the average was for each school each year and the average of all the schools over the course of each year.
Insert>Pivot Table
Columns are Years (yes to totals)
Rows are Schools (yes to totals)
Values are Average
Summarize by Average