Shulou(Shulou.com)11/24 Report--

Hello, everyone. I am an Excel teacher who can design forms.

Today we talk about "how to systematically learn conditional format". From 4495 Q & A records, I screened and summed up several directions of learning conditional format.

This article suggests that everyone collect, the follow-up can be used as a conditional format learning route, very comprehensive!

According to the usage scenarios of the conditional format, it can be roughly classified as follows:

The basis of ❶ conditional format

❷ data visualization

❸ highlight mark

❹ unit formatting

01 conditional format basis first of all, understand the function of conditional format, that is, according to the cell data, automatically mark the cell style, such as fill color, border, font color and so on.

There are two keys to understanding the conditional formatting function:

❶ automatic marking style. Will overwrite the color you filled manually.

❷ rows and rows are locked. The cells quoted in the judgment of conditional format, like the principle of the formula, also have the problem of row and column locking.

If you do not understand these two points, it is easy to make mistakes, such as:

Why is ▋ pasted red?

Questioner: happiness at the next stop

Respondent: Mr. bin Laden

This is the function of automatic tagging without knowing the conditional format.

Delete the conditional formatting and mark the style of the duplicate value.

▋ sets the conditional format, why is there no highlight if it meets the condition?

Respondent: Mr. bin Laden

At a glance, in the conditional format, the references to the judging cells are written to death, so the data are judged according to the first value, so the display is wrong.

Do not understand the row lock, this kind of problem is very difficult to solve.

02 data visualization conditional format is a simple and easy-to-use data visualization method. The two most commonly used functions are:

❶ data bar

❷ icon set

These two functions are relatively simple, easy to learn, and there are no in-depth skills. It mainly includes the following types of questions:

❶ data bar foundation

❷ bar length

❸ data bar beautification

❹ icon set settin

I mainly list a few high-frequency mistakes, everyone pay attention to avoid the pit.

How does the length of the ▋ bar match the actual value?

Respondent: Mr. bin Laden

This is a common problem, the data bar defaults to the maximum value in the selection, and the setting occupies the entire cell. So there will be a problem of 5.71% full space.

You can set the rules for conditional formatting, and set the maximum value to 1, which is fine.

How to hide the number of the data bar in the ▋ conditional format?

Respondent: Mr. bin Laden

Edit the conditional format, and then check "Show only data bars".

The same is true for chart sets!

How does ▋ display different percentages in stars?

Respondent: Mr. bin Laden

This is not going to happen.

The chart set in Excel has stars, but it can only display three states and cannot be adjusted in real time according to the size of the number.

03 highlight marking this is the most widely used scenario of conditional format, which is used to quickly mark and check data. It can be divided into the following scenarios:

❶ duplicate value marker

❷ date check

❸ text comparison

❹ digital comparison

Similarly, let's list a few questions, which you can understand at a glance.

▋ duplicate value marker

Questioner: chanting

How to make the same value be marked automatically?

Respondent: Mr. bin Laden

Using conditional formatting, you can automatically mark duplicate values.

A little bit of complexity marks the duplicate value, which will be judged based on multiple columns.

For example, three different companies, how to find the same content?

▋ date reminder

Questioner: Gao

It's time to remind me 3 days in advance. What should I do?

Respondent: Zhang JW

The conditional format combines with the IF function to judge the date and mark the color.

The formula is as follows:

Date judgment and marking is a very high-frequency use scenario in conditional format, and similar problems include:

How does the ❶ conditional format display in such a color?

❷ this conditional format reminder, why is it wrong?

❸ wants to realize that the font automatically turns red the day before the schedule occurs. How to do it?

How does ❹ make less than today gray?

Can the data in ❺ time format be conditionally formatted?

How does ❻ highlight eligible data?

How does ❼ highlight plans for April 10, plus or minus 10 days?

How does ❽ set the automatic filling color of the planet on Saturday and Sunday?

How can ❾ automatically highlight Saturdays and Sundays on time sheets?

After the ❿ colleague moved the form, the original automatic birthday reminder is gone, how to set it?

How to quickly mark Saturday and Sunday with ⓫

Why does ⓬ become a red reminder before it expires?

⓭ conditional format, only find out ETA = Saturday and Sunday, how to write the formula?

⓮ 3 days in advance to remind the time is up, what to do?

How ⓯ sets the date to be equal to today, show it with shading

Why does ⓰ show color on one line if it is less than 10 days?

⓱ I would like to ask the conditional format to set the occurrence date is less than today, red fill, how to write the formula?

▋ text comparison

Questioner: Hebei-Management-improve efficiency

Unfinished automatic marking of red fonts, how to achieve?

Respondent: Lele

Use conditional formatting, include in text, or customize rules with formulas.

Respondent: Mr. bin Laden

Text comparison class is prominent, the difficulty is irregular text extraction and matching, if the content of the cell is very standardized, the use of conditional format [highlight cell rules] can be easily done.

And text extraction, matching in detail, is a very large field of knowledge, I will not repeat it here.

▋ digital comparison

It is usually used to compare and judge the actual value and the target value, and mark it dynamically.

Questioner: anonymous

If > 0, the cell is set to a color

Respondent: Chen Honghong

Yes, you can use the conditional format + function formula.

04 the last category of cell format beautification is to assist batch completion of cell style beautification. The core focus is [batch], to give a few examples.

▋ cell style beautification

Questioner: fireworks

How to fill in color every 3 lines?

Respondent: Mr. bin Laden

Use the COUNTA function to determine that if the whole line is blank, the color is not marked. If a non-empty cell is included, the color is marked. Then set the conditional format.

Similarly, there are Gantt chart drawing, intercolumn filling colors, and so on, which can be done in batches in conditional format.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: bin Laden Dony

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.