{"id":2854,"date":"2020-12-28T11:52:25","date_gmt":"2020-12-28T19:52:25","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=2854"},"modified":"2020-12-28T11:52:26","modified_gmt":"2020-12-28T19:52:26","slug":"how-to-use-a-checkbox-in-excel","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=2854","title":{"rendered":"How to Use a Checkbox in Excel"},"content":{"rendered":"\n<p>by\u00a0<a href=\"https:\/\/www.ablebits.com\/office-addins-blog\/author\/svetlana-cheusheva\/\" target=\"_blank\" rel=\"noreferrer noopener\">Svetlana Cheusheva<\/a><\/p>\n\n\n\n<p><em>This tutorial will guide you on how to make a checkbox in Excel and use the check box results in formulas to create an interactive checklist, to-do list, report or graph.<\/em><\/p>\n\n\n\n<p>I believe everybody knows what a checkbox is, you must have seen plenty of them on various forms online. Still, for the sake of clarity, let me begin with a brief definition.<\/p>\n\n\n\n<p>A&nbsp;<strong>check box<\/strong>, also referred to as a&nbsp;<strong>tick box<\/strong>&nbsp;or&nbsp;<strong>checkmark box<\/strong>&nbsp;or&nbsp;<strong>selection box<\/strong>, is a little square box where you click to select or deselect a given option.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-checkbox.png\" alt=\"Checkboxes in Excel\" title=\"Checkboxes in Excel\"\/><\/figure>\n\n\n\n<p>Inserting a checkbox in Excel sounds like a trivial thing, but it opens up a host of new possibilities for your worksheets that will keep you on track with your goals, schedule, assignments, etc.<\/p>\n\n\n\n<ul class=\"wp-block-list\" id=\"id-post-toc\"><li>How to add checkbox in Excel<\/li><li>How to use checkboxes in Excel &#8211; examples<ul><li>How to link a checkbox to a cell<\/li><li>Make an Excel checklist<\/li><li>Create a to-do list with conditional formatting<\/li><li>Create an interactive report<\/li><li>Make a dynamic chart<\/li><\/ul><\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"insert-checkbox-excel\">How to insert checkbox in Excel<\/h2>\n\n\n\n<p>Like all other\u00a0<em>Form<\/em>\u00a0controls, the\u00a0<strong>Check Box<\/strong>\u00a0control resides on the\u00a0Developer tab, which does not appear on the Excel ribbon by default. So, you need to turn it on first.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"developer-tab\">1. Show the Developer tab on the ribbon<\/h3>\n\n\n\n<p>To add the Developer tab to the Excel ribbon, do the following:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Right-click anywhere on the ribbon, and then click&nbsp;<strong>Customize the Ribbon<\/strong>\u2026 Or, click&nbsp;<em>File<\/em>&nbsp;&gt;&nbsp;<em>Options<\/em>&nbsp;&gt;&nbsp;<em>Customize Ribbon<\/em>.<\/li><li>Under&nbsp;<em>Customize the Ribbon<\/em>, select&nbsp;<strong>Main Tabs<\/strong>&nbsp;(usually it is selected by default), check the&nbsp;<strong>Developer<\/strong>&nbsp;box, and click OK.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/show-developer-tab.png\" alt=\"Add the Developer tab to the Excel ribbon.\" title=\"Add the Developer tab to the Excel ribbon.\"\/><\/figure>\n\n\n\n<p>Now, with the Developer tab in place, you get access to a host of interactive controls, including Check Box.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">2. Organize the data<\/h3>\n\n\n\n<p>If you are creating an Excel checklist or to-do list, the first step is to make a list of tasks or other items for which the check boxes will be inserted.<\/p>\n\n\n\n<p>For this example, I&#8217;ve created the following&nbsp;<em>Party Planning Checklist<\/em>:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-checklist-data.png\" alt=\"Party Planning Checklist\" title=\"Party Planning Checklist\"\/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">3. Add a check box<\/h3>\n\n\n\n<p>The preparation steps are completed, and now we are getting to the main part &#8211; add checkboxes to our Party Planning list.<\/p>\n\n\n\n<p>To insert a checkbox in Excel, execute these steps:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>On the&nbsp;<em>Developer<\/em>&nbsp;tab, in the&nbsp;<em>Controls<\/em>&nbsp;group, click&nbsp;<em>Insert<\/em>, and select&nbsp;<strong>Check Box<\/strong>&nbsp;under&nbsp;<em>Form Controls<\/em>.<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/insert-checkbox-excel.png\" alt=\"Insert a checkbox in Excel.\"><\/li><li>Click in the cell where you want to insert the first checkbox (B2 in this example). The Check Box control will appear near that place, though not exactly positioned in the cell:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/checkbox-added-excel.png\" alt=\"Click in the cell where you want to add the checkbox.\"><\/li><li>To properly position the check box, hover your mouse over it and as soon as the cursor changes to a four-pointed arrow, drag the checkbox where you want it.<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/position-checkbox.png\" alt=\"Position the check box in the cell.\"><\/li><li>To remove the text &#8220;<em>Check Box 1<\/em>&#8220;, right click the checkbox, select the text and delete it. Or, right click the check box, select&nbsp;<em>Edit Text<\/em>&nbsp;in the context menu, and then delete the text.<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-checkbox-remove-text.png\" alt=\"Delete the checkbox caption text.\"><\/li><\/ul>\n\n\n\n<p>Your first Excel checkbox is ready, and you just have to copy it to other cells.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">4. Copy the checkbox to other cells<\/h3>\n\n\n\n<p>Select the cell with the check box by using the arrow keys on your keyboard, and position the cursor over the lower right corner of the cell. When the mouse pointer changes to a thin black cross, drag it down to the last cell where you want to copy the checkbox.<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/copy-checkbox-excel.png\" alt=\"Copy the checkbox to other cells.\"><\/p>\n\n\n\n<p>Done! The check boxes are added to all the items in the checklist:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-checkboxes-added.png\" alt=\"The check boxes are added to all the items in the checklist.\"><\/p>\n\n\n\n<p>As you can see in the screenshot above, our Excel checklist is almost ready. Why almost? Although the checkboxes are inserted and you can now check or uncheck them by simply clicking on a box, Microsoft Excel is not able to respond to these changes because no cell is linked to any of the checkboxes yet.<\/p>\n\n\n\n<p>The next part of our Excel Checkbox tutorial will teach you how to capture the user selecting or clearing a checkbox and how to use that information in your formulas.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"excel-checkboxes-examples\">Examples of using checkboxes in Excel<\/h2>\n\n\n\n<p>Below you will find a few examples of how to use checkboxes in Excel to make an interactive checklist, to-do list, report and chart. But first, let&#8217;s learn how to link checkboxes to cells. The technique is very simple, but it&#8217;s the corner stone of using the checkbox results in your formulas.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"link-checkbox-cell\">How to link a checkbox to a cell<\/h3>\n\n\n\n<p>As already mentioned, to be able to capture the checkbox state (checked or unchecked) you need to associate the check box with a certain cell. To do this, please follow these steps:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Right click the checkbox, and then click&nbsp;<strong>Format Control<\/strong>.<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/format-control.png\" alt=\"Right click the checkbox, and then click Format Control.\"><\/li><li>In the&nbsp;<em>Format Control<\/em>&nbsp;dialog box, switch to the&nbsp;<em>Control<\/em>&nbsp;tab, click in the&nbsp;<strong>Cell link<\/strong>&nbsp;box and select an empty cell on the sheet to which you want to link to the checkbox, or type the cell reference manually:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/link-checkbox-cell.png\" alt=\"Link a checkbox to a cell.\"><\/li><li>Repeat the above step for other check boxes.<strong>Tip.<\/strong>&nbsp;To easily identify the linked cells, select them in an adjacent column that does not contain any other data. This way, you will be able to safely hide the linked cells later so they won&#8217;t clutter your worksheet.<\/li><li>Finally, click on each of the linked checkboxes. In the linked cells, TRUE appears for selected checkboxes, and FALSE for cleared checkboxes:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/linked-checkboxes-excel.png\" alt=\"In the linked cells, TRUE appears for selected checkboxes, and FALSE for cleared checkboxes.\"><\/li><\/ol>\n\n\n\n<p>At this point, the link cells probably don&#8217;t make much sense, but please bear with me just a little longer and you will see how many new opportunities they provide to you.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"make-excel-checklist\">How to make a checklist in Excel with data summary<\/h3>\n\n\n\n<p>In fact, we have already done the major part of the job by\u00a0adding checkboxes\u00a0and\u00a0linking them to cells. Now, we will just write a few formulas to create a data summary for our Excel checklist.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Formula to calculate the total number of tasks<\/h4>\n\n\n\n<p>It&#8217;s the easiest one &#8211; use the\u00a0COUNTA function\u00a0to get the number of non-blank cells in the checklist:<\/p>\n\n\n\n<p><code>=COUNTA(A2:A12)<\/code><\/p>\n\n\n\n<p>Where A2:A12 are the checklist items.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Formula to get the number of completed tasks<\/h4>\n\n\n\n<p>A completed task means a checkbox with a tick symbol in it, which means the TRUE value in a linked cell. So, get the total count of TRUE&#8217;s with this\u00a0COUNTIF\u00a0formula:<\/p>\n\n\n\n<p><code>=COUNTIF(C2:C12,TRUE)<\/code><\/p>\n\n\n\n<p>Where C2:C12 are the linked cells.<\/p>\n\n\n\n<p>To make a formula a bit more clever, you use\u00a0COUNTIFS\u00a0instead of COUNTIF to check for blank cells in the list (column A):<\/p>\n\n\n\n<p><code>=COUNTIFS(A2:A12, \"&lt;&gt;\", C2:C12, TRUE)<\/code><\/p>\n\n\n\n<p>In this case, if you delete some irrelevant item(s) from your Excel checklist, but forget to remove a check symbol from the corresponding box, such checkmarks won&#8217;t be counted.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"checklist-percentage-formula\">Formula to get the percentage of completed tasks<\/h4>\n\n\n\n<p>To calculate the presented of the tasks completed, use the regular\u00a0percentage formula:<\/p>\n\n\n\n<p><code>Part\/Total = Percentage<\/code><\/p>\n\n\n\n<p>In our case, divide the number of completed tasks by the total number of tasks, like this:<\/p>\n\n\n\n<p><code>=COUNTIF(C2:C12,TRUE)\/COUNTA(A2:A12)<\/code><\/p>\n\n\n\n<p>The following screenshot demonstrates all of the above formulas in action:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/make-checklist-excel.png\" alt=\"Formulas to summarize the checklist data\"><\/p>\n\n\n\n<p>As you can see in the screenshot above, we have inserted one more formula in B18. The formula is based on the\u00a0IF function\u00a0that returns &#8220;Yes&#8221; if the number of completed tasks is equal to the task&#8217;s total, &#8220;No&#8221; otherwise:<\/p>\n\n\n\n<p><code>=IF(B14=B15, \"Yep!\", \"Nope :(\")<\/code><\/p>\n\n\n\n<p>To embellish your checklist a bit further, you can create a couple of\u00a0conditional formatting rules\u00a0that will change the colour of cell B18 depending on its value.<\/p>\n\n\n\n<p>Once that&#8217;s completed, hide the column with linked cells, and your Excel checklist is done!<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-checklist.png\" alt=\"Excel checklist with data summary\"><\/p>\n\n\n\n<p>If you like the checklist we&#8217;ve created for this example, you are welcome to download it now:<\/p>\n\n\n\n<p>Download this Excel Checklist<\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/12\/excel-checklist.xlsx\">excel-checklist<\/a><a href=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/12\/excel-checklist.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<h4 class=\"wp-block-heading\">More Excel checklist templates<\/h4>\n\n\n\n<p>To quickly get a selection of checklist templates for Excel, click&nbsp;<em>File<\/em>&nbsp;&gt;&nbsp;<em>New<\/em>, type &#8220;checklist&#8221; in the search box, and press Enter.<\/p>\n\n\n\n<p>If none of Excel&#8217;s checklist templates is well suited for your needs, you may find useful the following resources helpful:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"http:\/\/templatelab.com\/to-do-list-checklist-templates\/\" rel=\"noreferrer noopener\" target=\"_blank\">50+ printable checklist and to-do list templates<\/a>&nbsp;(for Excel and Word)<\/li><li><a href=\"http:\/\/www.vertex42.com\/ExcelTemplates\/checklist.html\" rel=\"noreferrer noopener\" target=\"_blank\">Free Excel check list templates<\/a><\/li><li><a href=\"http:\/\/www.clearlyandsimply.com\/clearly_and_simply\/2015\/01\/microsoft-excel-check-list-compilation.html\" rel=\"noreferrer noopener\" target=\"_blank\">Interactive Excel checklist template<\/a>&nbsp;(with a number of variations and enhancement)<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"todo-list-conditional-formatting\">How to create a To-Do list with conditional formatting<\/h3>\n\n\n\n<p>Basically, you can add checkboxes and formulas for a to-do list exactly in the same way as we have just done for the\u00a0Excel checklist. &#8220;What&#8217;s the point of writing this section then?&#8221; you can ask me. Well, in a typical to-do list, the completed tasks have the\u00a0<strong>strikethrough format<\/strong>\u00a0like this:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-to-do-list-example.png\" alt=\"Conditionally formatted Excel To-Do list\"><\/p>\n\n\n\n<p>This effect can be easily achieved by creating a conditional formatting rule. The detailed steps follow below.<\/p>\n\n\n\n<p>To begin with, write down a list of tasks, insert checkboxes and link them to cells:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-todo-list.png\" alt=\"To-Do list with checkboxes and linked cells\"><\/p>\n\n\n\n<p>And now, apply&nbsp;<strong>conditional formatting<\/strong>&nbsp;that will give the strikethrough format and, optionally, a different background or font color to the checked items.<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Select a list of tasks (A2:A11 in this example).<\/li><li>Go to the\u00a0<em>Home<\/em>\u00a0tab >\u00a0<em>Styles<\/em>\u00a0group, and click\u00a0<strong>Conditional Formatting<\/strong>\u00a0>\u00a0<strong>New Rule\u2026<\/strong><\/li><li>In the\u00a0<em>New Formatting Rule<\/em>\u00a0dialog box, select\u00a0<strong>Use a formula to determine which cells to format<\/strong>.<\/li><li>In the\u00a0<em>Format values where this formula is true<\/em>\u00a0box, enter the following formula:<code>=$C2=TRUE<\/code>Where C2 is the top-most linked cell.<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-todo-list-formatting-rule.png\" alt=\"Creating a conditional formatting rule\"><\/li><li>Click on the\u00a0<strong>Format<\/strong>\u00a0button, set up the desired formatting style, and click OK. In this example, we select the\u00a0<strong>Strikethrough<\/strong>\u00a0effect and the light grey font color:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/format-completed-tasks.png\" alt=\"Select the strikethrough effect and the desired font color.\"><strong>Tip.<\/strong>\u00a0If you have little experience with conditional formatting, you may find the following detailed guidance helpful:\u00a0Excel conditional formatting based on another cell value.<\/li><\/ol>\n\n\n\n<p>As of now, whenever a certain box is checked, the corresponding item gets formatted in the light grey font color with a strikethrough.<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/strikethrough-todo-list-excel.png\" alt=\"To-To list with the strikethrough format for completed tasks.\"><\/p>\n\n\n\n<p>And here&#8217;s one more idea for formatting your Excel to-do list. Instead of crossing out the competed tasks, you can insert an additional column with the following\u00a0IF formula:<\/p>\n\n\n\n<p><code>=IF(E2=TRUE, \"Done\", \"To Be Done\")<\/code><\/p>\n\n\n\n<p>Where E2 is the top-most linked cell.<\/p>\n\n\n\n<p>As shown in the screenshot below, the formula returns &#8220;Done&#8221; if a linked cell contains TRUE, &#8220;To be done&#8221; if FALSE:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-todo-list-formula.png\" alt=\"Formula for To-Do list\"><\/p>\n\n\n\n<p>After that, apply the desired conditional format to the&nbsp;<em>Status<\/em>&nbsp;column based on this formula:<\/p>\n\n\n\n<p><code>=$C2=\"Done\"<\/code><\/p>\n\n\n\n<p>The result will look something similar to this:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-todo-list-formatting.png\" alt=\"To-To list with the conditionally formatted Status column\"><\/p>\n\n\n\n<p>Lastly, add a couple of formulas to calculate the completed tasks (like we did for the&nbsp;<a href=\"https:\/\/www.ablebits.com\/office-addins-blog\/2016\/09\/21\/insert-checkbox-excel\/#make-excel-checklist\">checklist<\/a>), hide the linked cells, and your Excel To Do list is good to go!<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-todo-list-template.png\" alt=\"Excel To Do list template\"><\/p>\n\n\n\n<p>The\u00a0bar chart\u00a0at the top of the To-Do list is based on the\u00a0percentage formula\u00a0in B2. If you are curious to know the details, I encourage you to download the template, unhide columns D and E, and investigate the formulas.<\/p>\n\n\n\n<p>Download To-Do List Template<\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/12\/excel-todo-list.xlsx\">excel-todo-list<\/a><a href=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/12\/excel-todo-list.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"interactive-report\">How to create an interactive report with check boxes<\/h3>\n\n\n\n<p>Another useful application of checkboxes in Excel is for creating interactive reports.<\/p>\n\n\n\n<p>Supposing you have a sales report that includes data for 4 regions: North, South, East and West. Your aim is to get the total for one or more selected regions. Of course, this can be done by using the\u00a0Slicers\u00a0feature of an\u00a0Excel table\u00a0or\u00a0PivotTable\u00a0or by inserting\u00a0Subtotals. But why don&#8217;t we make the report more user-friendly by inserting 4 checkboxes at the top?<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/checkboxes-interactive-report.png\" alt=\"An interactive report with check boxes\"><\/p>\n\n\n\n<p>Looks nice, isn&#8217;t it? To create a similar report in your sheet, please follow these steps:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Add 4 checkboxes at the top of the sheet, for the\u00a0<em>North<\/em>,\u00a0<em>South<\/em>,\u00a0<em>East<\/em>\u00a0and\u00a0<em>West<\/em>\u00a0regions.<\/li><li>Create the criteria area somewhere in an unused part of the sheet, and\u00a0link the checkboxes\u00a0to empty cells:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/criteria-area.png\" alt=\"Create the criteria area and link the checkboxes to empty cells.\">In the above screenshot, I2:I5 has linked cells and H2:H5 are the region names exactly as they appear in the report.<\/li><li>Add one more column to the criteria area with an IF formula that returns the region name if the linked cell evaluates to TRUE, a dash (&#8220;-&#8220;) otherwise:<code>=IF(I2=TRUE, H2, \"-\")<\/code><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-checkbox-if.png\" alt=\"Add an IF formula to the criteria area.\"><\/li><li>Type a heading for the formula column that exactly matches the heading of the corresponding column in the report (<em>Region<\/em>\u00a0in this example). The exact match is very important and on the next step, you will understand why.<\/li><li>Next, write the formula to calculate the total for selected regions. For this, we are going to use the DSUM function that sums the values in a database that match the specified conditions:DSUM(database, field, criteria)Where:<ul><li><strong>Database<\/strong>\u00a0is your table or range including the column headings (A5:F48 in this example).<\/li><li><strong>Field<\/strong>\u00a0is the column you want to sum. It can be supplied either as the column heading enclosed in the quotation marks, or a number that represents the position of the column in the database. In this example, we add up numbers in the\u00a0<em>Sub-total<\/em>\u00a0column, so our second argument is &#8220;sub-total&#8221;.<\/li><li><strong>Criteria<\/strong>\u00a0is the range of cells that contain your conditions, including the column heading (J1:J5). That is why the heading of the formula column in the criteria area should match the column heading in the report.Put the above argument together, and your DSUM formula goes as follows:<code>=DSUM(A5:F48, \"sub-total\", J1:J5)<\/code>\u2026and works perfectly!<img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-checkboxes-dsum.png\" alt=\"DSUM formula for the interactive report\"><\/li><\/ul><\/li><\/ol>\n\n\n\n<p>If you&#8217;d like to hide the #DIV\/0! error that appears when no region is selected, wrap DSUM into the IFERROR function:<\/p>\n\n\n\n<p><code>=IFERROR(DSUM(A5:F48, \"sub-total\", J1:J5), 0)<\/code><\/p>\n\n\n\n<p>If in addition to total, your report calculates an average for each row, you can use the DAVERAGE(database, field, criteria) function to get a sales&nbsp;<strong>average<\/strong>&nbsp;for the selected regions.<\/p>\n\n\n\n<p>Finally, hide and probably\u00a0lock\u00a0the criteria area to prevent accidental changes, and your interactive report is all set!<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/interactive-report-excel-checkboxes.png\" alt=\"An interactive report to get the total and average for selected regions\"><\/p>\n\n\n\n<p>Download Interactive Report<\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/12\/excel-checkbox-examples.xlsx\">excel-checkbox-examples<\/a><a href=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/12\/excel-checkbox-examples.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"dynamic-chart\">Make a dynamic chart based on the checkbox state<\/h3>\n\n\n\n<p>This example will teach you how to create a dynamic Excel chart that can respond to changing the checkboxes state (selected or cleared):<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-checkbox-chart.png\" alt=\"Dynamic chart based on the checkbox state\"><\/p>\n\n\n\n<p>The source data for this example is as simple as this:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/sales-numbers.png\" alt=\"Source data\"><\/p>\n\n\n\n<p>To turn it into a dynamic Excel graph, execute the following steps:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li><strong>Create checkboxes<\/strong>&nbsp;and&nbsp;<strong>link<\/strong>&nbsp;them to empty cells.In particular, insert 2 checkboxes for the 2013 and 2014 years, and connect them to cells G2 and G3, respectively:<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/link-checkboxes-excel.png\" alt=\"Create checkboxes and link them to empty cells.\"><\/li><li>Create the&nbsp;<strong>dataset for the chart<\/strong>&nbsp;dependent on the source data and linked cells (please see the image below):<ul><li>For 2013 year (J4:J7), use the following formula:<code>=IF($G$2=TRUE, B4, NA())<\/code>If the 2013 checkbox is selected (G2 is TRUE), the formula pulls the original value from B4, otherwise returns the #N\/A error.<\/li><li>For 2014 year (K4:K7), enter a similar formula to pull the values from column C if the 2014 checkbox is selected:<code>=IF($G$2=TRUE, C4, NA())<\/code><\/li><li>In cell L4, enter the formula&nbsp;<code>=$D4<\/code>, and copy it down to L7. Because the data for the year 2015 should always be displayed in the chart, an IF formula is not needed for this column.<\/li><\/ul><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/chart-dataset.png\" alt=\"The dataset for the chart dependent on the source data and linked cells\"><\/li><li>Create a&nbsp;<a href=\"https:\/\/www.ablebits.com\/office-addins-blog\/2015\/10\/22\/make-graph-excel-chart-template\/#create-combination-chart\">combo chart<\/a>&nbsp;based on the dependent data set (I3:L7). Since we have linked all cells in the dependent table to the original data, the chart will update automatically as soon as any change has been made in the original data set.<br><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/excel-checkbox\/excel-checkboxes-chart.png\" alt=\"Dynamic chart reflecting the checkbox state\"><\/li><\/ol>\n\n\n\n<p>Download Dynamic Chart<\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/12\/dynamic-chart-1.xlsx\">dynamic-chart-1<\/a><a href=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/12\/dynamic-chart-1.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<p>This is how you can create and use checkboxes in Excel. To review all the examples discussed in this tutorial, you may want to download our sample workbook below. I thank you for reading and hope to see you on our blog next week.<\/p>\n\n\n\n<p>Ref: https:\/\/www.ablebits.com\/office-addins-blog\/2016\/09\/21\/insert-checkbox-excel\/<\/p>\n","protected":false},"excerpt":{"rendered":"<p>by\u00a0Svetlana Cheusheva This tutorial will guide you on how to make a checkbox in Excel and use the check box results in formulas to create an interactive checklist, to-do list, report or graph. I believe everybody knows what a checkbox is, you must have seen plenty of them on various forms online. Still, for the <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=2854\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[530],"class_list":["post-2854","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-checkbox-in-excel"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/2854","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2854"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/2854\/revisions"}],"predecessor-version":[{"id":2860,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/2854\/revisions\/2860"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2854"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2854"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2854"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}