How to Prefer Manual Fill to Conditional Formatting Excel (Excel)

Conditional formatting is a very useful feature that automatically fills in or changes formatting based on specified conditions. However, many of you may have experienced the problem that conditional formatting takes precedence over manual filling.

In this article, we will show you a trick that allows you to use conditional formatting and also manual fill.

Conclusion.

By enclosing the original conditional expression with a function in red, as shown below, the manual fill will take precedence over the conditional formatting when any text is typed.

=AND(original conditional expressionCondition cell="")

Here are some specific examples

Conditional formatting takes precedence over manual fill.

Let's check the previous figure again.

Dates are entered in "Column B," and the dates of attendance for each of "Mr. A," "Mr. B," "Mr. C," and "Mr. D.

The "Saturday and Sunday" rows are automatically set to gray by conditional formatting, and the workdays are manually filled in yellow.

The weekday rows are filled without any problem, but when the workday arrives on Saturday or Sunday, the gray conditional formatting takes precedence and I cannot fill the rows with any color other than gray. But is there any way to somehow fill "Mr. B's" Saturday and Sunday workdays with yellow as well?

Possible by adding multiple conditions to conditional formatting.

In fact, this can be done by making the formula for the condition multiple conditions.

Let's start with the basic conditional formatting.

The figure below is prepared before formatting the figure shown above.

With the entire range for which you want to set a condition (in this case, cells C3 to F35) selected, go to the "Home" tab and click "Conditional Formatting" and then "New Rule".

From the menu that appears, select "Use formulas to determine which cells to format" and click on the input field below.

Enter the following conditional expression in the input field.

=WEEKDAY($B3,2)>=6

Once you have entered the function, then click on the "Format" button.

Select gray from the "Fill" tab and click the "OK" button.

*You can choose your own color.

Confirm that the formulas and formatting have been set and click the "OK" button.

The Saturday and Sunday rows are now filled in gray.

Next, I selected all the cells marked "Attendance" and manually filled them yellow, as shown in the figure below. I had properly selected and filled in Saturdays and Sundays as well, but the gray conditional formatting took precedence and did not fill in the yellow.

So let's change the formulas for conditional formatting.

With a cell selected anywhere in the range where you set the condition this time, go to the "Home" tab, "Conditional Formatting" and click on "Manage Rules".

Double-click on the rule you want to change.

Then the screen where you just entered the formula for the condition will appear. Change the formula as follows and press the "OK" button. The formulas are enclosed in the formulas introduced at the beginning of this document. Here, the designated cell is set as "C3" because the top left-most cell to which the conditional formatting is applied is "C3.

=AND(WEEKDAY($B3,2)>=6,C3="")

Date is Saturday or Sunday" in the condition to fill in grayand the cell is emptyAdded the condition "The

Confirm that the formulas have been changed, although not all are shown, and click the "OK" button.

As shown in the figure below, the fill-in for workdays is prioritized even on Saturdays and Sundays.

Conclusion

By using the AND function to create multiple conditions for conditional formatting, it was possible to disable conditional formatting and display manual conditions first.

This time, we added the condition "must be blank," so if no text is entered, the gray conditional formatting will be displayed first, as shown in the figure below.

If you do not want the text to appear, you can disable the conditional formatting in the same way by typing a "␣" space instead of a letter.

There may be cases where you would like to express yourself differently from the conditions presented here.

In this case, the "AND function" was used to add the condition "if blank", but you can also use the "OR function" or, conversely, "if a character is entered" to find the condition you want to express.

If you want to express yourself in this way but it doesn't work, please let us know in the comments. We will also introduce the way to express it.