How to Filter Checkboxes in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

Add Developer Tab to Filter Checkboxes in Excel

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

Add Developer Tab to Filter Checkboxes in Excel

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

Add Developer Tab to Filter Checkboxes in Excel

  • Now check the Developer option and click OK.

Add Developer Tab to Filter Checkboxes in Excel

  • Now you will see the Developer tab on the ribbon.

Add Developer Tab to Filter Checkboxes in Excel


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.

Add Checkboxes from Developer Tab to Filter Checkboxes in Excel

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

Add Checkboxes from Developer Tab to Filter Checkboxes in Excel

  • After carefully drawing out the Checkbox, click anywhere on the sheet.
  • You will see your check box is present.

Add Checkboxes from Developer Tab to Filter Checkboxes in Excel

  • 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 right-click on the box and from the context menu, click on the Edit Text.

  • 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 right-click on the box and from the context menu, click on the Edit Text.

  • Then you will have the Checkbox pasted in cell C5.

Then right-click on the box and from the context menu, click on the Edit Text.

  • Repeat the same process for the rest of the cells.
  • After copying the Checkboxes, the table will somewhat look like the one below.

Then right-click on the box and from the context menu, click on the Edit Text.

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

Link Checkboxes with Adjacent Cells to Filter Checkboxes in Excel

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

Link Checkboxes with Adjacent Cells to Filter Checkboxes in Excel

  • Now select cell E5 and enter the following formula:
=IF(D5=TRUE,"Submitted","Not Submitted")

Link Checkboxes with Adjacent Cells to Filter Checkboxes in Excel

  • Then drag the Fill Handle to cell E12.
  • This will fill the range of cells E5:E12 with the submission status of each student.

Link Checkboxes with Adjacent Cells to Filter Checkboxes in Excel

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.

Convert Dataset to Table to Filter Checkboxes in Excel

  • After that, the whole range of cells will be converted to a table with a Filter icon on each table header.

Convert Dataset to Table to Filter Checkboxes in Excel


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.

Convert Dataset to Table to Filter Checkboxes in Excel

  • Then uncheck the False box and click OK.

Filter Checkboxes in Excel

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

Filter Checkboxes in Excel

  • Then you will notice that there are only entries with False showing.
  • That means we effectively Filtered out the entries with True.

Filter Checkboxes in Excel

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

Filter Checkboxes in Excel


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.


Related Articles


<< Go Back to Excel CheckBox | Form Control in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo