Spreadsheet Commenting Outside the Box
So you’ve been working on this spreadsheet, streamlining a complicated calculation,
investing company time for the sake of efficiency. After months of improvements,
additions and fixes, now you can say that it makes perfect sense and … it works!
It may even be ready to share it with advisors or coworkers that might appreciate
its utility. But will they understand what it does and how it does it as well as
you do? Typically no, they will not. So what can be done to jazz-up your functional
spreadsheet, making it blatantly obvious what it’s doing and why? In this article
we’ll take a look at some unconventional uses for Excel’s Comment and Data Validation
functionality to create a sort of
qualitative data layer to overlay upon
your essentially
quantitative spreadsheet.
Formatting
A good preliminary step might be to re-evaluate your choices for formatting. Font
sizes and weights, cell colors and borders, column and row heights and widths, may
all be varied to accentuate aspects of the spreadsheet and perhaps downplay others.
Experiment with these dimensions first but take care not to produce a bewildering,
Technicolor Dreamcoat style, spreadsheet.
Text Comments
Comment boxes may be used
to provide text instructions and explanations. Use them to explain the functionality
of a complicated formula or the source of a mysterious piece of data. They can be
set to
(a) always show or
(b) show when mouse points to the cell.
But if you have many cells on a sheet that need or could benefit from some further
explanation you may not be happy with the two Comment options of always show or
show with mouse point. There may not be room enough to have them all showing, and
it could become annoying if boxes are inadvertently opened as one navigates the
spreadsheet. We have another option through the use of the Data Validation tool
box (Data-Validation…). With this approach the message box comes up
when the cell
is selected.
Picture Files
Comment boxes may also be used in a quite different way. Buried deep inside the
Comment box formatting menus is a way to set the background of the comment box to
a picture file. This one seemingly minor feature can be utilized for a broad range
of applications. Sure, digital photos may be the first idea that come to mind, but
in effect anything that can be scanned can be attached to any cell. Here are a few
examples to inspire you:
-W2 Form-
-Photo-
-Business Card-
To access this feature, follow these steps. Insert a new comment box (Insert-Comment).
Right click on the frame of the box and select “Format Comment
…”. Note that choosing “Comment…” under the Format top menu does not provide
the same options.
Select the "Colors and Lines" tab and choose "Fill Effects…" under the Fill Color
drop down menu.
Select the “Picture” tab and click “Select Picture…” button to access the file folders
and navigate to the desired picture file. Many picture formats are accepted. Though
.pdf files are not directly accepted still consider the possibilities of using existing
.pdf files; these documents may be converted to picture files and then inserted.
One additional item to consider when deciding whether to use the comment box or
the data validation info box relates to file and worksheet protection. In order
to activate the data validation enabled cell, the cell must be either unlocked or
the worksheet must allow locked cells to be selected. Both of these options may
be undesirable, especially if the worksheet is designed to allow the user to tab
through entry fields. One way around some of these issues is to use the data validation
on an adjacent cell to the one referenced. That way the reference cell can be locked
and the data validation cell, holding only the non-functional comment, may be left
unlocked and selectable.
Happy Spreadsheeting!