For the easiest and fastest analysis of data that changes from time to time Let's take a look at the functions of Excel that help, conditional formatting. When you finish this lesson, When the value is selected, the content changes You can make it appear in a chart format. If you are curious, please look at the end. The example files used in this lecture can be downloaded for free from the Oppa Doo homepage. Click the link below to download the example file I think it would be nice if we could proceed together. I'll go to the next page by pressing the start button. First, let's find out what conditional forms are. In the previous lesson, we learned about custom forms. After coming to the first sheet, let's select the rate of increase or decrease in the sales range. Right click and go to the cell format Or go to the cell format with the shortcut CTRL + 1 key. There is a custom form. Separated by a semicolon (;), Positive, negative, 0, when text I told you that you can format it. Or, select the net increase/decrease rate next to it. When you come to cell format When the value is greater than 0, or when the value is less than 0 You can apply a custom form by putting the condition in square brackets. You can only use up to two conditional formats using cell format And there are limitations in that various conditions cannot be used. So apply multiple conditions or more various types of conditions, For example, to highlight only the top 3 items, Emphasize values above average Use conditional formatting when you need to apply formatting in various conditions, such as. Usage is very simple. Suppose you want to highlight the top three items among debt ratios. Excluding the total range, I will select the debt ratio range. Home-Go to Conditional Formatting. When you come to the top/bottom rules, there are'Top 10 Items'. It will be the top 3, not the top 10. So change the number 10 to 3. The top three items were easily obtained. Let's find out if it's right. Enter the RANK function. Office 365 users have dynamic array functions, so Enter two ranges as arguments. The ranking is automatically calculated. If you are not an Office 365 user, After entering the RANK function, enter the value. After entering the comma, I will select the reference range. After referencing with the F4 key, enter the function. Just fill the function automatically. This time, let's emphasize the value when it is smaller than the average. Go to the student list table. Let's select a key range. Conditional Form-If you come to the top/bottom rules, there is'below average'. Values below this average are automatically emphasized. Let's find out if this was done as well. We will get the average key with the AVERAGE function. And if the value of the average key minus the key is negative, it will be less than the average. Office 365 users are saved at once by selecting the range and then subtracting the average key. If you are not an Office 365 user, After entering the subtraction average key, Please refer to the average key with the F4 key. The value is calculated by auto-filling. Since values are difficult to see when they are positive and negative, we add a SIGN function to the front. You can see that it is easily separated by -1 and 1. You can see that conditional formatting is applied only when -1. It's also simple to check if conditional formatting is applied or not. I'll choose a range. Move from conditional formatting to rule management. If conditional formatting is applied, the conditional formatting rules are listed. After selecting the range without conditional expression, If you come to the conditional form rules management, the rules will not be displayed. This time, refer to other cells in real time Let's see how to apply conditional formatting. If the height is less than 135, I'll show it in red red. The range to which the conditional expression is applied is the key range. Conditional Formatting-When you come to a cell highlighting rule, there is a'less than' option. Specifies the value as a cell address. Values less than the cell's value of 135 are highlighted. Format to apply-Change the font style in the custom form. Then click OK to finish. Changing the value from 135 to 145 highlights students under 145. In the second example, if the body mass index is normal, That is, it emphasizes the color in the case of 20~24. Conditional formulas are applied to the range of body mass index. Conditional Formatting-After coming to the cell highlighting rule, I’ll select ‘between the following values’. Then, select the G9 cell as the first value. The second value is the H9 cell. The value between G9 cell value 20 and H9 cell value 24 is emphasized. In the same format as before, After moving to the custom form, Change the font. For simplicity, the body mass index is blue only. Third, let's change the cell background to yellow if the surname is Lee. Select a name range. After moving to the conditional form, if you look at the cell highlighting rule, there is a condition for embedding text. If you use the text inclusion condition, can you find the condition when the surname is Lee? It is not the correct answer. If you look at the name range, the last name may be Lee, In some cases, the names may include teeth such as'Kim, Lee,' and'Bang, Yi Soo'. If you use the text embedding condition, you cannot apply the correct conditional formatting. So this time, we will make a conditional form with a random formula. It is not difficult. I'll follow slowly. Range selected. Conditional Formatting-Add'New Rules'. If you look at the bottom, there is'Determine the cells to format using formulas'. I'll put the formula. The range to apply conditional formatting is selected. If you look at the top left, the currently active cell in the name box is B6 cell. After entering the equation in the conditional expression, enter the equal sign and then the LEFT function. The first argument is B6 cell. Gets the first letter of the cell, the last name. Compare the case where the last name is the same as the G12 cell. I'll check one more here. In the previous lecture, I explained about the cell reference method, If the $ is in front of the cell address, the cell address is I told you that it was fixed in place. If you look at cell B6, there is a $ in front of B, and a $ in front of 6. The scope to which we should apply the conditional form, B6, B7, B8... like this As you go down one space, you must apply conditional formatting to all ranges. In the B6 cell, the $ in front of 6 is deleted. The G12 cell, that is, the cell that comes in as a condition, should always be fixed in place. After coming to the form, change the cell background color to yellow. And if you click OK, your name will be highlighted only if your last name is Lee. If you change your surname to Kim, Kim is highlighted. Let’s look at the fourth. The fourth is if you are less than 145 and you are under 50. Previously, the name range, height range, body mass range, I chose only one column like this. This time, you need to apply conditional formatting to both the height and weight. Conditional Formatting-Add new rules. First, the range is selected. The active cell is the C6 cell. C6 cells are less than 145. If you look at the conditions, it means that you are less than 145 and you are under 50. Then, both of these conditions will be satisfied. Enclose the conditions in an AND function. Enter when the value of D6 cell next to C6 cell is less than 50. C6 cell is C7, C8, C9.. C6, C7, C9.. Because the value has to go down like this 6 Delete the preceding $ sign. In the D6 cell, also remove the $ in front of the six. After coming to the form, change the fill to green. It was emphasized only when the height was less than 145 and the weight was less than 50. Now everything is difficult. From now on, with just a few mouse clicks Let's look at three functions that can be easily applied in practice. The first is the data bar. There is a value, and the function of displaying the size of the value in the form of a bar. It's very simple to apply. I'll choose the range to apply. When you come to the conditional form, there is a'data bar'. Just choose the format you want. Then it's over. Further to this, the data bar can contain the minimum and maximum values. Body mass index is usually between 18 and 35. In the range, I will put the minimum and maximum values of the data bar. After selecting the scope, go to Conditional Formatting-Rule Management. Go to Edit Rule. The minimum and maximum values are set automatically, but I will change the values from automatic to numeric. Change the minimum value to 18 and the maximum value to 35. As the data bar changes, it looks better. The data bar can be very useful when creating reports in Excel. Let's look at an example. From the name, I will make the height, weight, and body mass index output according to the selected class. I will give you an explanation based on Office 365 users. If you are not an Office 365 user Enter the formula you wrote down next to it as an array formula. Just fill in automatically. Outputs when the value in the half range is equal to the selected value as a condition including the value. If you change the value, the value is updated. Select the body mass index range. Please choose generously. Conditional Form-I'll add a data bar. It was printed neatly. Going further from here, Let's sort the body mass index values in descending order. Before the FILTER function, use the SORT function Sorts the output values in descending order based on the 4th column. This time, we will look at sparklines. Sparkline is like the picture next It refers to the function of displaying the values in the range in chart format. To be precise, sparklines are not conditional, It is a very useful function when applied as a data bar. As with the data bar, usage is simple. Select the range to apply the sparkline. Insert-there is a sparkline. I'll select'column' here. The place to put the sparkline is selected up to J6:J14. Enter the desired data range from January to June. If you click OK, the scarf line is inserted. You can automatically display the minimum and maximum values, the last value, and the first value from the values. When you come to the Sparkline option, there is a display item. I'll choose the high point here. The maximum value is highlighted in red. If you change the color of the sparkline from blue to gray, You can also display it more cleanly. To delete the sparkline, after selecting the sparkline range, When you come to the Sparkline option, there is'Erase'. This is an icon set. Previously, in sales report creation by sales team, If you have any questions, please refer to the previous lecture on the upper right. In this lesson, I will just use it quickly. Select the body mass index range. Select the icon set from the conditional form. Because it will be classified as obese, overweight, normal, and underweight Here we select a shape with 4 values. Icon set was applied, Because the applied conditions are different from the conditions we will apply in the future, The condition needs to be corrected. Move from conditional formatting to rule management. After selecting the icon set, let's move on to editing rules. The first condition is when it is greater than 30. In that case, it is obesity. I will change the color of the traffic lights to red. If the value is greater than 24, it is overweight, so turn it yellow. Values greater than 20 and less than 24 Since it is normal weight, change the fill color to green. Finally, if not, it is time to be less than 20. In that case, the icon is replaced with a flag because it is underweight. Something is wrong. Change the value from Type to Number. Icon set has been applied. Lastly, let's take a look at the precautions when using conditional forms. In fact, conditional expressions don't change the values in a cell, It doesn't cause fatal problems to the files you are using. There is nothing to be very careful about. But what you need to know when using conditional formatting When applying conditional formatting over a wide range The cell reference method is used well. I chose the personnel team. We will put in a conditional form to highlight all rows in the selected department. In the video lecture on how to highlight the table by clicking the button, I have briefly explained. In this lesson, we will learn more in detail. The scope of applying conditional forms is from the department name to the annual salary. After moving to the conditional form, add a new rule as'New Rule'. The cell active in the selected range is the B4 cell. We will see when the value of B4 cell is the same as the department name. As before, the department name B4 cell is referenced by going down one space. As you go down one space, you should refer to cells B4, B5, and B6. Therefore, the $4 in front of the B4 cell is erased. This time, we will look at the case where the B4 cell moves sideways and right. The B4 cell is the same as the H4 cell's'Human Resources Team'. So conditional formatting is applied. I'll look to the right. Right is C4 cell. Originally, C4 and H4 cells should be compared, but there is a $ in front of B. I want to compare C4 and H4 by moving the C4 cell to the side. Because of the $, it is compared to the value in cell B4. So, if C4 cell is the same as the department name of B4 cell, conditional formatting is applied. The same applies to D4, E4, and F4 cells. If the value of cell B5 is the same, conditional formatting will be applied to the entire row. After coming to the form, change the fill to pale yellow. In the case of a human resources team, employees are emphasized. If you change the value, the value of the department is highlighted. How to use the cell reference method in the precautions when using the conditional expression described last If you are new to it, it may be difficult to understand at once. If you turn the video one or two more times or practice a few more times I think you can understand immediately. Please leave any questions you may have during this lecture in the comments below. If you liked this lesson, please subscribe and like it. Then I'll see you in the next lecture. Good-bye!
