As we use checkboxes in many cases, we many times need to filter out them based on their tick status. However, doing so is difficult as checkboxes are objects. If you are curious to learn about filtering checkboxes in Excel, this article may come in handy for you. In this article, we are going to show how you can filter checkboxes in Excel with elaborate explanations.
How to Filter Checkboxes in Excel: Step-by-Step Procedure
In the below article, we are going to demonstrate how you can add checkboxes in Excel and Filter them. In this process, we may need to alter the basic ribbon menu settings a bit.
Step 1: Enable Developer Tab
Before we delve into Filtering the Checkboxes, we need to add the checkboxes to the sheet first.
- We have the below subjects of which we will monitor the submission.
- In the beginning, we need to enable the Developer tab in the ribbon.
- To do this, click on the File command in the corner of the sheet.
- Then in the starting menu, click on Options.
- Then in the Excel Options dialog box, click on Customize Ribbon.
- Then on the right side of the menu list, notice the Developer check box.
- If it is not there, then you may need to add the Developer option from the left menu list.
- Now check the Developer option and click OK.
- Now you will see the Developer tab on the ribbon.
Step 2: Add Checkboxes from Developer Tab
As we have the Developer tab, there is now no obstacle to adding Checkboxes to the sheet.
- Now click on the Developer tab and then click on the Insert.
- Then from the dropdown menu, click on the check box icon as shown in the picture.
- Then there will be a drawing icon will appear on the sheet.
- With that icon, draw carefully the Checkbox limit.
- Try to match the edge of the cell with the draw icon.
- After carefully drawing out the Checkbox, click anywhere on the sheet.
- You will see your check box is present.
- Then right-click on the box and from the context menu, click on the Edit Text.
- Then remove all the text.
- The Checkbox will look something like the one below.
- Then again right-click on the Checkbox and from the context menu, click on Copy.
- Then select cell C5, and right-click again.
- Then from the context menu, click on the Paste icon.
- Then you will have the Checkbox pasted in cell C5.
- Repeat the same process for the rest of the cells.
- After copying the Checkboxes, the table will somewhat look like the one below.
💬 Note
The drawing of the Checkbox areas needed to be precise, matching the cell boundaries. Otherwise, when copied down in the cell, they could overlap and create visual irritation.
Read More: How to Add Checkbox in Excel without Using Developer Tab
Step 3: Link Checkboxes with Adjacent Cells
We can add a column stating whether the submission of the assignments is done or not. It can be done using the IF function.
- Select the topmost Checkbox and then right-click on it.
- From the context menu, click on Format Control.
- Then in the Format Object window, click on the Cell Link from the Control.
- Click OK after this.
- You will see that the cell is now linked with the check box.
- Repeat the same process for the rest of the cells.
- Now select cell E5 and enter the following formula:
=IF(D5=TRUE,"Submitted","Not Submitted")
- Then drag the Fill Handle to cell E12.
- This will fill the range of cells E5:E12 with the submission status of each student.
Read More: How to Link Multiple Checkboxes in Excel
Step 4: Create Table
Next, we will turn the data range into a table format, where we will have the Filter option by default.
- Then select the range of cells B5:E12, then from the Insert tab, click on the Table in the Tables group.
- After that, the whole range of cells will be converted to a table with a Filter icon on each table header.
Step 5: Filter Checkboxes
As we have the Checkboxes added to the sheet, now we can Filter them based on the Checkbox status.
- Then click on the Filter drop-down icon on the True/ False table header and notice that it is True and False selected.
- Then uncheck the False box and click OK.
- Then you will notice that there are only entries with True are showing.
- That means we effectively Filtered out the entries with False.
- Now we can check only the False box and then click OK.
- Then you will notice that there are only entries with False showing.
- That means we effectively Filtered out the entries with True.
💬 Note
- You should notice that there is an extra check box below the table in the cell
- The main reason this cell has an extra Checkbox is that Checkboxes are an object.
- As this Checkbox does not create any interruption in our main task of Filtering the Checkboxes, it should be ignored.
Download Practice Workbook
Download this practice workbook below.
Conclusion
Here, we have answered the question” how you can Filter Checkboxes in Excel” with elaborate explanations. We also showed how you can add Checkboxes in the first place.
For this problem, a workbook is available for download where you can practice these methods. Feel free to ask any questions or feedback through the comment section.
