Feature Conditional Formatting – conditional formatting of data is one of the great features of Excel that you should know.
This feature will make the data in the spreadsheet much more intuitive and vivid, thereby making it easier for us to read, understand and analyze the data contained in the spreadsheet.
Unlike normal formatting features (Font, Size, Increase Font Size, …) this feature will automatically detect cells that satisfy the condition => and then proceed to format it.
It’s great but when you want SHOW/HIDE nice TOGGLE it is with the Ribbon, the operations are quite complicated and cumbersome. So the most used solution method is to use Check Box.
Specifically, in this article, I will guide you to create and customize to be able to control the work show/hide nice toggle Conditional Formatting through Check Box with just one action.
Or to put it more simply, we will Create a Check box that has the ability to search and conditionally format data extremely quickly.
Given the data table as shown below (the data in the actual table will be much larger). Please format the column with the average score in orange, but do not format the whole column, but only format the cells with the average score below 5.
#first. Steps to create Check Box to find conditional data in Excel
+ Step 1: Start your Microsoft Excel => then select
File => choose
More… => choose
+ Step 2: In the dialog box
Excel Options => choose
Customize Ribbon => tick
Developer => choose
OK to add the Developer tab to the Menu bar on Excel.
+ Step 3: Choose
Developer => choose
Insert => choose
Check Box (Form Control)
+ Step 4: Click in any cell in the worksheet to make the Check Box insertion location
+ Step 5: Right-click on the newly created Check Box => select
Edit Text => enter
+ Step 6: Right click
Hiện/ Ẩn => choose
+ Step 7: Dialog box
Format Control appears then select the card
Control => checkmark
Unchecked => at
Cell link Please enter the absolute address of the cell to be linked..
=> In the scope of this article I will enter
$J$1 => and choose
+ Step 8: Select block of cells
F2:F5 => choose
Home => choose
Conditional Formatting => choose
New Rule …
+ Step 9: Next, you choose
Use a formula to determine which cells to format => and enter the formula
=AND($F2<5,$J$1=TRUE) => then choose
Step 10: Choose
Fill => choose the color you want => then select
OK => choose
Yes, so the process Create a Check Box to control Conditional Formatting in Excel Accomplished. From here on when you want show/hide nice toggle Conditional Formatting, you just need to select the Check Box Show/Hide is to be..
Before I stop writing, I would like to have a couple of explanations about the formula công
- AND function is required function
- The number of Logicals in the AND function is optional. Can be one, two, three, … or nested AND, OR functions.
- Here because it is necessary to format the average score column ie column
Fand starting at
F2so we’ll refer to the cell
F2, but must be a column-fixed composite reference
- The final logic should be the absolute address of the cell associated with the Check Box
In addition, when you want to format a line, please refer to the mixed address with the line. Ọkay ! Goodbye and see you in the next posts.