Like and see, form a habit; The stone of another mountain can attack jade.
Wechat search [Yixin Excel] to pay attention to this different we-media person.
In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.
The basic Excel article I want to share today is “Block 5 – Conditional Formatting,” but “Apply table formatting” and “cell style” are easy to click on, and we’ve covered them here, so today I’m going to focus on conditional formatting.
Conditional formatting is to highlight important data in data bars, levels, ICONS and other formats according to certain conditions. The main function of conditional format is to display the cell area that meets the expected condition as a pre-set format, so the complete conditional format is “condition + format”, which is a rule. In Excel conditional formatting, there are five types of rules provided by default: highlight cell rules, most/last rules, data bars, levels, icon sets, and custom rules.
1. Highlight the cell rule
Conditions such as greater than, less than, between, equal, text inclusion, date of occurrence, and duplicate values are highlighted. And they’re pretty easy to do. Most of them are just input a value, select a format, and I’m going to pick two of them.
1.1, between
For example, we want to see the salary between 3000~5000 employees who? To bring up the in-between rule dialog box, simply select the Salary data area and click “In-between” under “Highlight cell Rules” in the “Conditional Format” drop down from the “Styles” command group under the “Start” TAB.
Then enter 3000 and 5000 respectively in the between rules dialog box, and then you can choose the styles provided by default and you can choose to customize your own Settings. The effect is as follows:
1.2. Repeated values
This function can quickly find out all the repeated values in the selected cell area, which is especially convenient for repeating, and can also quickly mark the unique values in the selected cell area.
For example, if we want to see if there are any duplicates in the payroll, we can select the name column data area, and then click “Start” → “Style” → “Conditional Format” → “Highlight cell rule” → “Repeat Value” to bring up the repeat value rule dialog box.
2. First/last rule
The top 10, top 10%, bottom 10, bottom 10%, above average and below average conditions are highlighted.
For example, a class exam, we want to see who is behind, below the class average score, we just need to select the score column data area, click “start” → “style” → “conditional format” → “top/last rule” → “Below average” bring up the below average rule dialog box.
3. Data bar
The data bar rule is to visualize the data graphically. By default, the largest data bar will fill the entire cell. You can also choose “gradient Fill” and “solid Fill” as well as various colors.
For example, let’s use the data bar to show the results of middle school students in the last example. First, select the score data area, and then click “Start” → “style” → “conditional format” → “data bar” → “Gradient fill” → “light blue data bar”
4, color gradation
The color level is mainly to facilitate the display of data size effect. The darker the color is, the bigger the data is, and the lighter the data is, the smaller the data is. There are three colors and two colors, not several colors, but several colors, because the color will be darker and lighter according to the data size.
In addition, for the three colors, different colors represent the maximum, middle and minimum values respectively. The two colors represent the maximum value and minimum value respectively.
For example, we have a class of students each monthly test results color scale display, see how the distribution of scores?
Select the score data area, then click “Start” → “Style” → “Conditional Format” → “Color levels” → “Gradient Fill” → “Green-Yellow-red Levels”.
5. Icon set
The icon set provides default ICONS for direction, shape, tag, and rank.
Icon set application scenarios are still many, such as my previous article also made a tracking work schedule with marks, interested friends can turn over the previous article.
Today we’re going to focus on the basics, so let’s take a simple example. For example, we select students’ scores, and then apply markers, with a mark for high scores, an exclamation mark for medium, and a cross for low scores. Select the score data area, then click “Start” → “style” → “conditional Format” → “Icon set” → “Mark” → “Three matches (with circles)”, the effect is as follows.
6. Customize rules
In addition to the five preset rules, custom rules can be flexibly applied according to your actual requirements.
To create a custom rule, just click New Rule under Conditional Formatting.
Create a format rule as follows:
Today we’re going to focus on the basics and we’re going to talk about custom rule applications separately.
Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.
If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.
This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.