Excel's conditional formatting function can quickly identify data key points by setting rules. ① Highlight specific values: If you mark cells above 5000 or below 1000, the operation path is selected as the selected area → "Start" → "Conditional Format" → "New Rule" → Set conditions and select colors; ② Use data strips and color scales to observe trends: the data strips compare the size of the value through length, and the color scale represents the high and low distribution with gradients. You can directly select the corresponding options to apply; ③ Find duplicates or unique values: Set colors through "Highlight cell rules" → "Repeat Values" to quickly identify duplicate or unique data, which is suitable for cleaning tables.

Excel's conditional formatting function is actually quite practical, especially when you need to quickly identify key points in your data. It can automatically change cell styles according to set rules, such as marking data above average, duplicates, or grading trends by color.

How to highlight specific values in conditional format

One of the most common uses is to highlight data that meets a certain condition, such as cells that exceed a certain value. The operation is very simple:

• Select the data area you want to format
• Click the "Start" tab → "Conditional Format" → "New Rule" → "Format only cells containing the following content"
• Set conditions, such as "cell value greater than 100"
• Then select a fill color or font color

For example, suppose you have a list of sales and want to mark all those over 5,000 in green, this method is very suitable. And you can set multiple rules, such as marking those larger than 5000 and smaller than 1000 at the same time, with different colors and clear at a glance.

Observe trends using data stripes and color scales

If you are not just looking for a few outstanding values, but want to look at the data distribution as a whole, then "data stripes" and "color scales" are very useful.

• Data strip: A horizontal bar of varying lengths will be displayed in each cell. The larger the value, the longer the bar, which is suitable for comparing the size of the number.
• Color scale: Usually a gradient color, from red to green, which means low to high

The usage method is also very simple. Just select the area, click "Conditional Format", and then select the corresponding option. This method is particularly intuitive when making monthly reports and ranking lists. You can see the trend without drawing charts.

Quickly find duplicates or unique values

Sometimes you want to check if a column of data is duplicated, such as customer name, order number, etc. At this time, you can use the "Repeat Value" in "Highlight Cell Rules".

• Select a column
• Conditional Format → Highlight Cell Rules → Duplicate Values
• Select a color and all duplicates will be visible immediately

Conversely, if you want to find only the unique value, you can also set it here. This feature is very effective for cleaning data, especially when dealing with tables exported from elsewhere.

Basically all this is it. Conditional formatting is not difficult, but it can save a lot of time if you use it well. The key is to choose the right rules according to your needs. Sometimes you may also need to match them with custom formulas, but in most cases the default options are enough.

The above is the detailed content of Conditional formatting in Excel. For more information, please follow other related articles on the PHP Chinese website!