How to change cell color for every Saturday・Sunday・holiday

When managing Excel data, one important aspect other than formulas will be ‘cell color and text color‘. It makes the data more easier to look at.

Let us look at ‘conditional formatting’ that changes the color of cells and texts based on specified conditions.

 

Example of use 1) Change the cell color for every Saturday

Below is a company schedule. We will set up a formatting rule so that if column C is a ‘Saturday’, the cell color will change to light blue and if a ‘Sunday’ or a ‘holiday’, to orange.

Original table)

 

(1) Highlight cell range to format.

 

(2) Begin the set up of conditional formatting.

① Select the [Home] tab.
② Select [Conditional Formatting (※click on the word)] from the ribbon.
③ Select ‘New Rule’.

 

(3) When the dialog box appears, follow the steps below:

① From ‘Select a Rule Type:’, select ‘Use a formula to determine which cells to format‘.
② In ‘Format values where…’, input the following formula beginning with ‘=’.

=$C4=”Saturday”

The formula above means “If C4 is ‘Saturday’, format the cell“.
※We only add the ‘$’ symbol before column ‘C’. The column is fixed but the rows will shift.

③ Click information on relative and absolute cell reference, click’Format’ on the bottom right to set the changes (color of cell etc.).

 

(4) When a new dialog box appears, select the color of cell (Saturday = light blue).

① Select color from the color list.
② Click [OK] to complete.

 

(5) If we click [OK] in (4), it will go back to the previous dialog box and we will see the cell color in the ‘Preview:’. Check that the correct color is previewed and click [OK] to finish.

 

(6) The cell color of ‘Saturdays’ in column C has been changed to light blue.

Results of conditional formatting)

 

POINT!

In the table above, only the cells in column C is colored in. It might be easier to see when the color for column B~E are also colored in together.
In this case, highlight the whole table (B4:E34) before formatting.

Selected range) 

 

Results)

 

Example of use 2) Change the cell color for every Sunday・holidays

Now that we have changed the cell color for every Saturday, we will move on to Sunday and holidays.
The set up is mostly the same, so this time we will start where the dialog box appears.

 

(1) Change the cell color for every Sunday

① Input formula that indicates “If C4 = ‘Sunday’…”.

=$C4=”Sunday”

② Click ‘Format’ and select orange from the [Fill] tab.

 

(2) Change the cell color when something is written in column D (=when not empty).

① Input formula that indicates “If D4 is not ’empty’…”.

=$D4<>””

② Click ‘Format’ and select orange from the [Fill] tab.

 

(3) Results

Every Saturday・Sunday・holiday has been colored in.

 

Change the color of cells with dates before today

When using a calendar in Excel, there might be times when we ‘want to change the color of all cells with dates before today’. Conditional formatting makes this possible.

On the right hand of this table we added ‘Today’s date’ and changed column B from ‘month/day’ to ‘day’ only. (the cells nonetheless must be in ‘date’ format in order to make this conditional format work).
We will use this table to set up a conditional format when ‘the date changes, the cells with dates in the past will be colored in automatically‘.

 

Example of use) Change the cell color of all dates before today

(1) Highlight cell range to create a format for, [B4:E34].

 

(2) Set up conditional format.

① From ‘Select a Rule Type:’, select ‘Use a formula to determine which cells to format‘.
② In ‘Format values where…’, input the following formula beginning with ‘=’.

=$B4<$G$4

※The formula indicates that ‘when cell B4 is smaller than cell G4. Fix only the column number for cell ‘B4’ and both for ‘G4’.

③ Click ‘Format’ and select gray from the [Fill] tab.
④ Click [OK] to complete.

 

(3) Results

All cells with dates before ‘2016/1/6’ (cell G4) has been colored in.

 

Set up multiple conditions with conditional formatting

The table below shows scores of a Japanese national examination.
A pass is ‘300 or more points for each field’, so we would like to have cells with lower scores colored in with gray.
The conditions to fail are the following:

① Less than 300 points in the Strategy field
or
② Less than 300 points in the Management field
or
③ Less than 300 points in the Technology field

There are 3 conditions that would lead to a fail (=cells colored in with gray).
By using the OR function and the AND function, we can create a conditional format with multiple conditions.

 

Example of use) Conditional formatting with multiple conditions/OR function

(1) Follow the steps below:

① From ‘Select a Rule Type:’, select ‘Use a formula to determine which cells to format‘.
② In ‘Format values where…’, input the following formula using the OR function.

