Checklists are a handy tool for anyone at home or in the office, and surprisingly simple to create in Excel for your own use or to share with others. Checklists are great for: Show
Excel is available as part of an Office 365 package subscription, or to purchase standalone, and is one of the best tools for productivity as well as recording and sharing information. Here’s how to easily create an Excel checklist that’s completely interactive and easily updatable. 1. Turn on the Developer tabTo create an Excel checklist, you need to turn on the Developer tab on the ribbon, if its not already showing. To do this, right-click any part of the Excel ribbon, and choose Customise the Ribbon from the dropdown menu that is shown. Under Customise the Ribbon on the right-hand side of the screen, tick the Developer tab, and then click OK. The Developer tab will now be accessible from the Ribbon. 2. Create your listThe next step to creating your Excel checklist, is to make your list of items or tasks and then choose where you want to put your checkboxes. Click in the first cell you want to create a checkbox in and, from the Developer tab, click Insert and then select checkbox under Form Controls. Your mouse cursor will then become a + which will allow you to draw the checkbox where you want it. You can click and drag the checkbox into position, and remove the text Check Box 1 by right-clicking on the checkbox and selecting Edit Text. The cursor will then appear at the start of the text inside the checkbox, and you can delete the text, resize the checkbox, and reposition it. The easiest way to create checkboxes for the remainder of your list items is to highlight the cell the checkbox is in, and drag from the handle in the bottom right of the cell (the cursor will become a + when it is draggable) which will Autofill the checkboxes down. To check and uncheck items, simply left click the checkbox. 3. Use and modify your checkboxesThere are additional formatting options you can apply to your checkbox to change its appearance. Right-click the first checkbox, and choose Format Control from the menu. This will allow you to change the colour, size and other attributes of your checkbox, including whether your checkbox is checked or unchecked by default. Once you’ve formatted as required, click OK. 4. Link a checkbox to a cell for using in formulasIf you want to use formulas like IF, SUM, SUMIF or COUNTIF to capture how many checkboxes are checked or unchecked, you can link your checkbox to a cell that will display TRUE or FALSE, depending on its checked value. Simply right-click on the checkbox, choose Format Control from the menu, and then click the Control tab. Click in Cell link, and then select the cell you wish the TRUE/FALSE value to be entered in (i.e. D2 – note, you don’t want this to be the same cell that you have the checkbox or other text in). You will need to repeat this manually for each checkbox, so that each one has its own linked cell – Autofill will copy the value and link from the first cell only. Once these are all done, you can create a simple COUNTIF formula to total the number of cells that are FALSE, which will tell you how many action items are outstanding. And if you don’t want to see the TRUE/FALSE results, simply hide the column displaying them – this won’t affect any formulas that are using these values. Want to know more about what you can do with Excel?Now that you know how to create an Excel checklist, why not discover even more about your computer and Excel with our technical training service. We can take you through how to use your PC or laptop, and the other handy features that software like Excel has to increase your productivity. How to Insert Checkbox in Excel (to Create Interactive Lists and Charts) FREE EXCEL TIPS EBOOK - Click here to get your copy Watch Video – How to Insert and Use a Checkbox in Excel In Excel, a checkbox is an interactive tool that can be used to select or deselect an option. You must have seen it in many web form available online. You can use a checkbox in Excel to create interactive checklists, dynamic charts, and dashboards. This Excel tutorial covers the following topics:
To insert a checkbox in Excel, you first need to have the Developer tab enabled in your workbook. Can’t see the developer tab? Don’t worry and keep reading! Get the Developer Tab in Excel RibbonThe first step in inserting a checkbox in Excel is to have the developer tab visible in the ribbons area. The developer tab contains the checkbox control that we need to use to insert a checkbox in Excel. Below are the steps for getting the developer tab in the Excel ribbon.
Now with the Developer tab visible, you get access to a variety of interactive controls. How to Insert a Checkbox in ExcelHere are the steps to insert a checkbox in Excel:
Now your checkbox is linked to cell A1, and when you check the checkbox, it will show TRUE in cell A1, and when you uncheck it, it will show FALSE. Examples of Using a Checkbox in ExcelHere are a couple of examples where you can use a checkbox in Excel. Creating an Interactive To-Do List in ExcelBelow is an example of a To-Do list that uses checkboxes to mark the task as complete. A couple of things are happening in the example above:
Here is how to make this:
Click here to download the checklist. Creating a Dynamic Chart in ExcelYou can use an Excel checkbox to create a dynamic chart as shown below: In this case, the checkbox above the chart is linked to cell C7 and C8. If you check the checkbox for 2013, the value of cell C7 becomes TRUE. Similarly, if you check the checkbox in for 2014, the value of cell C8 becomes TRUE. The data used in creating this chart is in C11 to F13. The data for 2013 and 2014 is dependent on the linked cell (C7 and C8). If the value in cell C7 is TRUE, you see the values in C11:F11, else you see the #N/A error. Same is the case with data for 2014. Now based on which checkbox is checked, that data is shown as a line in the chart. Click here to download the dynamic chart template. Inserting Multiple Checkboxes in ExcelThere are a couple of ways you can insert multiple checkboxes in the same worksheet. #1 Inserting a Checkbox using the Developer TabTo insert more than one checkbox, go to the Developer Tab –> Controls –> Insert –> Form Controls –> Check Box. Now when you click anywhere in the worksheet, it will insert a new checkbox. You can repeat the same process to insert multiple checkboxes in Excel. Note:
#2 Copy Pasting the CheckboxSelect an existing checkbox, copy it and paste it. You can also use the keyboard shortcut (Control + D). Note:
#3 Drag and Fill Cells with CheckboxIf you have a checkbox in a cell in Excel and you drag all fill handle down, it will create copies of the checkbox. Something as shown below: Note:
Deleting the Checkbox in ExcelYou can easily delete a single checkbox by selecting it and pressing the delete key. To select a checkbox, you need to hold the Control key and the press the left button of the mouse. If you want to delete multiple checkboxes:
If you have many checkboxes scattered in your worksheet, here is a way to get a list of all the checkbox and delete at one go:
Note: The selection pane displays all the objects of the active worksheet only. How Fix the Position of a Checkbox in ExcelOne common issue with using shapes and objects in Excel is that when you resize cells or hide/delete rows/columns, it also affects the shapes/checkboxes. Something as shown below: To stop the checkbox from moving around when you resize or delete cells, do the following:
Now when you resize or delete cells, the checkbox would stay put. Caption Name Vs. NameWhen you insert a checkbox in Excel, you see a name in front of the box (such as Check Box 1 or Check Box 2). This text – in front of the box – is the Caption Name of the checkbox. To edit this text, right-click and select the ‘Edit Text’ option. While you see the new text, in the backend, Excel continues to refer to this checkbox as Check Box 1. If you select the checkbox and look at the Name Box field, you will see the name Excel uses for this checkbox in the backend. You can easily change this backend name by first selecting the checkbox in the worksheet and then typing the name in the name box (the naming rules are same as that of named ranges). See Also: How to Insert a Checkbox in Google Sheets. You May Also Like the Following Excel Tutorials:
FREE EXCEL BOOK Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster
Name YES - SEND ME THE EBOOK 17 thoughts on “How to Insert Checkbox in Excel (to Create Interactive Lists and Charts)”
|