Table of Contents
Microsoft Excel is an immensely popular workbook developed by Microsoft in 1995. It lists and calculates data with the help of mathematical formulae and functions. One of the most commonly used programs to generate reports, analyze, and make predictions, estimations, etc. across industries, offices, schools, and colleges. This can be easily done using Checkbox in Excel.
The data is assembled in cells in rows and columns. The built-in Excel formulas and functions process the data. The checkbox is one such function of Microsoft Excel. A checklist or some mathematical functions make use of a checkbox. We will see how to insert a Checkbox in Excel in this article.
What is a Checkbox in Excel?
A Checkbox is an interface between a question or a task and its response. It is a square box where the response is ticked in Word document, Publisher, Google Docs, Excel, etc
To insert an Excel checkbox, the Forms toolbar or the developer tab is required.
The developer tab should be available on the toolbar. The below-mentioned steps are followed if the Developer tab is not seen on the toolbar.
- Open Microsoft Office
- Create a new Spreadsheet
- Right-click on the Mini tool Bar
- Choose the “ Customize Quick Access” toolbar
- Choose “Popular”
- Click on the “Show Developer tab in the ribbon”
- Select OK.
Right-click on the Toolbar ribbon –>Customize ribbon –> All Tabs –> Developer tab
Click on the Developer tab and press OK.
The Developer Tab is visible in the ribbon,
Click on Developer –>Insert –> Form Controls –>select Checkbox □–>select cell to paste Checkbox.
Select the cell where you want the Checkbox in Excel Spreadsheet. You may have to drag the Checkbox and the label to the desired cell and size it to fit in the cell.
Click on the Checkbox and it will fill with a tick mark.
The Excel Spreadsheet toolbar may even look like this ↓depending on the version of Microsoft Excel
For complex functions in a Checkbox in Excel,
Go to Developer -> Insert -> ActiveX Controls -> select Check Box ->click on the cell where you require the checkbox.
To insert multiple Checkboxes in Excel, there are two methods.
1. Copy and Paste the inserted Checkbox from its cell.
2. Select the cell with Checkbox. Click the cursor on the right bottom corner and drag to as many cells as required.
mathematical formulas and functions will not work in both of these cases. The copied Checkboxes will assume the cell value of the original Checkbox in Excel.
Consider that the checkbox in cell B2 is linked to cell A2. All the copied checkboxes will show the value of cell A2. Mathematical calculations or projections will be incorrect in such a case. To insert multiple checkboxes, the process of selecting a Checkbox in Excel must be repeated.
There are some shortcuts for copying the checkboxes and formatting
1. To copy the Checkbox in Excel use ctrl+c
2. To paste the checkbox press ctrl+v
3. To duplicate the checkbox enter ctrl+d
4. To format the checkbox use ctrl+1
Formatting a Checkbox in Excel
1. Select the Checkbox to format.
2. Right-click on the cell
3. Select Format control / Format objective.
There will be a number of options available like:
1. Add or change Colors and Lines. Colors and lines are added to the Checkbox.
2. Alter size. The size of the checkbox will increase or decrease.
3. Control to link cells. Functions and formulas added to the cells determine the value projected in the checkbox. Checked, unchecked, or mixed cells are there.
4. Protection. It locks the object or text.
5. Properties for object positioning. This helps to move or size the cells.
6. Web to write alternative texts to enable search engines to find web pages.
Linking a cell
Click on the cell where you want to insert the checkbox. Right-click on it and go to format control after inserting the checkbox. There you will see Cell Link. Enter the cell address or click on the cell on the spreadsheet where you wish to project the value or answer.
Go to the cell with the answer displayed. Right-click on it
Go to Format Cell -> Category -> Custom ->Type ->enter ;;; -> click OK. Here, the answer will show only on the formula bar.
Learning Microsoft Excel
There are many classes and tutorials which teach how to use Microsoft Excel and insert Checkbox in Excel. Henry Harvin is an ISO 29990:2010 Academy and offers over 800 courses in different fields. It has the Best Corporate Training Platform award to its credit. Henry Harvin Academy offers many popular courses like Advanced Excel Course and Excel for Business Analyst
Use of Checkbox in Excel
A list with checkboxes is very useful. We can add one or multiple checkboxes to our daily planner, diet chart, travel itinerary, shopping list, guest list, menu, project planning, study timetable, etc. The list is endless. Create a table in a spreadsheet with checkboxes against each task. Tick the checkbox after completing a task. This helps us keep track of the progress of our project or task. It helps in analyzing and keeping a check on the budget, meeting deadlines, and efficiency of the team members. It becomes easier to view which tasks need attention.
Formulas linked to the Checkbox in Excel will help to make financial, sales, or purchase forecasts. A visual projection of our progress in the task undertaken is possible with this small square box in Excel or Word etc.
A. It is a small square box inserted from the Developer tab to enable the selection or de-selection of a variable based on our answer or calculation. A checked or unchecked box is a visual representation of the tasks pending and completed from our list of assignments undertaken. For example, if one puts a tick in the checkbox it would mean ‘True’ and an unchecked box would mean ‘False’. In forms with multiple options, checkboxes make it easier to answer.
A. Insert one Checkbox in an Excel spreadsheet using the Insert -> Forms -> Checkbox. Then you can copy and paste the checkbox to as many cells as you require. You can also use Ctrl +C and Ctrl +V to copy and paste the checkbox. Another method is to select the cell where the checkbox is inserted and click on the right bottom corner of the checkbox and drag it to as many cells vertically or horizontally as required.
A. Simply press the SHIFT key and click on a Checkbox in Excel to select multiple checkboxes.
A. First ensure that each Checkbox in Excel is linked to a cell to state the result as true or false. Next, select a cell where you want the count of checked boxes to be displayed. Enter the COUNTIF formula in this cell. For example, cells B1 through B12 have checkboxes linked to cells C1 to C12. To display the count of checked boxes in cell C14, type the formula =COUNTIF (C1:C12,TRUE) in cell C14 and click enter.
A. Right-click on the checkbox -> Format control -> Control -> click on Cell link -> assign the cell address to be linked.