How to Make a Tally Counter in Excel (VBA + Formula)
Excel supports two approaches for building a tally counter: the no-code checkbox + COUNTIF method, and clickable VBA macro buttons for increment, decrement, and reset. This guide covers both methods step by step, plus a multi-category counter setup for tracking multiple items simultaneously.
Need a quick counter without building one? Use our free online tally counter - works instantly in your browser with no setup. For a Google Sheets version, see how to make a tally counter in Google Sheets.
Enable the Developer Tab First
Both the checkbox method and the VBA method require the Developer tab. It is hidden by default. Here is how to enable it:
Windows:
- Go to File > Options > Customize Ribbon.
- In the right panel, check the box next to Developer.
- Click OK. The Developer tab now appears in the ribbon.
Mac:
- Go to Excel > Preferences > Ribbon and Toolbar.
- Under Customize the Ribbon, check Developer.
- Click Save.
Method 1: Checkbox + COUNTIF Formula
This method requires no VBA code. Each checkbox is linked to a cell, and a COUNTIF formula counts how many are checked.
- Go to the Developer tab and click Insert in the Controls group.
- Under Form Controls, click the Check Box icon.
- Draw a checkbox in cell B2 by clicking and dragging.
- Right-click the checkbox and select Format Control.
- In the Control tab, set the Cell link field to C2. This cell shows TRUE when checked and FALSE when unchecked.
- Click OK. Repeat for as many checkboxes as you need (B3, B4, etc.), linking each to the corresponding C column cell.
- In cell D2, enter this formula:
=COUNTIF(C2:C100, TRUE)The formula counts every TRUE value in the linked cell column, which corresponds to every checked box. D2 updates in real time as you check and uncheck boxes.
To reset: Select the linked cells (C2:C100) and press Delete. All linked cells become empty, unchecking all boxes and resetting the count to 0.
Method 2: VBA Macro Buttons
The VBA approach gives you clickable buttons that directly increment or decrement a counter value in a cell. This is the cleanest interface for a standalone tally counter in Excel.
Step 1 - Open the VBA editor and add macros:
- Go to the Developer tab and click Visual Basic.
- In the VBA editor, go to Insert > Module.
- Paste the following code into the module:
Sub IncrementCount()
Range("B1").Value = Range("B1").Value + 1
End Sub
Sub DecrementCount()
If Range("B1").Value > 0 Then
Range("B1").Value = Range("B1").Value - 1
End If
End Sub
Sub ResetCount()
Range("B1").Value = 0
End SubClose the VBA editor (Alt+F4 or the X button). The macros are saved with the workbook.
Step 2 - Create buttons and assign macros:
- Go to Developer > Insert and click the Button (Form Control) icon under Form Controls.
- Draw a button on the sheet. The Assign Macro dialog appears automatically.
- Select IncrementCount and click OK.
- Right-click the button and choose Edit Text to label it "+1".
- Repeat to create "-1" and "Reset" buttons assigned to DecrementCount and ResetCount.
Your counter value lives in cell B1. Click the +1 button to increment, -1 to decrement (stops at 0), and Reset to clear it.
Important: Workbooks containing VBA macros must be saved as .xlsm (Excel Macro-Enabled Workbook). If you save as .xlsx, Excel will ask to remove the macros. Choose .xlsm when saving to preserve your buttons.
Method 3: Multi-Category Counter
Track multiple categories simultaneously by extending the VBA approach. Set up your sheet with category names in column A and counter values in column B (A1/B1, A2/B2, A3/B3, etc.).
Sub IncrementA()
Range("B1").Value = Range("B1").Value + 1
End Sub
Sub IncrementB()
Range("B2").Value = Range("B2").Value + 1
End Sub
Sub IncrementC()
Range("B3").Value = Range("B3").Value + 1
End Sub
Sub ResetAll()
Range("B1").Value = 0
Range("B2").Value = 0
Range("B3").Value = 0
End SubCreate Form Control buttons for each category and assign the corresponding macro. Add as many IncrementX subroutines as you need by copying the pattern and changing the cell reference.
Excel vs Google Sheets for Tally Counters
| Feature | Excel | Google Sheets |
|---|---|---|
| Offline use | Yes | Limited (requires initial setup online) |
| Mobile button support | No (VBA buttons) | No (Apps Script buttons) |
| Real-time collaboration | Limited (SharePoint/OneDrive) | Yes, built-in |
| Macro language | VBA | JavaScript (Apps Script) |
| File format for macros | .xlsm required | No special format needed |
| Setup time | Moderate (Developer tab + VBA) | Moderate (Extensions + script) |
Troubleshooting Common Problems
| Problem | Fix |
|---|---|
| Macros not working after reopening | File must be saved as .xlsm. Resave and re-enable macros when prompted. |
| "Cannot run the macro" error | Check that macro security allows macros from the current file. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and select "Enable all macros" or "Enable VBA macros". |
| Button click selects the button instead of running macro | Click outside the button first to deselect it, then click the button normally to run the macro. |
| Checkbox not linking to cell correctly | Right-click the checkbox, select Format Control, and verify the Cell link field shows the correct cell reference. |
| Count shows 0 even after checking boxes | Confirm the COUNTIF range matches your linked cell column. The formula should reference the C column (or wherever you set the cell links), not the B column where checkboxes sit. |
Prefer a ready-made counter? Our free online tally counter and clicker counter work with one click and no setup. Sheets user instead? Follow how to make a tally counter in Google Sheets. For scoring games between two teams, the scoreboard tracks both sides at once.