Perhaps one of the most underused features of Excel are its Form tools, such as the Spin button, which lets you enter data into a worksheet using a clickable spinner. Here we’ll show you how to put one to work to control an animated chart so that when you click on the spinner, the chart will change with each click. This project can be completed in any version of Excel.
To begin, open a new worksheet and enter some data. Our data includes information for a period of seven months for two hypothetical offices and it starts in column B with the office locations. Each successive column contains data for that office for a one-month period. Above each column heading is a number, starting with 1 on the first column.
Copy the row headings from B3 and B4 to cells B6 and B7, enter the number 1 in cell A5 and then use the formula in step 1 of the guide below to populate a second copy of the worksheet. The formula for cell C6 is =IF($A$5=C$1,HLOOKUP($A$5,$B$1:$I$4,3),0) and for C7 it’s =IF($A$5=C$1,HLOOKUP($A$5,$B$1:$I$4,4),0). Enter these and then copy them.
To create the chart, select cells B2:I2 and hold Ctrl as you select the range B6:I7 and create a column chart.
To add a spin button to animate the chart, first ensure the Developer toolbar is visible in Excel 2007/2010. In Excel 2003, use View —> Toolbars —> Forms. Click Insert and then the ‘Spin Button’ control from the Forms collection. Drag to create it on your worksheet. Step 2 below shows how to format the control.
In cell A6, type the following formula: =HLOOKUP(A5,B1:I4,2). This sets the value in cell A6 to a date matching the date of the data in the chart. To use this for the chart title, add a title, click in it and click on the formula bar. Type this formula and press Enter: =Sheet1!$A$6. The chart title will then show this date.
If you click the spin button, you’ll see the chart change. Unfortunately, the Y axis probably changes, too, so the chart will tend to jump around a bit. The solution is to fix the axis minimum and maximum values. Follow step 3 below to set the minimum to the lowest value to plot (or zero) and the maximum to the largest value in your data set. This solution can be easily scaled up or down to show more or less data. If you place the second data set in an out-of-the-way place on the worksheet and hide column A, you’ll have a neat worksheet.
To make things easier for you to follow, we've provided a step-by-step guide below. Firstly, to create the data to plot, in cell C6, type in =IF($A$5=C$1,HLOOKUP($A$5,$B$1:$I$4,3),0) then follow the steps below.
Add a spinner to an Excel chart
