How to use Checkboxes and Conditional formatting for a dynamic display of data (Video transcript)
In this worksheet we have two checkboxes, when we select contract all the contract data will be highlighted by yellow
and when we select full time all the full-time ones will be highlighted by green.
Our aim is to use checkboxes together with conditional formatting. so let’s create and associate these checkboxes exactly like these, First let’s copy this data in a new sheet to add the checkboxes, first go to the developer tab. If yours is not activated, right-click on the main tab and click customize the ribbon and check the box developer on the right button.
so you get the developer commands.in the developer ribbon, click insert tools then you will get the checkbox form controls. Click the checkbox and insert around here and copy it one more to change the name of the checkbox right click and edit text and from right click you will get format control here we should link the values to a certain cell say let’s link this to I6. we will do the same full time. right-click format control and then link it to I8.
If we want to change the color for the checkboxes, go to format and give what you love. and ctrl and click to select the checkbox.
Now we will do the conditional formatting. The idea is when this is true, all the contract in the data is selected and when it Full time is True, all the data associated the full time will be highlighted in Green.
Now let’s associate conditional formatting with these checkboxes. First, be sure to highlight all the data. To do that go to the main tab and conditional formatting and select new rules. and select a Rule type: We will select a formula to determine which cells to format. What we want is the first Column C is an equal Contract. so =C2=”Contract”
we want a $ sign in front of C2.
And we what the cell associated with the contract checkbox to be true also. So let’s include, AND and Cell I6. If both these are true, we can add a common color format.
To do the other formatting go conditional formatting and manage rules to copy the formula.
and click the new rule and the formula and change the contract to full time and change the I6 to I8 and format the color aligns with the checkbox color that is it great to highlight data.