Conditional Formatting

Using Google Sheets and Numbers

Abby Gonzalez

5th Grade Math

Lubbock Cooper South

agonzalez@lcisd.net

​

SLIDESMANIA.COM

What is Conditional Formatting?

Conditional formatting is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula

Photo by Jordan Whitfield on Unsplash

SLIDESMANIA.COM

Why Use Conditional Formatting?

• Allows students to self monitor their progress & can slow down students who rush through assignments
• Allows teachers to quickly see if students are grasping concepts
• An easy way to engage students through technology and implement gamification into lessons and activities

SLIDESMANIA.COM

How to Use Conditional Formatting in Google Sheets

SLIDESMANIA.COM

1. Choose your content and create a template for your questions

• I like to bold the boxes where students are supposed to put their answer to make it easy for students
• To make larger spaces you can merge cells by highlighting the cells you would like to merge and choose this icon

​

• If you question is long, you can wrap the text by clicking on the cell with the text and choosing this icon

SLIDESMANIA.COM

2. Create a rule for each cell you want to conditionally format

• Highlight the cell you want to conditionally format, choose Format, and then Conditional Formatting

​

SLIDESMANIA.COM

2. Create a rule for each cell you want to conditionally format

• A toolbar will pop up on the side where you can set your conditional formatting rule. You have lots of options at this point

​

SLIDESMANIA.COM

2. Create a rule for each cell you want to conditionally format

• Start with when you would like the cell formatted. Choose the drop down arrow under Format Cells if…
• You can choose how you would like the text to be written
• You can choose how you would like a date to be written
• You can choose how you would like numbers to be written

​

SLIDESMANIA.COM

2. Create a rule for each cell you want to conditionally format

• Next, choose what you would like the cell to do once the desired text is entered in correctly
• Under Formatting Style you can choose if you would like the box to fill a certain color
• You can choose if you would like the text to change a certain color
• You can choose if you would like the text to bold, underline, or italicize

​

SLIDESMANIA.COM

2. Create a rule for each cell you want to conditionally format

• When you have completed a rule it should look something like this. Be sure to click Done when you are finished to apply the rule to the cell

​

​

• The rule will show like this when it is applied

SLIDESMANIA.COM

3. You can add additional rules to the cells as well

• You can keep it simple by adding only one rule to each cell to where the students will know if it turns a color they are correct
• You can also add a second rule to the cell where students are able to see what is correct and incorrect. Simply choose + Add Another Rule and follow the same steps to create the desired rule
• In my example below I wanted the students to build equations. I wanted it to be obvious to students which part of the equation was correct and which part of the equation was incorrect. The cells turn green when they are correct and red when they are incorrect

​

​

SLIDESMANIA.COM

How to Use Conditional Formatting in Numbers

SLIDESMANIA.COM

• Choose your content and create a template for your questions

• You can add pictures and text boxes easily to numbers
• Be sure to add a table so that you are able to conditionally format
• You can edit the size of the table by clicking on the two lines at the end of the rows and columns and moving them until you get your desired number of cells
• You can also edit or delete rows and columns by choosing the drop down arrow at the front of each row or column

SLIDESMANIA.COM

2. Create a rule for each cell you want to conditionally format

• Click on the cell you would like to format. A toolbar will appear on the side of your screen. At the top of your toolbar are the options: Table, Cell, Text, and Arrange
• Click on Cell then choose Conditional Highlighting…

​

SLIDESMANIA.COM

2. Create a rule for each cell you want to conditionally format

• Choose Add a Rule for your options to appear. You can format to numbers, text, date, duration, or cell

​

SLIDESMANIA.COM

2. Create a rule for each cell you want to conditionally format

• Next, choose what you would like the cell to do once the desired text is entered in correctly
• Type in the box what you would like the cell to say in order for it to format.
• Choose how the cell will format. You can have the cell fill with a color, you can have the text change color, or you can have the text change to bold or italic

​

SLIDESMANIA.COM

2. Create a rule for each cell you want to conditionally format

