Excel conditional formatting refers to the operation technique of highlighting and labeling certain data by setting special filter conditions for the selection range data without changing the order of the original form. So, today let's talk about one of the items in conditional formatting, highlighting cells.
oneRelated Notes:
1.Location of the function:
Under the Start tab, in the "Conditional Formatting", click on it and select "Highlight Cell Rules" to display the relevant options.
As shown in Figure 1).
2.Descriptions of each option:
Greater than, less than, between, and equal to: It means that the data that is greater than, less than, between, or equal to certain values is highlighted in the selected data range.
Text Contains: It means that data containing certain text values is highlighted within the range of the selected data.
Date of Occurrence: This means that the dates that match the filter criteria are highlighted in the selected Date Format Data range.
Duplicate Value: This means that data with the same value is highlighted in the selected data range.
Other rules: It means that in the selected data range, special selection rules are set to highlight the data that meets the conditions.
As shown in Figure 2).
2. Operational focus
1. Select the correct range according to actual needs:
In our daily work, we use the "Highlight Cell" operation, which is often for "part of the data in the form", so before performing the operation, we must first select the correct "target data range", which is also the most important thing to show the results in the future!
If you have little data, we can hold down the Left Mouse Button and drag to select the relevant range. (Figure 3).
If you have too much data, you can select "First place of target data" in the open form, and then hold down shift and click "Last of target data" to quickly select the regional range of data. (Figure 4-5).
If you are particularly familiar with the composition of the form, you can use the method of "click on the target column or target row" to select the scope. (Figure 6-7).
Note that if you are not familiar with the form, please do not use this method to avoid "range error"!
2.For the data type, select the display rule:
For "numeric-only" data, the emphasis is more suitable for "greater than, less than, between, equal to". (See Figure 8-9).
For text data for "Chinese characters, letters, and multiple mixed classes", highlighting of "text contains" applies. (Figure 10-11).
For date-based data, highlighting of Date of Occurrence is more appropriate, but this feature is generally less common. (Figure 12-13).
Duplicate values", which is suitable for "any type" of data, and is used most frequently in his daily work. (Figure 14).
The display of "other rules" is based on the situation, and the follow-up will explain the operation skills separately for you according to the specific problems. (Figure 15).
Three. Detailed explanations of specific operations
After selecting the target data range and clarifying the data type, you can perform the relevant operations.
First of all, let's introduce the operation of "greater than, less than, between, equal to", we take "greater than display" as an example, as follows:
In the form that has been selected, click on the conditional formatting, select "Highlight cell rules", and then select "Greater than", the system will pop up the "Filter Condition Dialog", the left side of the box is "Input Display Condition", and the right side is "Selective Display Effect", we can enter and select according to our own work needs, and after confirming that it is correct, click Enter to complete the operation!(Figure 16-18).
Please note that if your raw data has set display conditions such as "Font Color" and "Background Color", then after the operation is executed, the system will overwrite the set "Display Effect", and under the premise that the "Highlight Rule" is not canceled, any secondary operation that conflicts with the selected "Display Effect" will be "displayed at the bottom", and other effects will only be displayed after the setting is canceled. (Figure 19-20).
The operation of "text contains" is as follows: in the selected data range, click on the conditional formatting, find the text contained in the highlight, after clicking, the "filter condition dialog box" will pop up, the same is the "input display condition" on the left, and the "selective display effect" is on the right, we enter the corresponding information, hit enter, and the operation can be completed. (Figure 21).
Please note that the "text contains" in the system can only recognize continuous text values, for those "interrupted text data", the system will not be able to recognize the content contained, and when entering and filtering, be sure to pay attention to whether the text data information contains "spaces", if there are "spaces", it will also affect the final "highlight", please be careful!(Figure 22-23).
The previous operation is the same as the above, the difference is that in the pop-up dialog box, the left side becomes "selective display condition", and the right side is still "display effect", when operating, please note that the selective display condition in the system is based on the current system set time and logical arrangement, for example, today is July 7, 2021, and then select "yesterday" After that, the system will mark July 6, 2021, and when we open the form tomorrow, the system will mark July 7 according to the passage of time, so the "occurrence date" function in the highlight is actually real-time, and will not be "really fixed" because of our saving, and for this reason, this item is not particularly common in actual work (as shown in Figure 24-26).
Finally, there is the highlighting of "Duplicate Values", which is the most common feature in daily work and operates as follows:
First of all, we still have to box the correct data range, and then find the duplicate value option in the conditional format, after clicking, there will still be a dialog box that we are familiar with, the left side is "selective display conditions", the right side is still "display effect", on the left we can follow the work requirements, select "repeat" or "unique", here take repeat as an example, after confirming that it is correct, click OK to complete the operation. (Figure 27-28).
It should be noted that the system will only identify the location of the "duplicate values", but will not mark the correspondence between the "duplicate values" for you, so if your "after plagiarism" data display is too large, you may also need to use the "filter" function for secondary identification. (Figure 29).
4. Undo the highlight
After the "highlight" is executed, you can "cancel or retain" the format according to the needs of subsequent work, and the cancellation operation is as follows:
Click on the conditional formatting in the start, select Clear Rules, and then select "Clear Rules for the entire worksheet", and then click on the highlight in all forms to cancel all settings. (Figure 30).
5. Special instructions
You can perform multiple operations on the same data before canceling the rule, but each time the highlight is superimposed on the previous effect display before the rule is canceled, so be sure to pay attention to the order in which the data is operated.
The above is highlighted, the most commonly used operation skills, have you learned?
If you want to know more about this issue, please click on the link below to view!
Excel Conditional Formatting Tips "Part 1: Highlight Cell Rules".