skip to main |
skip to sidebar
of us would agree that Lack of Communication is one of the core
obstructions in the conducting of most businesses. This is typified by the sometimes
exasperating experience professionals have with some Excel workbooks.
time, workbooks can become overwhelmingly complex and, what may seem
straightforward and obvious when you create a spreadsheet, may be bewildering
in the future (especially for other users). This is where Cell Comments shine!
These are built-in Excel equivalents of
the still-used Post-It Notes that have been popular in businesses for so many
As with so many things Excel, there are a variety of ways you can insert and
format comments. I have, however, been a long-time evangelist of using the Right-Click
option with your mouse, as it is intuitive and context-sensitive.
To insert a Comment, simply Right-Click
on a cell and choose Insert Comment
from the dropdown menu.
· This will Insert
a comment with the Username of the PC automatically included in the textbox
(which you can delete or change under your general settings)
· You can edit the
comment and insert your directions at this time (or any time) by right-clicking
the cell and choosing Edit Comment
· Subsequently, a Small
Red Triangle will appear in the upper corner of any cell that includes
every time the user of the workbook will see the customized Comment pop up
whenever they hover their mouse over the modified cell. Pretty Cool!
You can do More, however, as a great deal of formatting options are
available for your comments. For instance,
suppose that you have different types of data in your worksheet and you want to
Customize the look of your comments for various
groupings. Here is what you do:
1. Right-click the cell and choose Edit
2. When the comment appears, right-click the Edge of the textbox and
choose Format Comment
3. Choose the Colors and Lines
tab and customize your special look for the comments by changing Fill Color, Transparency, Line Color and
Using Comments is most certainly a Best Practices
recommendation in Excel. It’s all about Communication, and if you haven’t
been doing so, consider the use of comments today. Future users of your workbooks (including
yourself) will Thank You.
I think we can all agree that Charts are tremendous depictions of data that conveys information
in a powerful Visual manner.
Dynamically controlling your chart’s Titles, however, make the even better!
Let’s say that you have a table of data and a chart that changes dynamically
every time you change the value in a dropdown box. If you create and use interactive reports,
(highly recommended), this is a common occurrence. So, would it be possible to have the Title of the chart
change to reflect the value (perhaps a name) chosen in the dropdown?
is an advanced, (but very easy) way to make the charts in your report Stand
Out from the mundane masses.
Here is How It is Done:
Let’s say you have created a table of data that changes
in accordance with the Employee Name shown in a Drop-Down
Box (easily done using Validation / List), with and Equals
Sign (=), you can Link the Chart Title
to reflect the name chosen in the drop-down.
If your chart Does Not have a Title, do the following:
1. Click anywhere in the chart
the Design tab, click a layout that contains a title from the Chart
the Chart Title
Go to the Formula Bar and type an Equals Sign: “ = ”
the Drop-Down Box Cell to which you want to link and click Enter
Bamm! Your chart's title changes is now Synced with the dropdown
value! Try using this technique and enjoy
seeing your reputation as an Excel Guru grow even more!
Abracadabra! I have always enjoyed
quick tricks that accomplish very worthy tasks in Excel. Some of these are Oldie-Goldies,
they’re all really Good Stuff!
1. Select Noncontiguous Cells and
We all know how to select contiguous cells in a range or database, but how
about noncontiguous cells?
Selecting these cells in a worksheet is as simple as holding down the CTRL
key and click on the cells you want. Presto!
2. Align Text Your Way!
Right-Click and access the Alignment
tab on Format Cells. It’s a Snap aligning your cell in any
orientation you want. For instance, do
you want the text in your cell to be vertically oriented? Just click the Vertical Orientation Graphic,
3. Jump Between Worksheets
To move from worksheet to another does
not mean you have to reach for your mouse.
To switch to the next worksheet to the left, keep your hands on the
keyboard and simply enter Ctrl + Page Up.
Or change to the worksheet to the right by entering Ctrl + Page Down. Poof!
4. Use Your Chart in Another App
Do you want to use
your chart in PowerPoint, Word, or some other application? Select your
chart and Copy / Paste as a Picture. You can then feel assured that it will stay
true to the original. Voila!
Tricks are Good Stuff! Give
them a try and Amaze your colleagues!
Have you ever inherited an Excel
workbook, and had trouble figuring out how it works? There are times in any Excel user's life when
you're just not sure what is going on behind the scenes in your
workbook. This is an important concern,
so let’s look a couple of Key Techniques for investigation of this
1) Precedents and Dependents?
Do you need a tool to see what your data is doing and where it comes from? The Formula Auditing group on the Formulas
tab is where you should go! There you
will find a great deal of help enabling you to Trace Precedents,
Trace Dependents, and even Show Formulas. The information is returned
in easily-understood graphics that help you make sense of the workbook
you inherited from old what’s-his-name (why didn’t he leave some notes?...).
2) Formulas in My Workbook?
Okay, let’s click on each cell and see
if there are Formulas at work. Ha,
what a drag that would be! Rather than
clicking on individual cells, (as you needed to do in some of the older
versions of Excel), you can reveal all of this information in your entire
worksheet with the following cool little Keyboard Shortcut (you may know that
I am big fan of keyboard shortcuts!):
This simple keyboard combination immediately
unveils the formulas in All of your cells, from which you can
then determine if there is a flaw in their construction (old what’s-his-name
did have his off days…).
If you are ever faced with a mysterious workbook, these two Investigative
Tools can make your Excel life a log easier. (And “Easier” is a good thing…).