Home and Learn: Microsoft Excel Course
Use a Spin control in Excel to apply a discount
What we'll do in this lesson is to add a spinner control to a cell in an Excel spreadsheet. When we click the spinner control, it will increase or decrease a value in a cell. This value will be used to calculate a discount on a total price. The spreadsheet will look like this, when you're done:
The cell G1 contains the spinner control. The cell F1 is the percentage discount that we'll use in the D column, for the Discounted Total. Clicking the top spinner will increase the discount, while clicking the lower spinner will decrease the discount. The value in cell D2 will update every time the spinner is clicked. Let's make a start.
For this tutorial, you'll need to add the Developer ribbon to Excel. To do that, click the File ribbon at the top of Excel. From the File screen, select Options from the menu on the left. When you get the Excel Options dialogue box up, select Customize Ribbon on the left. From the items on the far right, put a check in the box for Developer:
Click OK to return to Excel, and you should see the Developer ribbon:
Make sure to increase the height of the first row, as we'll be adding a spinner control in cell G1.
In cell C2, enter a formula to get a total, which is just number sold multiplied by the item price:
=A2 * B2
Cell D2 is where we'll enter a formula to calculate the discount price. This formula will be based on whatever percentage we get from the spinners.
Now add a value in cell F1. Enter 10, meaning 10 percent discount.
To add a spinner to a cell, click on the Developer ribbon at the top of Excel. Locate the Controls panel, and the Insert item:
From the Insert menu, locate the Spin Button control:
Now move your mouse pointer to the top left of cell G1. Hold your left mouse button down. Keep it held down and draw a Spin Button in the cell. It should look like this:
To select a control, you click it with the right mouse, not the left. It should then have the white sizing handles, as in the image above.
Right click the Spin Button again to see the following menu appear:
From the menu, select Format Control. You will then see the following dialogue box:
Current value is whatever you have in the selected cell. The Minimum value is the lowest you want to set for your Spin Button. If you click the decrease button, it won't go below the value you set in this box. Likewise, the Spin Button won't go above the Maximum value that you type (we've set a value of 50 here). The Incremental change is how many you want to go up or down by when the buttons are clicked. You can only have whole numbers here. The Cell link is which cell you want to change. We want to change cell F1. If you can't see $F$1 in the text box then click the up arrow to the right:
The dialogue box shrinks:
When the dialogue box is the smaller sized one above, select the cell F1 and click the arrow again. The dialogue box will grow to its normal size.
Now click OK on the dialogue box.
The Spin Button will still be selected. So click anywhere in the spreadsheet with your left mouse button. This will deselect the Spin Button.Now click your Spin Button. When you click the up arrow the value in cell F1 will increase. When you click the down arrow the value on F1 will decrease. You should find that you can't go below 0 and into negative numbers, nor can you go up beyond 50.
However, the value in cell F1 is not doing anything. We need to hook it into a formula in cell D2.
Click into cell D2 and enter the following formula:
=C2 - (($F$1 / 100) * C2)
Whatever we have in the round brackets will be calculated first. This is to divide the number in F1 by 100. This will get you something like 0.1. This is then multiplied by the value in cell C2. This will get you something like 10. This is then deducted from the value in cell C2 again. We need to do it this way because the Spin Button can't recognize a percentage value directly.
Press the enter key on your keyboard to complete the formula.
When you have a value of 10 in cell F1 the Discounted Total will be 90. Increase the percentage in cell F1 to, say, 15. Cell D2 should then be 85.
And that's it for Spin Buttons. They can make your life a lot easier, once you know how to set them up!
Exercise
Add a heading to cell E1. Type Add Percentage as the heading. Create a formula in cell E2 that adds whatever percentage is in cell F1 to the total in cell C2. So if you had £100 pounds in cell C2 then setting the percentage in F1 to 10 would result in a figure of £110 in E2.
HINT: you only need to change one thing in the previous formula from cell D2.
In the next part, you'll see how to add an error message to an Excel spreadsheet.
Add your own Error Messages to Excel -->
<--Back to the Excel Contents Page