Join Dennis Taylor for an in-depth discussion in this video Creating colorful 3D buttons for worksheet annotation and macro buttons, part of Excel Tips Weekly.
- The colorful buttons you're seeing on this worksheet could contain text, one of them actually does. These can also be associated with macros and they have an interesting 3D kind of look to them they're easy to create and they do have a functional purpose for example, the macros that I suggested. Let's show how to create one of these and then use it for macros. In most of these examples, except for the circle we're talking about simply two shapes put on top of one another and shaded in opposite directions. So let's create one.
We can do this from the Insert tab on the ribbon. You'll see a shapes button. This could look a little larger depending upon your screen resolution. I'll just pick the rounded rectangle option. Highlight it like this. Maybe I prefer a different color. Notice, a contextual tab is present, the Format tab. How about shape fill here? Maybe a color I've used recently. How about this one maybe? That one? Looks good. Let's duplicate this, control d. So we've got two of these.
Take one of them, move it aside a little bit make this one smaller. Now, we want to keep the same ratio of height to width most of the time so that means hold down the shift key. Make it a little bit smaller. And let's apply shading to this. Again, back to that Format tab. Shape Fill, choose Gradient and consider one of the corner options for example, the one I'm circling down here the lower right-hand corner. That means the lower right corner of the shape is going to be a little darker than the other part of it.
Let's go to the other, the companion shape here and essentially do the opposite. Go to Shape Fill, Gradient and we'll shade it from the upper left. This option right here. So now I've got opposite shading. Let's pull them together by choosing first of all, both of these together. We can use the control or shift key to select both of these. In other words, make sure both appear to have these handles on each of the sides and corners. Then from the Format tab, let's align their centers and then align their middles.
They both have lines around them, let's remove those. So that's by way of Shape Outline, also on the Format tab. No Outline. Now, you might want to experiment a little bit by clicking outside of the shape. If you want the inner part to be a bit smaller you can drag one of the corner rectangles but in order to keep the same center and the same ratio of height to width hold down control, think of that for the center and then shift to keep the same aspect or height-width ratio.
Drag it a little bit smaller, let go of the mouse. Maybe that's too small. Again, certainly a judgment call here. Click outside of it, okay. I think that looks pretty good. So, let's join these, turn them into one shape. Click one of them, with the shift key click the other one. And then back to the Format tab. Group, Group. And you can imagine doing that with lots of different shapes. You see different shapes being done here. The circle off to the left actually has one, two, three, four, five different circles and they're shaded in opposite directions every other one, to come up with that effect.
Now we can use these in different ways. I've got another worksheet here so I'm going to copy this shape. Right click and copy and go to the other worksheet called Employees and simply paste it out here to the right and make it considerably smaller and move it up here. Now, there are two different macros already written here that sort the data. So I'm going to make a copy of this simply by pressing control d.
I could make a different color change I think I won't for the moment. But let's say we want each of these to be associated with a different sorting macro. The macros are already written. So I'm going to right click on the first one and choose Assign Macro. And it's going to be the macro that sorts by department, status, and name. And I'll click OK. Now, if I control click in here I can add text so I'll just say Sort by Department, Status, Name.
And I might reconsider that text and also possibly reconsider the size of it. So we can make this be larger and smaller to contain our text a little bit differently there or just by clicking in here making the size of that text be a little bit smaller maybe. And we'll rethink this and rework a little bit, possibly. You can imagine doing that, of course. Not quite the way I'd want it, but good enough. And also, by the way, if we control click on here we could make that be bold, that might help a little bit.
There we go. And also change the color of the text if we wanted to. So that particular button is associated with the macro that sorts by department and then status and then name. And that's the current order of the data. Let's take the other button here and by the way, what I could've done and certainly I could do it now, why don't I just get rid of this button and with the control key click the first button and then I'll simply with the control key, drag that downward to duplicate it. But I want this to be associated with the other macro so I'll right click here, Assign Macro and assign it to the macro that sorts by status first, and then department, and then name.
And I'll change the wording in there to be status first and then department and then name. So now, and zooming in a little bit so we can see the buttons better and you can imagine resizing those, making them more compact possibly putting them above the worksheet although I don't think that would be a good idea here. I want to sort by status, department, name, right here. Watch column E. All the contract people are now first. Now, zooming back we can't see everything all at once maybe quite as nice as we want.
Upper button, sort by department, status, name. Lower one, sort by status, department, name. And you can imagine a few other buttons here, too. And we might want to change the colors of these and consider where they're being placed. But an interesting appearance, the 3D feature is popular. When I show this to people, they say "How'd you do that? How'd you do that?" And it's very easy to do as we saw previously. And then associating them with existing macros is easy too. All we did here was simply to, with the button to simply right click and Assign Macro. We associate it with a macro.
So an interesting way to work with data and to have an appealing, attractive feature here used as buttons for running macros.
Author
Updated
2/23/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 24m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Creating colorful 3D buttons for worksheet annotation and macro buttons