It has been far too long since I last posted on my blog. Between removing the popcorn ceilings throughout our entire upstairs of our home and just the general craziness of the holiday season, I’ve had very little time to do anything. One of my goals in 2015 will be to try and post once a week to my blog. That seems like such an easy feat but for some reason I always seem to struggle to stay consistent in getting the content out on such a reserved schedule. That all being said, I’m excited to get back into the swing of things and today I’ll be showing you how you can use the option button form control in Excel to control your dashboard number formatting.
When we are talking about number formatting, I’m talking about how the numbers and the text within your dashboard are being displayed. Often times the dashboard that you are building will have multiple different users and consumers and as a result, each individual may have a preference on how the numbers are being displayed. In this post we are going to review how you can use form controls, or more specifically the option buttons, to allow the user to select their preferred number formatting for the data presented. This method only requires to separate steps to get things going.
STEP 1 : Create and setup the option buttons for your dashboard
STEP 2: Using conditional formatting with the option button cell link to apply interactive custom number formatting to your data
The Final Product
Download the Workbook
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!
GET IT FREE
Creating the Option Buttons for your Dashboard
This step of the process is probably the easiest of the two. The one thing that you do want to make sure of is to setup the developer tab in the quick access ribbon. Let’s have a quick look at how to setup the developer tab.
Head over to File ⇒ Excel options ⇒ Customize Ribbon ⇒ Main Tabs. Once you’ve arrived here do the following in the screen shot below:
After you’ve added the developer tab, you can now add the option button form control to your dashboard. To do this, go to the developer tab and then add however many different interactive number formats you would like to add to your dashboard. In the case of this example, we will add three different options.
Adding the Excel Option Buttons from the Developer Tab
STEP #1: Go to the developer tab of the quick access ribbon. Under the controls section, click the insert drop-down and select the option button (not the activeX option button) and then draw it out over a shape that you’ve added to your dashboard (or to the sheet if you haven’t added a shape as I have). You are going to need to do this 3 times and then right click on each of your option buttons and edit the text accordingly.
STEP # 2: Once you have added the option buttons with their new titles, you need to add the cell link location to the first option button. Once you do this, it will automatically add the cell link location to each of your other option buttons. The way that this works is that when you select the first option button the cell link location will contain a value of 1, option button 2 will be 2 and option button 3 will be 3. This cell link location will be used in conjunction with some conditional formatting in the next step to change the number formatting of our data within the dashboard.
To add the cell link location, select each of the option buttons independently, right click on them and select format control. After selecting format control, select a cell where you are going to place the cell link value. Often times, this cell link location will be placed on the calculations tab of your workbook. For simplicity, I’ve added the cell link location to same worksheet. Once it is added, you’ll notice that as you select each option in your dashboard, the number will change accordingly and will have an associated value.
Using Conditional Formatting with the Option Button Cell Link to Apply Interactive Custom Number Formatting to your Data
Now that you’ve added the cell link location in the last step, we can now use the value from that cell to apply conditional formatting to our data set. Let’s break down how to apply conditional formatting to our data to allow for interactive number formatting based on the selection made by the dashboard user.
How to use Conditional Formatting with your Cell Link Value to get your Option Buttons Working
The first step of the process will be to select the cells that you would like to apply the formatting to. Once you’ve selected the cells, you are going to click on the conditional formatting button from the home tab of the quick access ribbon. Once you’ve done this, you’ll click new rule.
After you’ve selected new rule, you will be prompted with the New Formatting Rule screen. From here you will select “use a formula to determine which cells to format”. Now that we’ve selected this, we are going to put in a different conditional formatting rule for each cell link value so that we can apply specific number formatting based on the option button selected. I will only walk you through one of the formulas and formatting processes, however, each rule that you create will follow the same procedure.
In the case below, we have the “=” operator then we selected the cell link and again use the = operator and then 1. This simply means if we select our first option button, the cell link will be a value of 1 and we will apply the formatting based on the rule we created (=$H$2=1). Please make note that you will add new rules for each of the other cases which means we will have a rule for (=$H$2=2) and (=$H$2=3) respectively. After adding the formula, we will then select the format button within the same screen.
After selecting the format option, you will choose the number formatting that you would like to apply when selecting your first option button. In our case, it will be regular currency so we will display the entire value in full. You’ll notice that I am using custom number formatting below as I want to display the value 0 as $0. I will be sure to leave a link below to show you how custom number formatting works.
Note: To learn how to apply the other custom number formatting options when the value in the cell is 2 and 3, please checkout this post.
Now proceed to adding each of the other rules for each option button and value that you’ve added to your dashboard. Once you’ve completed this, you’ll notice that you can select any of the option buttons and they will update based on the custom number formatting that you’ve applied within your conditional formatting rules.
Free Excel Tip
Because we have the cell value displayed for our cell link, one thing we can do is hide the value in cell H2 by using custom number formatting. To hide the value in the cell, go to cell H2, then type Ctrl + 1. This will bring up the formatting screen. From here go to custom and then input in the text box area ;;; (semicolon 3 times in a row) then click OK as shown below. This will hide the value in the cell.
Summary
Creating a dashboard that allows the user to interact and change how the values are being displayed is a great way to ensure that all parties are happy when you are done. As we have learned, using conditional number formatting with the option button cell link value allows us to do this. As the value changes, we can create a new rule in conditional formatting so that for each value that is selected, we have a custom number format applied. Using a quick formula solution in conditional formatting will allow you to do that.
Keep in mind you can also use other control options that are available from the developer tab such as a list box.
My homework for you is to figure out what other formulas and ways you can apply conditional formatting in combination with the form control cell link value to make your worksheets interactive. Please be sure to leave your comments below.
Do you Want to Become an Excel Dashboard Expert?
Suggested Excel Dashboard Affiliate Courses and Products
Trackbacks/Pingbacks