How to use checkboxes in Excel. In this Excel tutorial, we will explore how to insert checkboxes, and then use the data of whether the box is checked or unchecked: you can have the formatting of table rows change automatically when a box is checked, and you can build checklist completion summaries and charts that use the checkbox data.

⏱️TIMESTAMPS⏱️
00:00 Using checkboxes in Excel
00:32 Adding checkboxes using form controls
01:58 Checkbox TRUE/FALSE
03:55 Checkbox changes formatting of table rows
05:09 Checklist data summary
08:05 Checkbox charts

The easiest way to add checkboxes in Excel is through the Developer tab. If you don’t already have it, the Developer tab can be enabled by going to the top of your #Excel screen, right-clicking, and selecting “customize the ribbon”. Check the box next to “Developer” at the bottom right, and click OK. Congratulations, you are now an Excel Developer!

On the Developer tab, we click Insert – Form Controls – Check Box. This brings you into some sort of drawing mode. Let’s draw an area that is roughly the same as cell C2. We can now edit the standard text, and change it to “Done”. Or delete the text altogether, as the purpose of a #checkbox is pretty clear to most people.

Let’s do some more formatting. This is a bit tricky at first, as the checkbox is superimposed on top of the cell itself. If you click on cell C2 with your left mouse button, you change the checkbox from unchecked to checked, which is not what we want to do yet. If you click on cell C2 with your right mouse button, you can go to format control, and then in the control tab switch from a standard checkbox to a 3-D shaded checkbox. This will become visible once you click OK. Now let’s narrow the checkbox object and center it in the middle of the cell. This solves our left-click issue as well, we can now either select the cell or select the checkbox.

Let’s copy over the checkbox from cell C2 into cells C3 through C6. Next, we right-click on the checkbox in cell C2, go back to format control, and provide a cell link. We link this particular checkbox to cell D2. Now this seems to be a bit counterintuitive. Normally, with regular Excel functions, you would go to cell D2 and insert a formula to pull data from cell C2. In the world of form controls, you are telling the checkbox in C2 to push data to D2. Click OK, and nothing seems to be happening. However, once you check the checkbox in C2, the value in D2 becomes TRUE. And once you uncheck C2, the value in D2 becomes FALSE. As you can see by clicking on D2, there is no formula here, TRUE and FALSE are pushed from C2 to D2, rather than pulled from there.

We repeat the cell link procedure for each of the checkboxes and its related row in column D. But wait, can’t we just copy over cell C2 down the rows after inserting the cell link, and have this update automatically? No, sadly you can’t. You have to manually assign a cell link to each of the checkboxes in Excel. I have tried every possible way I could think of to make the cell link update automatically. I have read every possible source on checkboxes and form controls in Excel. There is no other way, as far as I know. If you do find a way, please let me know in the comments. For now, it’s not a big deal to assign the cell links to each checkbox manually, but if you have an Excel table with one hundred or more checkboxes it becomes very labor intensive.

Let’s check each box on and off to activate the form control, and have FALSE show up in column D for each row.

Here comes the fun part of using checkboxes in Excel! Let’s have the formatting of table rows change automatically when a box is checked. Select cells A2 through C6, go to the Home tab, conditional formatting, new rule, use a formula to determine which cells to format. Below “format values where this formula is true”, type =$D2, then click Format. What you could do here is to select the Strikethrough effect. Click OK, and then OK again. For any row where the checkbox is checked, the text gets a strikethrough.

As a next step, we add reporting functionality that uses the Excel checkbox data: summaries and charts.

Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers finance training in various formats: YouTube videos, livestreams, classroom sessions, and webinars. Connect with me through Linked In!

Want to get access to bonus content, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through https://www.youtube.com/channel/UCQQJnyU8fALcOqqpyyIN4sg/join