How to count the number of items through Spin Button in Excel?

—

by

Himanshu
in

You can use a Spin Button in Excel (from the Form Controls) to dynamically count and display items, such as incrementing a value, controlling a formula, or navigating records.

Here’s a step-by-step guide on how to use a Spin Button to count the number of items in Excel:

🔢 Goal: Use a Spin Button to Count Items in Excel

For example, if you want to count how many items are in a list, or simulate a counter that you can increase/decrease with a Spin Button.

✅ Step-by-Step Guide

Step 1: Enable the Developer Tab

  1. Go to File > Options > Customize Ribbon.
  2. Tick Developer on the right side and click OK.

Step 2: Insert the Spin Button

  1. Go to the Developer tab.
  2. Click Insert under the Controls group.
  3. Under Form Controls, click on the Spin Button (Form Control).
  4. Click and drag on the sheet to place the Spin Button.

Step 3: Link the Spin Button to a Cell

  1. Right-click the Spin Button and choose Format Control.
  2. Under the Control tab:
    • Current value: 1
    • Minimum value: 1 (or 0 depending on your need)
    • Maximum value: e.g., 100
    • Incremental change: 1
    • Cell link: Choose a cell (say B1)
  3. Click OK.

Now, every time you click the up/down arrows of the Spin Button, the value in B1 will increase or decrease.

Step 4: Use That Cell to Count Items

Let’s say you have a list of items in A2:A100. You want to count how many items are currently visible or considered.

You can write in another cell (e.g., C1):

=COUNTA(A2:INDEX(A2:A100,B1))

This formula will count non-empty cells from A2 to the row determined by the Spin Button’s value in B1.

🎯 Example Use Cases

  • Count tasks completed (Done list)
  • Browse through list items one by one
  • Display dynamic summaries or progress bars
  • Interactive dashboards

🎓 Want to Learn More Excel Automation Tricks?

If you enjoy interactive features like this, you’ll love the full Excel course that covers Form Controls, Data Analysis, Dashboards, VBA, and more:

🔗 Mastering MS Excel – A Comprehensive Training Course

✅ Includes hands-on examples
✅ Build automation without heavy coding
✅ Great for professionals, students, and entrepreneurs

👉 Click to Enroll Now

On sale products