Join Nate Makdad for an in-depth discussion in this video Inserting control buttons, part of Creating Interactive Dashboards in Excel 2013.
- View Offline
- By adding buttons to our dashboard, we will create an intuitive method for users to change their charts and bring out the dynamic capability of our macros. This is also gonna complete our controls layer and we'll really round out the dynamic nature of the dashboard. So before I insert the buttons, because I kind of need to figure out where I'm gonna place them as we start to insert, what I wanna do is add just a little additional help text here by creating the next section. So here I'm gonna copy my text cause I wanna keep my formatting the same.
I also wanna create a little bit of a visual distinction between my two sections. To do that, I'm going to start over in A19 here and I'm gonna highlight all the way over. Then I want to do right-click and do Format Cells and I wanna pick the Border tab and then click on kind of the thin line, and then I wanna apply it up at the top. Then I just need to change my color here to white and go ahead and re-click on the line. It's hard to see in the contrast, but now the white line's there. Once I press OK, you can see I've added this little border line just to kind of, again, create a little bit of a visual distinction between the two sections, but still within the controls layer.
So now let's go ahead and insert our buttons. To do that, we're gonna go over to the Developer tab again and under Insert, we're gonna pick from our Form Controls. The Form Controls are gonna automatically prompt us to assign a macro. Now I'm just gonna go down and create my first button where we can assign our macros. So we're gonna pop in Agency here cause that's the same as the first one that's in our slicer section. I just wanna keep the order consistent. So again, just part of the visual design, I wanna keep this flow in the right order.
Now I'm gonna rename it because I wanna keep track of where I am in the process, and I'm just gonna pick Agency. Now let's add another one. The next one over will be our Division work unit. I'm gonna keep the titles the same so that way people know what it is. Then let's go ahead and do Work_Location and our Borough.
Next we're gonna do our Degree. Again, keep them in order. We're going to add one for our posting type. Again, we said one thing, but I need to go back because again, I wanna keep them the same. Then last but not least, I want to add one for the clearing. You may remember we added that extra button for clearing so that that way we can kinda get back to a starting point. Now I'm just going to rename that and clear.
Okay, great, so all my buttons are in and we can just test them by pressing one or two just to make sure. We can see that our macros are working against the buttons, so that's great. But these don't really look that great. They're different sizes. They're all over the place, so we need to align these a little bit better. So I'm gonna zoom in because we've been kind of zoomed out for a while as we've been working on this template design, but let's zoom in now. I'm actually gonna right-click on each of these.
I'm actually gonna hold down control and then keep right-clicking. That's gonna allow me to select these. I'm gonna leave the Clear button out because I don't wanna change the properties in quite the same way for the Clear button that I wanna do the rest. So now I can right-click again and do Size and Properties. A couple of pieces we're gonna do. One, we wanna standardize the heights, so similar to the way we standardized our height around our slicers, I wanna do the same around the buttons. About .6 looks good to me. Then we wanna standardize the width a bit, too.
So keep that consistent. We also wanna make our buttons big enough that it's very easy for people to click on. This seems about good, it's about a 1:2 ratio, so it seems good. We'll also, while we're in here, wanna change our Properties because we wanna go down to Don't move or size with cells. So if someone were to adjust column C or D or whatever, it could move the size of the buttons, so throw things off. We just wanna keep that kind of visual design consistent. Okay, so now that we're in here, we can close out of our Format Shape.
If we wanted to, we could also adjust the font. Now I'm gonna right-click and do Format Object. You'll see this brings up our Font list. We could up the font size a little bit if we wanted to, but we really don't need to make it much bigger, given the size of the buttons. You could certainly increase the size of your buttons if you wanted to make the fonts bigger, but we're good for there. I'm good there. Now I just wanna align my buttons or make them a little bit easier or a little bit closer together.
I'm okay with the Agency button being where it is. I'm just gonna right-click to highlight that button. I'm doing a lot of right-clicking in here because these buttons, if you left-click on them incorrectly, it'll just run them. It doesn't highlight them. I just wanna right-click to select the buttons and then get 'em in order. My Clear button I still need to deal with because I just wanna change my font a little bit on this. I wanna actually make this a little bit bigger and I also wanna change the color so that it stands out as being a little bit different from the rest.
I'm gonna use the orange because it's a complementary color to blue. Then again, I'm also gonna change my properties here. So my button now looks a little bit bigger or a little bit different, and I also wanna just change the size a little bit. I don't wanna be a lot smaller, but I want it so stand out and be a little bit different from the rest. I think I'm also going to use some of my font approaches, and I'm going to make this all caps so that it stands out a little bit better, as well.
Okay, so that's inserting buttons. Now we have our buttons available in our report to really give us that dynamic capability.
- Defining your requirements
- Setting up data
- Adding reference tables
- Creating hierarchy in the layout
- Choosing color and typography
- Importing data
- Inserting slicers
- Recording a macro
- Editing VBA code
- Improving the user experience