3. Create a Color Palette with Conditional Formatting

Playback Speed:

In this video, you will use conditional formatting to add color to your project.

Conditional formatting changes how cells appear when values meet specific conditions.

A "value" is a number or text entered into a cell.

"Conditions" are rules that determine what values are formatted and when.

"Conditional formatting" applies formatting to values that meet specific conditions, or rules.

For example, you might set the condition that if a cell contains the value one, the cell is formatted to turn blue, but if the cell contains the value two, it turns pink.

To get started, add a conditional formatting rule.

Begin by selecting all the cells in your spreadsheet.

Then open conditional formatting.

Check the range, which determines which cells a rule will apply to.

"A" is the first column, and "one" is the first row of the range.

"Z" is the last column, and "1000" is the last row of the range.

There are many rules you can use to format cells.

Some apply to a specific value while others include more than one value.

Some common rules apply formatting to a cell when the cell value is equal to, greater than, or less than a set value, although there are endless possibilities.

For this project, select is equal to.

Enter a number and select a fill color for that value.

Then, test the rule in your sheet by typing the number into several cells.


Your first color is ready to use.

Next, repeat the process to add more conditional formatting rules for all the colors from your image.

Choose your own colors, or use colors from your sample image.

You can easily change your palette later if you change your mind.

Each rule you create adds a single new color.

So, if you set five rules for five numbers, you'll have five colors.

When you have added a rule for every color in your image, test your rules by entering numbers directly in the cells.

Check that the cells change to the appropriate colors when you enter your values.

Now, it's your turn: select all cells in your spreadsheet, create a conditional formatting rule for each color in your image, and check your conditional formatting rules by adding numbers to cells.


  1. Select all the cells in your spreadsheet.
  2. Create a conditional formatting rule for each color in your image.
  3. Check your conditional formatting rules by adding numbers to cells.