How to Make a Tally Counter in Excel (VBA + Formula)

Published by

Jake Lim@jakelim_fps

How to Make a Tally Counter in Excel - My Click Speed

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 Sub

Close 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 Sub

Create 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.