In business, I often see disputes between the finance and operations departments on which metric is a more important indicator of performance. Operational leaders (more often than not) like to see their numbers in terms of units/quantities and the finance executives/leaders want to see everything in dollars or currency. This is where the Excel option button form control comes in handy (note that if you are part of a finance team, you can also use this logic to switch between local currency versus your company’s global standard currency). I’m going to show you how you can use the option button to switch between quantity/units and currency all in one graph.
Download the Workbook for Free Before we Begin
The Excel file is indeed free. When you click the ‘GET IT FREE’ button be sure to enter in $0 for the value and this will remove the request for your credit card information. All you’ll need is your email address!
Overview of What you Are Going to Learn
◊ How to setup and use the Excel option button form control.
◊ How to use the choose formula in collaboration with the Excel option button to make your data set and graph dynamic.
◊ How you can apply conditional custom number formatting to your number axis to change how values are displayed based on the size of the number.
◊ How to setup a dynamic chart title within an Excel object.
Step # 1: Setting Up the Option Buttons On Your Chart Sheet
In order for this to work, you’re going to have to setup the developer tab in Excel. Here is a quick tutorial from Microsoft on how you can setup the tab. Let’s now dive into how we can setup the option buttons with the help of the developer tab.
How to Setup the Option Button Form Control
Now that you’ve gotten that out of the way, let’s move over to the developer tab in the quick access ribbon and select the option button form control.
◊ Developer » Insert » Option Button (Form Control)
◊ Now use your cursor to add the option buttons to your worksheet.
⇒ Before copying and pasting a new option button, setup the format control by right clicking the option button » format control » control » update the cell link. The cell link will be the location on your data analysis sheet (not the dashboard sheet) that will contain an index number depending on which option button the user selects.
⇒ Use Ctrl + C to copy the first option button and Ctrl + V to paste another.
⇒ Update the names of the option buttons to best suit your needs.
⇒ Add a shape and send it backwards to add a little bit of style.
Step # 2: How to Use the Choose Formula With Your Option Buttons To Make Your Data Set and Graph Dynamic
The choose formula is the key to making your data dynamic. Depending on the index value that is created through the Excel option control buttons (i.e. option button #1 results in index number of 1 and option button #2 if selected results in index number of 2), a different set of values will be used and displayed. You can use this formula as an array as well which will allow you to populate multiple cells based on the starting/ending position of your value range.
=choose(index_num,value1,value2,…)
A Step by Step Guide on Using the Choose Formula with The Option Button to Build a Dynamic Chart
The way that I’ve decided to set this up is to use the choose formula to create a separate set of data that will be used for the chart. The formula will refer to the index number to determine which data set will be used for your chart. If option button # 1 is used, the index number will be 1 so our data set will refer to the list of values that are associated to that option (in our case the value shipped).
Building a Dynamic Title with the Choose Formula
◊ The choose formula is pretty straight forward. You decide what value or range of values you would like option button #1, and #2 to display (below you’ll see that if the user selects value shipped, the index_num in cell B8 will be 1, if you select quantity shipped, the index_num in cell B8 will be 2). Now you get to decide which value you want to display if the user selects option button #1 and option button #2. In the choose formula, if the index_num is 1, the cell will reference and display the value in the value1 portion of the formula and if the index_num is 2, the cell will reference value2 of the formula.
Building a Dynamic Data Set with the Choose Formula
◊ You are going to use the choose formula again to display the range of values (an array) associated to the index_num selected. If you select “value shipped”, our index_num in cell B8 will be 1 and you will reference a range (E9:E20) in the Value1 section of the formula (Total Value Shipped range). The value 2 section of the formula will reference a different range (H9:H20) which will be displayed if you select option button #2 (Quantity Shipped). Follow the steps below to finalize your dynamic data set.
⇒ Learn about array formulas here.
Building your Chart with the New Dynamic Data Set
Now that you have your dynamic data set in place, you can now create a chart that will reference that data set.
⇒ Select the insert tab in the quick access ribbon and choose the bar graph chart type on the dashboard worksheet.
⇒ A blank chart will be created and now you will setup the data by right clicking the blank chart and clicking select data.
⇒ Next you are going to select your data source for the chart. A quick way of doing this is using the chart data range.
#1 ⇒ Select the chart selection tool.
#2 ⇒ Select your data source by dragging your mouse and highlighting your newly created dynamic data range.
⇒ You can now update the chart series title by referencing your dynamic title cell. This will allow the chart title to change as your series updates through the user interaction with the option buttons.
⇒ You can now proceed to hit OK from the select data source screen and your dynamic chart will have now been built. Notice now that you can use the option buttons to update your chart data.
Step # 3: Applying Conditional Custom Number Formatting to our Axis Values to Switch Between Currency and Number
Now that you’ve got your dynamic chart setup, you realize that the format of the numbers on the axis are not ideal for management. You want to make sure that the dollar values are showing in dollars and in thousands and you want to display your quantity to the nearest whole number with a comma separating the thousands. Let’s have a look at how you can utilize custom number formatting within your chart settings to do this.
Setting Up your Chart with Conditional Custom Number Formatting
Now that you have your chart setup, you can go ahead and apply some formatting to get the perfect look and feel. Once you’ve applied and changed the colors and fonts on your chart, you’ll then want to update the number formatting through axis formatting.
⇒ To learn more about custom number formatting, check this article out – A Comprehensive Guide To Number Formats in Excel
Once you’ve applied the custom conditional number formatting to the number axis, you’re going to add a few minor tweaks to the chart formatting to get the look and feel you are looking for. Here’s the result:
Step # 4: How to Setup an Object to Display a Dynamic Chart Title
This is the last step of your process. You can add an object/shape into your chart area to display a dynamic title that will match whatever option button you end up selecting.
Setting Up the Dynamic Chart Title
This last step in finalizing your chart is to add a dynamic chart title. In order to do this, you are going to create and overlay an object over your chart. You can choose whatever shape you would like. Let’s have a look below to see what you’ll need to do to display the title dynamically on your chart.
Now that your chart has a dynamic title, here’s what your final product should look like.
Summary
The Excel option button is one of the best ways to consolidate multiple sets of related or non-related data in one chart. It allows the consumer to interact with the data and chart to select their preferred visual display. If you are the person building the chart and dashboard, this is a great way to satisfy the needs of multiple different audiences or even allows you to provide different ways of looking at multiple sets of data. In this article, we were able to satisfy the needs of two different management groups by switching between quantity and value. Let’s quickly review the important points that you should take away from this post.
⇒ Setup the developer tab and use the Excel option button form control to create a data index cell. This will allow you to know which option has been selected by the dashboard consumer.
⇒ Once you have your index number cell setup, you can then refer to that cell using the choose formula. The choose formula uses this index cell to determine what will be displayed. If option button # 1 is selected, the Value1 cell reference or cell range will be displayed (if it is a range, the choose formula will need these parentheses added to each end of the formula { } indicating an array – this is done using Ctrl + Shift + Enter). If it is a range, you will drag your formula down as many cells as are required by your referring value1/value2 data range.
⇒Now that the you have a dynamic data set, you can setup a chart that refers to this range. This dynamic data set will change depending on what option button is selected by the consumer. Once your chart is setup, you are then able to use conditional customer number formatting to display currency versus units/quantity.
⇒ The last thing that you did was create a dynamic chart title by referring an object to the dynamic cell title that was created in your second step using the choose formula. This step is accomplished by selecting the object, going to the formula tab, typing the “=” sign and then selecting your dynamic title cell that you created in the second step.
I think we have covered everything that you’ll need to know to keep both the finance and operation managers and executives happy. Now that I’ve gone through the step by step process of showing you how the option button can be used, how would you use the Excel option button to simplify your day to day activities?
Hello
In the last step to setup a dynamic chart title, I am trying to use the CHOOSE function. One of the characters in the source cell of text for the dynamic title is a superscript. It does not copy over except as inline text. Is there a way to copy over that format property using this dynamic method?
Khan