Practical Excel skills sharing: Use 'conditional formatting' and 'function formula' together

In the previous article " Practical Excel skills sharing: "Data validity" can be used like this! ", we learned 3 tips to make data validity more efficient. Today we will talk about conditional formatting with function formulas. Through two examples, we will introduce how "conditional formatting" and "function formulas" are used together. Come and learn!

Everyone knows how to play conditional formatting, but I believe you don’t know how to find the maximum value of each row in 100 rows of data at the same time and mark it. Today we will use 2 examples to learn how to use conditional formatting with function formulas.

Example 1 [Highest score color marking for multi-row data]

Speaking of using conditional formatting to mark the maximum value, I believe everyone knows, This can be accomplished by using the items in the "Project Selection Rules" as shown in the figure below, but this operation is relatively limited. If we have 100 rows of data, what if we want to mark the highest value of each row at the same time? Let me explain to you the formula to solve this problem.

Recently, we will conduct assessments on four subjects including excel, word, ppt, and comprehensive. The student test scores are recorded in the table. Now you need to fill in the cells with the highest score of each student with green.

Operation steps:

(1) Select the D2:G13 data area and click [Conditions] in the [Start] tab Format] and select [New Rule].

(2) Open the [New Format Rule] dialog box and select the rule type [Use formulas to determine the cells to be formatted]. Enter the formula =AND(D2=MAX($D2:$G2),D2"") in the editing rule description below. Click [Format] to set the cell fill color to green.

Function formula analysis

(1) D2=MAX($D2:$G2) determines whether D2 is equal to $D2: The maximum value in the $G2 area, returns TRUE if they are the same, otherwise returns FALSE. The $ symbol is added to the column coordinates to indicate an absolute reference, so that the data will be judged in columns D-G. The row coordinates are relative references. Every time you move down one row, the formula automatically changes to the corresponding row coordinates.

(2) D2"" means that D2 is greater than or less than a blank cell, then TRUE is returned, otherwise FALSE is returned.

(3) The AND function determines whether the above two conditions are both true. If both are true, it returns TRUE and the cell will be filled with green.

After editing the formatting rules, click [OK] to enter [Conditional Formatting Manager] (Note: Conditional Formatting Manager can be understood as storing the set conditional formatting)

I saw the conditional formatting just set in the manager. The area where the current conditional formatting is applied is fixed to the $D$2:$G$13 cell range. If our data range changes, just modify the application cell range.

Click [Apply]-[OK] in the above picture to complete the highest score color marking. The final result is as follows.

You can try changing the formula and color-coding the lowest score!

Example 2 [Contract Expiration Reminder]

How to set a contract expiration reminder in the form? I believe that all partners in contract management have such needs. The simplest way is to use the "Occurrence Date" in "Conditional Formatting" to display the cells containing the data that is about to expire in a special color.

#But this method only has the following options. What if I want to find out the contracts that will expire within 5 days? You need to use function formulas.

The following table is the labor contract record table for employees of the Marketing Department of China Unicom Group Corporation. The table records the employee's entry date and contract termination date. Now we need to use conditional formatting to automatically color-code reminders for employees whose contracts are about to expire.

Operation steps:

(1) Select the H2:H13 data area and click [Conditional Formatting] in the [Start] tab . Select [New Format Rule], open the [New Format Rule] dialog box and select [Use formulas to determine the cells to be formatted]

(2) In the editing rule description In the input setting condition, we enter =DATEDIF(TODAY(),H2,"d")

Maybe many friends are not very familiar with the meaning of DATEDIF(TODAY(),H2,"d")

Let’s use a table to briefly introduce this function:

As you can see, we use the entry date and the current date (today ()Return the current date) for comparison, using "y", "m", and "d" as calculation units respectively, and return the number of years, months, and days between the two dates in turn. Note: The first parameter of the function formula is a small date, and the second parameter is a large date.

After understanding the DATEDIF function, it is not difficult for us to understand the formula DATEDIF(TODAY(),H2,"d")

Today we shared with you through 2 examples how to combine formulas in conditional formatting to judge and label data. There are many related examples in real work, such as:

1. Repeated data can be marked through the =COUNTIF($H$3:$H$13,H2)>1 function.

2. Use the =VLOOKUP($H2,$M:$M,1,0)"#N/A" function to directly color the matched cells. You can try it out!

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: Use 'conditional formatting' and 'function formula' together. For more information, please follow other related articles on the PHP Chinese website!