Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use Spin Button to Display Negative Values in Excel

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.


Download Practice Workbook

You can download the practice workbook from here.


2 Quick Ways to Display Negative Values Using Spin Button in Excel

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.

Dataset to Display Negative Values Using Spin Button in Excel


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.

Use Spin Button from Form Controls to Display Negative Values in Excel

  • After that, click and drag the mouse cursor where you want the Spin Button.

Insert Spin Button from Form Control and Link to Cell to Display Negative Values

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

Using Context Menu to Display Negative Values Using Spin Button in Excel

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

Format Controls Dialog Box for Displaying Negative Values Using Spin Button in Excel

  • 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

Use Mathematical Operation to Change into Negative Value

  • Next, press Enter and you will get the negative value.

Using Formula to Display Negative values Using Spin Button in Excel

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.

Hide Value in Linked Cell

  • After that, select Custom.
  • Then, write;;; in the Type section.
  • Next, select OK.

Format cells Dialog Box to Change the Format of Lined Cell to The Spin Button to Display Negative Numbers in Excel

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

Insert Spin Button from ActiveX Controls to Display Negative Values in Excel

  • After that, click and drag your mouse cursor where you want the Spin Button.

Insert Spin Button and Set Properties to Display Negative Values in Excel

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

Setting Minimum and Maximum Values for Spin Button to Display Negative Values in Excel


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.

Write VBA 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

VBA Code for Spin Button to Display Negative values in Excel

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.

Get Negative Values Using Spin Button

  • Now, click on the Spin Button and select Monthly Expenses.

  • In the end, you can see that I have entered the Monthly Expenses.

Read More: [Solved!] Excel Spin Button Not Working (2 Reasons with Solutions)


Practice Section

Here, I have provided a practice sheet for you to practice how to use Spin Button to display negative values in Excel.

Practice Sheet for How to Use Excel Spin Button to Display Negative Values


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. Stay connected with ExcelDemy for more articles. If you have any questions, feel free to let me know in the comment section below.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo