How to Use the Spin Button in Excel (2 Methods)

Method 1 – Using the Form Control Spin Button

Step 1: Calculate PMT Value

Calculate the initial monthly payment for the given values using the PMT function.
Select cell C8 and enter 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.)

Press Enter to apply the formula.

Step 2: Insert Form Control Spin Button

Select the Developer tab from the ribbon.
Choose Insert from the Controls group drop-down menu.
From the Insert drop-down menu, choose Spin Button (Form Control).

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

Right-click the spin button.
From the context menu, select Format Control.

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.
Set the minimum value to 0.
Increase the value to 30000 as the maximum.
Provide the incremental change of 100.
Select the Cell link where you want to put your results.
Click OK.

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

Step 3: Determine Annual Interest Rate

Repeat the previous procedure exactly.
Right-click the spin button.
From the context menu, select Format Control.

The Format Control dialog box will appear.
Choose the Control tab at the top.
As the current annual interest rate is 10, set this value in the Current value section.
Set the minimum value to 0.
Choose 20 as the upper limit.
Provide the incremental change of 1.
Specify the Cell link for the location of your desired results.
Click OK.

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

Select cell C5 and enter the following formula:

=F5%

Press Enter to get the following output.

Excel will have the desired spin button.
If you click the up arrow, the annual interest rate will rise, and the monthly payment will rise in tandem.
If you click on the down button, the annual interest rate will decrease, as will the monthly payment.

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

Method 2 – Calculating Yearly Earnings with the ActiveX Control Spin Button

Step 1: Determine Yearly Earnings

Select cell C6 and enter the following formula:

=C4*12+C5*12

Press Enter to get the yearly earnings.

Step 2: Add ActiveX Control Spin Button

Go to the Developer tab on the ribbon.
Select the Insert drop-down option from the Controls group.
Select the Spin Button (ActiveX Control) from the Insert drop-down option.

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

Right-click on the spin button.
Select Format Control from the Context Menu.

The Format Control dialog box will appear.
Choose the Control tab at the top.
Set the $5400 loan principal amount as the current value in the Current value field.
Set the minimum value to 0.
Increase the value to 30000 as the maximum.
Provide the incremental change of 100.
Set the Cell link where you want to put your results.
Click OK.

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, your monthly salary will decrease, and your earnings will decrease yearly.

Step 3: Compute Additional Monthly Earnings

Repeat the previous procedure exactly.
Right-click the spin button.
From the context menu, select Format Control.

The Format Control dialog box will appear.
Choose the Control tab at the top.
As the current additional monthly earning is $330, set this value in the Current value section.
Set the minimum value to 0.
Choose 1500 as the upper limit.
Provide the incremental change of 10.
Specify the Cell link for the location of your desired results.
Click OK.

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.
If you click the down arrow, the yearly earnings and additional monthly earnings will also decrease.

Things to Remember

✎ The maximum limit for this spin button is 30000. You must alter the cell connection from its original location if you require more than this figure. 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 the Practice Workbook

Download this workbook to practice.

Use Spin Button.xlsx

Get FREE Advanced Excel Exercises with Solutions!