=OR($C5<300,$D5<300,$E5<300)

③ Click ‘Format’ and select gray from the [Fill] tab.
④ Click [OK] to complete.

 

(2) Results

The cells of the 6th row and 8th row which do not meet the requirements to pass have been colored in.

 

Copy a conditional format/Relative・absolute cell reference

Conditional formats once set up can be copied to other cells.
However, we must be aware of relative and absolute cell reference when copying. → For more information on relative and absolute cell reference, click here

We will explain how to copy formats using the same example of ‘changing the color of all cells with dates in the past‘.

 

Example of use) How to copy conditional formats

(1-1) On the 4th row (arrow), a conditional format is set up which colors in the cell gray if the date is in the past.

 

(1-2) Below is the format set up for the 4th row:

The formula states that ‘if cell B4 is smaller than cell G4’, format the cell range selected.

=$B4<$G$4

We must check the formula especially in terms of absolute cell reference as there are fixed values in this formula.

Firstly, cell B4 has its column fixed with ‘$’ meaning that if copied or Autofilled, the column will not shift but the row will.

Secondly, cell G4 has both values fixed meaning that nothing will be changed when copied or Autofilled to other cells.

Keeping this in mind, let us look at the steps to copy conditional formats.

 

(2) Drag (Autofill) cell range [B4:E4] downwards (in this example we will drag down until the 10th row).

 

(3) When dragged down, the original contents in the 5th~10th row will be overwritten. Do not panic and follow these steps:

① Click the ‘Autofill option’ that appears when copied or Autofilled.
② Select ‘Fill Formatting Only’ from the menu.

What is an Autofill option?

An Autofill option appears at the bottom right when a cell is copied or Autofilled. It allows us to change the content of the Autofill.

 

(4) The original content from the 5th row~10th row is back, and only the format (conditional format) is copied.

Like this, Autofill and copying cells allows us not to only copy values/texts but (conditional) formats as well.
Be aware of relative and absolute cell references when copying.

 

Link conditional formats with drop-down lists

We can also link conditional formats with drop-down lists, changing the content of the format according to what is selected in the drop-down list. → For more information on drop-down lists, click  here

Below we see a members list for a Sports club. A drop-down list is created in column D.

Drop-down list) → Can select either ‘○’ or ‘×’

We would like the text color to change when the admission fee payment is ‘×’.

 

Example of use) Link with drop-down list

(1) Highlight cell D4 and follow the steps below:

① From ‘Select a Rule Type:’, select ‘Format only cells that contain‘.
② In ‘Format only cells with:’, select the following:

[Cell Value] + [equal to] + [× (type)]

The above means “if cell D4 is equal to ‘×’…”.

③ Click ‘Format’ and select red from the [Font] tab.

Click [OK] to complete.

 

(2) Results

When we select ‘×’ from the drop-down list, the text changes to red.

 

Link conditional formats with check boxes

We can link conditional formats with check boxes as well.

What is a check box?

A check box is a box that looks like □ that we can check or remove check.

 

Below is a list of members of the diet course in a Sports club.
We would like the whole row to be colored in with gray if we the check box in column E is checked.

 

Example of use 1-1) Prepare to link with check box

Before anything, we must change the settings of the check box.

(1) Right click on check box to link, and select ‘Format Control’ from the menu.

 

(2) When the dialog box appears, input the following:

① Input F4 in ‘Cell link:’ → $F$4
② Click [OK] to finish.

 

(3) What the results look like when we do the same for the rest of the rows:

If we check the check box, a ‘TRUE’ and if no do not, a ‘FALSE’ appears in column F.
Cells in column F are necessary in linking the check box with conditional format.

 

Example of use 1-2) Link conditional format with check box

(1) Select cell range to change color (for this example we will highlight [B4:E4]) and set up the format.

① From ‘Select a Rule Type:’, select ‘Use a formula to determine which cells to format‘.
② In ‘Format values where…’, input the following formula:

=$F$4=TRUE

The above means “if cell F4 is ‘TRUE’…”.

③ Click ‘Format’ and select gray from the [Fill] tab.

Click [OK] to complete.

 

(2) Results

If we check cell E4, the whole row is successfully colored in with gray.

 

POINT!

There are times when we do not want to display the text in column F.
In this case, we can change its text color to ‘white’ (so it blends in with the background).

The white text will not appear when printed either so it is a good tactic to remember.