Contents
Helen Bradley explains how to add interactive elements to an excel sheet using Scroll bar и Counter.
Whenever a user selects from a discrete set of options what data to enter into an Excel sheet, you can save time by automating the entry process. This can be done in various ways, and one of them is to use interactive elements. Spin Button (Counter) or Scroll Bar (Scroll bar). Today I will introduce you to using Counter и Scroll bars, and also show how to add an interactive element to a graph in Excel Counter.
Open the Developer tab
In Excel 2007 and Excel 2010 items Counter и Scroll bar available on tab Developer (Developer). If this tab does not appear for you, do the following:
- in Excel 2010 open Fillet > Options > Customize Ribbon (File > Options > Customize Ribbon) and in the panel on the right, check the box next to the tab name Developer (Developer).
- in Excel 2007 click the button Office, Select Excel Options (Excel Options) and so on under Popular (Basic) enable the option Show Developer tab in the Ribbon (Show the Developer tab on the Ribbon).
To see the tools, go to the tab Developer > Insertion (Developer > Insert) and select an element Spin Button (Counter) or Scroll Bar (Scrollbar) from group Form Controls (Form controls). It is extremely important to choose from this group, and not from ActiveX Controls (ActiveX controls) because they work completely differently.
Drag to Excel worksheet Counter и Scroll bar. Note that you can reposition these elements as well as rotate them vertically or horizontally. To move or resize a control, right-click on it and then resize or drag.
To see how it all works, right-click on an object and select Format Control (Object format). In the dialog box that opens, on the tab Control (Control) are options to configure Counter. At its core, Counter puts some value in the cell, and you, by pressing the arrows, increase or decrease it.
Element Counter has restrictions: the value must be an integer from 0 to 30000, the parameter Incremental Change (Increment step) must also be any integer between 1 and 30000.
Set CurrentValue (Current value) equal to 50, Minimum Value (Minimum value) equal to 0, Maximum Value (Maximum value) equal to 300 и Incremental Change (Change step) equal to 10. Click on the field Cell Link (Cell Link), then select the cell A1 and close the dialog.
Click away from Counterto deselect it, and click the arrows. You will see that with each click the value in the cell A1 changes. You can increase the value up to 300, but no more, and decrease it to 0, but no less. Note that the value changes in increments of 10.
Making the Counter more useful
You can use Counter so that the user can enter values on the sheet by simply pressing a button, instead of manually entering values from the keyboard. You probably have a question: what if the values that the user must enter are not integers in the range from 0 to 30000?
There is a solution – use an intermediate cell to calculate the value you need. For example, if you want the user to enter values between 0% и 5% in increments 0,1%, you need to scale the value that the counter gives to get the result from 0 to 0,05 in increments 0,001.
There are many options for how this can be done mathematically, and if your solution works, it doesn’t matter how you did it. Here is one possible solution: right-click on the counter, select Format Control (Object Format) and set Minimum Value (minimum value) = 0, Maximum Value (Max value) = 500 and Incremental Change (Change step) = 10. Link to cell A1. Next in the cell A2 write down the formula =A1/10000 and apply a percentage number format to it with one decimal place.
Now, by clicking on the buttons of the counter, you will receive in the cell A2 exactly the result that is needed – the value of the percentage between 0% и 5% in increments 0,1%. Cell value A1 created by the counter, but we are more interested in the value in the cell A2.
Whenever you need to get a value more complex than an integer, use a similar solution: scale the result received from the counter and turn it into the desired value.
How the scroll bar works
Scroll bar works in the same way as Counter. In addition, for Scroll bars You can adjust the setting Page Change (Increment per page), which determines how much the value changes when you click on the scroll bar on the side of its slider. Parameter Incremental Change (Change step) is used when clicking the arrows on the edges Scroll bars. Of course, you need to set up the link of the scrollbar with the cell in which the result should be placed. If you need to scale the value, you will need an additional cell with a formula that will change the value received from Scroll barsto get the correct end result.
Imagine an organization that provides loans from $20 to $000 in increments of $5. You can use the Scroll Bar to enter the loan amount. In this example, I have linked to a cell E2, And in C2 introduced the formula =E2*10000 – this cell shows the desired loan amount.
The scrollbar will have options: Minimum Value (minimum value) = 2, Maximum Value (Max value) = 500, Incremental Change (Change step) = 1 and Page Change (Page increment) = 10. Incremental Change (Step) should be set to 1 to allow the user to fine-tune the multiple of $10. If you want to create a user-friendly solution, it’s important that the user can easily get the result they want. If you set the Change Step to, for example, 5, the user will be able to change the loan amount in multiples of $50, which is too large a number.
Parameter Page Change (Increment by Page) allows the user to change the loan amount in increments of $100, so they can quickly get closer to the amount they are interested in. The scroll bar slider is not configurable in any way, so the user is able to instantly go from $000 to $20 simply by dragging the slider from one end of the scroll bar to the other.
Interactive chart with counter
To see how these two objects work together, consider a table with sales values for the period June 1, 2011 to September 28, 2011. These dates, when converted to numbers, range from 40695 to 40814 (dates in Excel are stored as the number of days since January 0, 1900).
In a cell C2 there is this formula:
=IF($G$1=A2,B2,NA())
=ЕСЛИ($G$1=A2;B2;НД())
This formula is copied to the rest of the cells in the column C. To cells C2: C19 applied conditional formatting, which hides any error messages, because formulas in these cells will show many error messages #AT (#N/A). We could avoid having an error in the column Cby writing the formula like this:
=IF($G$1=A2,B2,"")
=ЕСЛИ($G$1=A2;B2;"")
But in this case, the graph will show a lot of zero values, and we do not need this. Thus, an error is just the desired result.
To hide errors, select cells in a column C and press Conditional Formatting > New Rules (Conditional Formatting > New Rule), select a rule type Format Only Cells That Contain (Format only cells that contain), in the first dropdown select Errors (Errors) and then in the format settings set the font color to white so that it blends into the background of the cells – this is the most effective way to hide errors!
In a cell G1 there is this formula =40000+G3, and the cell G3 we will make connected with Counter. Let’s set the following parameters: CurrentValue (Current value) = 695, Minimum Value (minimum value) = 695, Maximum Value (Max value) = 814, Incremental Change (Change step) = 7 and Cell Link (Link to cell) = G3. Test Counterby clicking on its arrows, you should see in the column C the value corresponding to the date in the cell G1.
Minimum Value (Minimum value) is a number, adding 40000 to which we get the date June 1, 2011, and adding Maximum Value (Maximum value) and 40000, – we get the date September 28, 2011. We set Change step equal to 7, so the dates that appear in the cell G1, come at intervals of one week to match the dates in the column A. With every click on the arrows Counter, cell content G3 changes and shows one of the column’s dates A.
Adding a chart
Create a chart from a range of data A1: C19, choose the type Column chart (Bar chart). Size the graph so that it covers the column C, but so that the first row of the Excel sheet is visible.
To customize the graph view, you need to right-click on a single column showing the value for Series 2 (Row 2), choose Change Series Chart Type (Change Chart Type for Series) and then Line Chart With Markers (Chart with markers). Next, right-click on the marker, select Format Data Series (Format of the data series) and set up a pretty marker. Right-click on the marker again and select Add Data Labels (Add data labels), then on the Legend to select it and remove it.
Now when you press the buttons Counter, the marker jumps across the graph, highlighting the sales figure for the date that appears in the cell G1, and a label with the amount of sales is displayed next to the marker.
There are countless ways to use Counters и Scroll bars on Excel sheets. You can use them for data entry or, as I showed in this article, to make charts more interactive.