A spin button helps to increase or decrease values easily by clicking on the button easily. But, you can not get negative values directly by using the spin button. You have to follow some methods for that. The focus of this article is to explain how you can use Spin Button  to display negative values in Excel.
How to Display Negative Values Using Spin Button in Excel: 2 Quick Ways
To explain this article, I have taken the following dataset. It contains Monthly Income, Monthly Expenses, and Net Savings. Here, I will use a Spin Button to insert negative values for Monthly Expenses in Excel. And, I will get the Net Savings finally.
1. Use Spin Button from Form Controls to Display Negative Values in Excel
In this first method, I will use Spin Button from Form Controls to display values in Excel, and then I will use a mathematical operation to display the negative values using the button. Let’s explore the steps of this method.
Step-01: Insert Spin Button from Form Control and Link to Cell
In this step, I will insert the Spin Button, and then link a cell to this spin button.
- Firstly, go to the Developer tab from ribbon.
- Secondly, select Insert.
- Thirdly, select Spin Button from Form Control.
- After that, click and drag the mouse cursor where you want the Spin Button.
- Then, you will see that you have inserted a Spin Button in your selected location.
- Next, right-click on the Spin Button.
- Then, select Format Control.
- Now, the Format Control dialog box will appear.
- Go to the Control tab.
- Then, select your Minimum value. Here, I selected 1500 because that is the fixed expense for every month.
- After that, select Maximum value. Here, I select 5500 which is the Monthly Income. You can not spend more than your income.
- Next, click on the marked button to select the Cell link.
- After that, select the cell. Here, I selected cell D7.
- Then, click on the marked button.
- Next, select OK.
- Now, you will see that the minimum value has appeared in the linked cell.
Step-02: Use Mathematical Operation to Change into Negative Value
Now, I will write a formula to turn the positive value into a negative.
- To begin with, select the cell where you want the negative value.
- Then, write the following formula in that selected cell.
=D7*-1
- Next, press Enter and you will get the negative value.
Here, the formula multiplies the value in Cell D7 by -1 and turns the value into a negative value.
Step-03: Hide Value in Linked Cell
Now, I will show you how you can hide the value in the linked cell.
- In the beginning, right-click on the linked cell.
- Then, select Format Cells.
- After that, select Custom.
- Then, write “
;;;“ in the Type section. - Next, select OK.
- Now, you will see that the value in the cell is hidden.
- After that, use the Spin Button to change the Monthly Expenses.
- Finally, I have inserted the Monthly Expenses and got my Net Savings.
Read More: How to Create Spin Button with VBA in Excel (3 Useful Methods)
2. Insert Spin Button from ActiveX Controls to Display Negative Values in Excel
You can also use the Spin Button from ActiveX Controls to display negative values in Excel. Let’s see the steps.
Step-01: Insert Spin Button and Set Properties
Here, I will insert the Spin Button and set properties for it.
- Firstly, go to the Developer tab.
- Secondly, select Insert.
- Thirdly, select Spin Button from ActiveX Controls.
- After that, click and drag your mouse cursor where you want the Spin Button.
- Then, you will see that you have inserted a Spin Button.
- Next, right-click on the button.
- Then, select Properties.
- Afterward, the Properties dialog box will appear.
- Then, select the Categorized tab.
- Next, select Max and Min for the Spin Button.
Step-02: Write VBA Code
Now, I will write a VBA code for the Spin Button.
- In the beginning, right-click on the Spin Button.
- Then, select View Code.
- Consequently, a module will appear with a Private Sub on it.
- Then, write the following code in that module.
Private Sub SpinButton1_Change()
Range("C6").Value = SpinButton1.Value
End Sub
Here, the VBA code will display the negative values you will get from the Spin Button as the value of Cell C6.
- Next, Save the code and go back to your worksheet.
Step-03: Get Negative Values Using Spin Button
Here, I will use the Spin Button to get negative values.
- Firstly, go to the Developer tab.
- Secondly, click on Design Mode to get out of Design Mode.
- Now, click on the Spin Button and select Monthly Expenses.
- In the end, you can see that I have entered the Monthly Expenses.
Practice Section
Here, I have provided a practice sheet for you to practice how to use Spin Button to display negative values in Excel.
Read More: [Solved!] Excel Spin Button Not Working
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
To conclude, I tried to explain how to use Spin Button to display negative values in Excel in 2 quick ways. I hope this article was helpful to you.
