Sometimes you just need an interactive checkmark. Unfortunately, Excel does not come with a really cool solution for this.
Oh, there is the little trick about inserting a checkmark symbol - but this is not dynamic and provides zero sizzle.
Then there are the form controls. While they have always had potential, I think they have not been enhanced in over a decade. They are cumbersome and create potential problems when rows are resized or moved, even though they come with options that help a little in this regard.
But perhaps the biggest shortcoming of form control checkboxes is the fact that they do not automatically clone themselves in a table as the table grows. It's astounding to me that after all these years, Excel does not have a Check Column as an option for a table.
I needed this recently and so I created my own. Excel 2007 and 2010 come with a new sub-feature of Conditional Formatting called Icon Sets. With a little VBA coding to create a generic round-robin array-element selector, I came up with a nice addition to the Excel interface. The animated GIF above shows an inkling of what can be done with this technique.
I think it is pretty cool, what do you think?
Here is the file:
The round-robin functionality will work in older versions of Excel (which can be very useful in its own right - examine the Alpha Round Robin Demo in the workbook for an example), but the Icons Sets will display simply as numbers in the old versions.
Learn Advanced Excel!
Advanced Interface Design is just one of literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy
I've received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they've ever made. Wow.
This Spring I will be offering the course again. There's already over 200 people on the interest list for the next class. Why not join us? Just fill the form below and I'll send you more information as we get closer!
If you liked this article, please share it!