servebolt
Awario
Learning Office

Create Check Box to find conditional data in Excel

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.

For example:

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.

Create Check Box to find conditional data in Excel | Learning Office

#first. Steps to create Check Box to find conditional data in Excel

+ Step 1: Start your Microsoft Excel => then select File => choose More… => choose Options

Create Check Box to find conditional data in Excel | Learning Office
Create Check Box to find conditional data in Excel | Learning Office

+ 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.

Create Check Box to find conditional data in Excel | Learning Office
Create Check Box to find conditional data in Excel | Learning Office

+ Step 3: Choose Developer => choose Insert => choose Create Check Box to find conditional data in Excel | Learning Office
Create Check Box to find conditional data in Excel | Learning Office Check Box (Form Control)

Create Check Box to find conditional data in Excel | Learning Office
Create Check Box to find conditional data in Excel | Learning Office

+ Step 4: Click in any cell in the worksheet to make the Check Box insertion location

Create Check Box to find conditional data in Excel | Learning Office
Create Check Box to find conditional data in Excel | Learning Office

+ Step 5: Right-click on the newly created Check Box => select Edit Text => enter Hiện/ Ẩn

Create Check Box to find conditional data in Excel | Learning Office
Create Check Box to find conditional data in Excel | Learning Office

+ Step 6: Right click Hiện/ Ẩn => choose Format Control

Create Check Box to find conditional data in Excel | Learning Office
Create Check Box to find conditional data in Excel | Learning Office

+ 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 OK

Create Check Box to find conditional data in Excel | Learning Office
Create Check Box to find conditional data in Excel | Learning Office

+ Step 8: Select block of cells F2:F5 => choose Home => choose Conditional Formatting => choose New Rule …

Create Check Box to find conditional data in Excel | Learning OfficeCreate Check Box to find conditional data in Excel | Learning Office

+ 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 Format …

Create Check Box to find conditional data in Excel | Learning Office
Create Check Box to find conditional data in Excel | Learning Office

Step 10: Choose Fill => choose the color you want => then select OK => choose OK continue.

Create Check Box to find conditional data in Excel | Learning Office
Create Check Box to find conditional data in Excel | Learning Office

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..

Create Check Box to find conditional data in Excel | Learning OfficeCreate Check Box to find conditional data in Excel | Learning Office

#2. Epilogue

Before I stop writing, I would like to have a couple of explanations about the formula công =AND($F2<5,$J$1=TRUE)

  • 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 F and starting at F2 so 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.

Content Protection by DMCA.com
Back to top button