Week 29 - Conditional Formatting

CLASS OBJECTIVE

Apply conditional formatting in a spreadsheet table to automatically highlight the relevant contents in a cell or a range.

INTRODUCTION

In this course we have already talked about cell formats, such as fill colors, borders, font formats, etc. Conditional formatting will AUTOMATICALLY alter the appearance of certain cells, but we must established a condition for this format to be applied.

For example, on a report card it is very common to see grades below 6 appear highlighted in red.

In this example of conditional formatting, when changing the grades below 6 to passing grades, the format should be updated automatically.

In today's activity you will have to use a table like the one above to apply these formats that will be applied when certain conditions are met.

CONDITIONAL FORMATTING

The procedure for applying conditional formatting in Google Sheets is pretty simple. As always, the main thing is to correctly select the cells in which you will apply this Conditional Formatting.

Once the selection of the cells has been made, in the FORMAT tab you have to select the CONDITIONAL FORMATTING option. This will open a new panel on the far right of the screen, like the one shown below.

 

If you correctly selected the cells before opening this panel, it will no longer be necessary to indicate where the format will be applied.

• FORMAT RULES. It is the condition that is established so that the formats are applied, for example, a number is greater or less than another, or that it contains a certain text, or that some date is met.
•  FORMATTING STYLE. They are the formats that will be applied in case the condition has been met. You will notice that they are only font or fill formats.

With the "Add another rule" button you can establish more conditional formats, but the order in which they are applied could override the previous ones.

Practice of the topic (done together with the teacher)

You should take your class notes before completing this week's activity. To do it completely, you'll need to remember several topics such as sorting, filtering, formulas, and functions (prior knowledge.

Now that we are about to close the school year, a spreadsheet report will be very useful to help you calculate your final grades and averages.

• To get started, open a new spreadsheet and you will create the following table. Apply static formats, that is, those that don't change automatically.

 

• Fill in the table with your grades from Periods 1 and 2. Make up grades for the third period.
• Calculate subject averages, period averages, maximum and minimum grade per period, using the AVERAGE, MAX and MIN functions.
• Set the "number format" so that grades are displayed with only one decimal place.
• Apply bold format to the results of the AVERAGE function.

Use this example table to compare with your results.

Now you will apply the conditional formats.

• Select the grades of all your subjects, use the following reference (C3: F13).
• Apply conditional formatting to these cells so that grades less than 6.0 have a red background and white letters.
• Now select only the results of the averages by period (C15: F15) and the averages by subject (F3: F13). Remember to use the CONTROL key to select both ranges.
• Apply conditional formatting so that grades less than 8.0 have a yellow background.

Since the grades of Period 3 are not real yet, I invite you to change them so that you can see how the format of the cells is automatically changed when the established condition is met. Make up some "under 6" grades and you will see the changes.