How to Use Spin Button in Excel (2 Suitable Examples)

You may occasionally need to change the cell value. Changing a cell’s value every time is challenging. In that case, Excel provides you with a perfect spin button solution, allowing you to quickly increase and decrease cell value. If you are looking for some special tricks to know how to use Spin Button in Excel, you’ve come to the right place. This article will go over the ways to add different spin buttons to Excel and two ways to use it. Let’s follow the complete guide to learn all of this.

How to Use Spin Button in Excel: 2 Suitable Examples

The following section demonstrates the use of spin buttons in Excel using two practical and tricky examples. Here, we would like to show two examples including the monthly payment of a taken loan amount and the yearly earnings of an employee. Spin buttons come in two different varieties: Form Control spin buttons and ActiveX Control spin buttons. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

1. Use Form Control Spin Button to Calculate Monthly Payment in Excel

In this example, we’d like to figure out how much a borrowed loan will cost each month. Here, we use a dataset that contains information about the loan’s principal, annual interest rate, and term. By using these, we will calculate the monthly payment. Here we are going to demonstrate how to use the Form Controls spin button in Excel. Let’s walk through the following steps to do the task.

📌 Step 1: Calculate PMT Value

First, we have to calculate the initial monthly payment for the given values using the PMT function.
Then, select cell C8 and write down the following formula.

=PMT(C5/12,C6*12,C4)

Here, the PMT function is used to get the loan payment for different payment frequencies (weekly, monthly, quarterly, etc.)

Next, press Enter to apply the formula.

📌 Step 2: Insert Form Control Spin Button

Now, we are going to add a spin button. Select the Developer tab from the ribbon.
Next, choose Insert from the Controls group drop-down menu.
After that, from the Insert drop-down menu, choose Spin Button (Form Control).

The plus (+) sign then appears as the mouse cursor.
Next, to change Excel’s spin button, drag the plus sign.

Following that, right-click the spin button.
From the context menu, select Format Control.

Subsequently, the Format Control dialog box will appear.
Choose the Control tab at the top first.
Set the $15400 loan principal amount as the current value in the Current value field.
Next, set the minimum value to 0.
Then, increase the value to 30000 as the maximum.
Then, provide the incremental change of 100.
Select the Cell link where you want to put your results.
Finally, click on OK.

Consequently, you will get the desired spin button in Excel.
The loan principal amount and the monthly payment will both increase if you click the up button at this point.
In addition, the monthly payment will also decrease if you click the down arrow after the loan principal amount has decreased.

📌 Step 3: Determine Annual Interest Rate

Again, we would like to add another spin button for the annual interest rate.
So, repeat the previous procedure exactly.
In this section, you must modify the Format Control.
Next, right-click the spin button.
From the context menu, select Format Control.

Consequently, the Format Control dialog box will appear.
First, choose the Control tab at the top.
As the current annual interest rate is 10, set this value in the Current value section.
Next, set the minimum value to 0.
After that, choose 20 as the upper limit.
Then, provide the incremental change of 1.
Next, specify the Cell link for the location of your desired results.
Finally, click on OK.

Since we require percentages of the annual interest rate, we use different cell links here.
In its original place, the cell reference will be used as a percentage.

Now, select cell C5 and write down the following formula.

=F5%

Then, press Enter to get the following output.

As a result, Excel will have the desired spin button.
The annual interest rate will now rise if you click the up arrow, and the monthly payment will rise in tandem.
Then, if you click on the down button, the annual interest rate will decrease. At the same time, the monthly payment will also decrease.

💡 Note:

You must take some important actions to obtain a spin button before you can add it to Excel. These procedures are all fairly simple to comprehend. You must first add the Developer tab to the ribbon before taking any further action.

2. Calculate Yearly Earnings with ActiveX Control Spin Button

In this example, we’d want to compute an employee’s yearly wages. Earnings during a year include both monthly and extra revenue. Combining them for a year will yield the needed yearly profits. Follow the steps exactly. Here, we will show another way to use the ActiveX Control spin button. Let’s walk through the following steps to do the task.

📌 Step 1: Determine Yearly Earnings

First, we have to calculate the yearly earnings for the initial monthly salary and additional monthly earnings.
For this purpose, select cell C6 and write down the following formula.

=C4*12+C5*12

Next, press Enter to get the yearly earnings.

📌 Step 2: Add ActiveX Control Spin Button

Next, we have to add a spin button for the dataset.
To do this, go to the Developer tab on the ribbon.
Then, select Insert drop-down option from the Controls group.
Next, select Spin Button (ActiveX Control) from the Insert drop-down option.

After that, the mouse cursor becomes the plus (+) sign.
Drag the plus sign to give the shape of the spin button in Excel.

Then, right-click on the spin button.
Select Format Control from the Context Menu.

Consequently, the Format Control dialog box will appear.
Choose the Control tab at the top first.
Set the $5400 loan principal amount as the current value in the Current value field.
Next, set the minimum value to 0.
Then, increase the value to 30000 as the maximum.
Then, provide the incremental change of 100.
Next, set the Cell link where you want to put your results.
Finally, click on OK.

Therefore, you will receive the desired spin button in Excel.
The monthly salary will increase if you click on the up button. Additionally, yearly earnings will increase.
When you click on the down button, the monthly salary will decrease. Additionally, earnings will decrease yearly.

📌 Step 3: Compute Additional Monthly Earnings

Then, we would like to add another spin button for the additional monthly earnings.
Repeat the previous procedure exactly.
In this section, you must modify the Format Control.
Next, right-click the spin button.
From the context menu, select Format Control.

Consequently, the Format Control dialog box will appear.
First, choose the Control tab at the top.
As the current additional monthly earning is $330, set this value in the Current value section.
Next, set the minimum value to 0.
After that, choose 1500 as the upper limit.
Then, provide the incremental change of 10.
Next, specify the Cell link for the location of your desired results.
Finally, click on OK.

As a result, Excel will display the desired spin button.
The additional monthly earnings and yearly earnings will both increase if you click the up button at this point.
Then, if you click the down arrow, the yearly earnings will also decrease along with the additional monthly earnings.

💬 Things to Remember

✎ The maximum limit for this spin button is 30000. If you require more than this figure, you must alter the cell connection from its original location. Then, in the original location, utilize this cell reference and multiply it by the needed number.

✎ This button aids in displaying future output. Using this, we may take the first step toward deeper measurement, which will aid in loss reduction.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

Use Spin Button.xlsx

Conclusion

That’s the end of today’s session. I firmly believe that from now you may be able to use spin control in Excel. If you have any queries or recommendations, please share them in the comments section below.

Get FREE Advanced Excel Exercises with Solutions!