We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)
You tried to make a slider with percentages in Excel, but it didn’t work! It’s NOT you, it’s Excel.
The trouble with this is that the Form Control inputs only accept whole numbers. This means no decimals, percents, or negative numbers.
To use percentages with your slider, you need to do a little extra formatting. But it’s easy!
Before you start, you’ll need to have the Developer tab enabled. If it’s enabled, you’ll see it in your ribbon.
Create a Slider that Shows Percentages in Excel
WATCH: How to Make a Slider with Percentages in Excel – Tutorial
Insert a Slider
To insert a slider, click on the Developer tab and go to the Controls section.
Under Form Controls, click on Scroll Bar (this is the actual name of the Slider).
Your cursor should now look like a small plus sign ( + ). This means that you can now click anywhere on your sheet to insert your Scroll Bar.
Tip: Press the Alt key while you drag the Scroll Bar into your sheet to snap it into a cell.
Use the edges to position and size it how you want.
Format the Slider Controls
Next, you need to format the slider controls. The Form Control fields drive the slider results.
To set these, right-click on your slider and select Format Control.
So that your slider displays percentages, set:
- Minimum Value: to 0 (or 1, if you don’t want to display a zero)
- Maximum Value: to 100
- Incremental Change: to 1
- Set to a different value, if you need to. For example, 5 for a 5% change each time your slider is moved.
- Page Change: to 10
- Again, or preferred value
- Cell Linked: select a cell (in an inconspicuous location) where the value of the scroll bar will populate
Don’t forget that the trouble with creating a slider that shows percentages is the input fields and its output only use whole numbers.
To work around this, you can set your linked cell in an inconspicuous location.
TIP: Consider setting your linked cell in a different sheet, then hiding the sheet before finalizing your project.
Then, in the location where you actually want to display the percentage, create a link referencing the linked cell and:
- Format it as a percent
- Use a formula to divide it by 100
For example, if you placed your linked cell in a different sheet, the formula where you are displaying your percentage might look like:
=Sheet2!A2/100
Now, moving the slider will display a percentage between 0 and 100%!
Make a Slider with Negative Value Percentage in Excel
But what if you instead need to make a slider that also reflects a negative value percentage?
To do this, you’ll need to make a small edit to the Form Control values and change the Maximum Value to 200.
Then, in the cell where you are displaying the percentage, apply an IF function like:
IF(A2>=100, (A2/100)-1,-(A2/100))
*replacing A2 with the cell linked to your Scroll Bar
And don’t forget to apply percent formatting to the cell!
How this IF function works:
If your Linked Cell has a value greater than or equal to 100, then the value will be divided by 100 and subtract 1 – because remember the Max value is 200.
If the value is less than 100, it will divide it by 100 and make it negative, resulting in a negative percent.
FYI, this was created using the desktop version of Excel in Microsoft 365.
Leave a Reply