Pimp My Spreadsheet! (with apologies to MTV...)
I was helping my nephew with a spreadsheet the other day. He mentioned that
before it was presented I might have to help him “pimp my spreadsheet.” The
phrase was just too awful not to use… Of all of the things that are being
‘pimped’ these days, a spreadsheet is absolutely the most absurd. But
honestly, we run into an awful lot that could use it!
The process of
pimping one’s ride usually involves adding lots of unnecessary color,
electronics, upholstery and other items that don’t actually increase your
car’s efficiency in getting from one place to another. What we’re going to
add to our spreadsheet is pretty slick, but it won’t lead to any loss of
functionality in favor of flash.
The use of ActiveX controls – dropdown lists, checkboxes, and radio
buttons – in web pages is standard. In fact, many web browsers consider it
a great inconvenience to have to type a date into a textbox rather than
select one from a calendar control. Yet despite the availability of such
controls in MS Office documents for over ten years, they are comparatively
rarely used.
The use of ActiveX controls increase the user’s convenience and
confidence and decrease the likelihood of inappropriate data entry. Take for
instance, a ‘date’ that was actually typed into a spreadsheet cell: ‘Last
Thursday’. You can imagine what happens to the spreadsheet formula expecting
mm/dd/yyyy and getting ‘Last Thursday’. Inserting a calendar control in
place of a standard Excel cell or even a textbox on a form eliminates the
need for complex error checking when more open-ended data input is used.
You can view the ActiveX controls that are available to you in Excel by
going to View > Toolbars > Control Toolbox.
You should be familiar with most of the controls you see like Combo Box
(or dropdown list), checkboxes and spinner buttons. You can add other
controls to the toolbox by clicking on the icon at the bottom right.
We used the Calendar control in our spreadsheet to avoid any issues that
might come up when users enter bad dates like April 31, February 30 or ‘Last
Thursday’. We also used a Combo Box to limit the Length of Project to 3
months, 6 months, 9 months or 1 year. Finally, we used a horizontal scroll
bar to allow the user to increment interest rates one-quarter percent by
clicking on the left or right arrows. This spreadsheet only takes the data
and calculates a table of simple interest rate data, but you can also hide
the data behind a chart to provide the user with graphic feedback. You can
download the pimped-out spreadsheet by clicking on the graphic below.
For further information on using ActiveX controls, view the article I Get
It! Development, Inc. published in the
Journal of Accountancy back in
June 2001.
It details the actual steps in adding and using ActiveX controls.
Please download the article and give it a try or feel free to
contact us for help pimping your
spreadsheets.