• When you have completed a rule it should look something like this. Be sure to click Done when you are finished to apply the rule to the cell

​

​

​

• The rule will show like this when it is applied. You can click Show Highlighting Rules… to view your rule

SLIDESMANIA.COM

3. You can add additional rules to the cells as well

• You can keep it simple by adding only one rule to each cell to where the students will know if it turns a color they are correct
• You can also add a second rule to the cell where students are able to see what is correct and incorrect. Simply choose Add A Rule and follow the same steps to create the desired rule

​

​

​

SLIDESMANIA.COM

Tips and Tricks

SLIDESMANIA.COM

SLIDESMANIA.COM

Copy and Paste

​

• In BOTH Google Sheets and Numbers you can copy a cell and paste it to another cell. This will copy and paste the conditional formatting as well
• This is a quick way to duplicate a rule and then you can go in and change the desired text without having to create a separate rule for every single cell

SLIDESMANIA.COM

Use a new Sheet for Each Question

​

• In BOTH Google Sheets and Numbers I make one template for how I want the questions to look
• Once I have the problem formatted the way I want it, I duplicate the sheet for the next problem and change the question and answers
• This is also makes it easy for students to navigate through the assignment

SLIDESMANIA.COM

Pros & Cons to Using Google Sheets

Pros

• Google Sheets is a live document so all of your changes update instantly
• Friendly with Google Classroom and I can see student’s progress live as they complete the assignment
• Can be shared easily with students and students can open the document easily
• Can be imbedded into a google site

​

Cons

• Can be difficult to change the shape of the cells to fit what you need
• Can be difficult to add pictures and have them placed where you want and the size you want

​

SLIDESMANIA.COM

Pros & Cons to Using Numbers

Pros

• Easy to add pictures and text
• Can easily move things around the document. You can add the table only where you want it and change the size of the table
• Can change the formatting rules quickly by choosing to show the formatting rule and then just clicking on each cell and changing the text or number

​

Cons

• Not a live document and needs to be saved after each change
• Students have to download the document and open it in the Numbers App to be able to edit
• Students have to re-upload their completed document to google classroom in order for you to be able to view their completed work

​

SLIDESMANIA.COM

Examples

SLIDESMANIA.COM

Building Equations

• In this assignment students were having to build equations to solve multi-step problems. The equations started with the numbers in place and students had to place the operations
• As the problems progressed, students had to build the entire equation
• Boxes turned green to show the students which parts of the equation were correct
• Boxes turned red to show the students which part of the equation was wrong
• This allowed students to focus on the part of the equation they built incorrectly

Photo by Charles Deluvio on Unsplash

SLIDESMANIA.COM

Escape Rooms

• I have built conditional formatting into our digital escape rooms
• For the escape room, students typically have to complete 4 tasks to receive 4 codes. All 4 codes allow them to break out at the end
• The colors correspond to what the students have to use as their code for that task

Photo by Charles Deluvio on Unsplash

SLIDESMANIA.COM

Comparing Numbers

• Conditional Formatting is a great tool for students to use to practice comparing numbers
• In this activity, students had to come up with a number that would fall between two numbers that were given
• Through conditional formatting I was able to set a range of answers
• Any student who answered in that range was able to get it correct

Photo by Charles Deluvio on Unsplash

SLIDESMANIA.COM

Input Output Tables

• For this assignment students were required to complete an input output table based on a numerical rule on a graph. Numbers was easy to add pictures and text boxes
• Students had to fill in the table with the coordinates shown on the graph, find the rule, and then use the rule to fill in the missing values on the table
• Cells turned green when the student filled in each cell correctly

Photo by Charles Deluvio on Unsplash

SLIDESMANIA.COM

THANK YOU!

SLIDESMANIA.COM

SLIDESMANIA.COM

Free themes and templates for Google Slides or PowerPoint

​

​

NOT to be sold as is or modified!

Read FAQ on slidesmania.com

Sharing is caring!

SLIDESMANIA.COM