Conditional Formatting and Color Scale

In this chapter we will cover the topic Conditional Formatting and Color Scale. You will learn how to use conditional formatting to apply a color scale and highlight cells based on specific conditions.

Applying Conditional Formatting with Single Color

Conditional formatting allows you to highlight cells with a specific color if their value meets a given condition. This helps visually emphasize important data and makes it easier to analyze information.

Using Conditional Formatting for a Single Color (e.g.)

• Select the range;
• Go to the Format menu → Conditional formatting;
• In the Single color section, choose a rule:
  • Select a condition (e.g., Greater than, Text contains, etc.);
  • Enter the value that will be used to check the cells;
• Click the color field and choose the desired color that will be applied to cells meeting the condition;
• Click Done to save the rule and apply it to the selected cells.

Example of Use

You have a sales data table, and you want to highlight all sales above $500 in green.

• Select the cells containing the sales data;
• Go to Format → Conditional formatting;
• In the Single color section, choose Greater than;
• Enter the value 500 in the cell check field;
• Click the color field and choose green;
• Click Done to save and apply the formatting.

Now, all cells with sales greater than $500 will automatically be highlighted in green.

Applying Conditional Formatting with Color Scale

Conditional formatting with a color scale allows you to visualize data using a color gradient, where the value of cells is displayed according to a predefined color range. This helps quickly assess data distribution and identify trends.

Using Conditional Formatting with Color Scale (e.g.)

• Select the range;
• Go to the Format menu → Conditional formatting;
• In the Color scale section:
  • Choose the type of color scale (e.g., from red to green);
  • You can choose a preset palette or customize your own by setting the minimum, midpoint, and maximum values with corresponding colors;
• Click Done to save the rule and apply the color scale to the selected cells.

Example of Use

You have a table with temperature data for the month, and you want to visualize the changes from the lowest to the highest temperature.

• Select the cells with the temperature data;
• Go to Format → Conditional formatting;
• In the Color scale section, choose the scale type from blue to red;
• Set the values for the minimum temperature (blue), the average temperature (neutral color), and the maximum temperature (red);
• Click Done to save and apply the formatting.

Now, the temperature changes will be displayed with a smooth color transition, making it easy to identify the highest and lowest values.