Comments

Most 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.

Over 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 years.

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 a comment

Now 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 Comment
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 Style

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.

Happy Thanksgiving All!

Coolest Chart Titles!

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? Absolutely!  This 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
2.     On the Design tab, click a layout that contains a title from the Chart Layouts group
3.     Select the Chart Title
4.     Go to the Formula Bar and type an Equals Sign: “ = ”
5.     Then Select 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!

A Few Tricks…

Abracadabra!  I have always enjoyed quick tricks that accomplish very worthy tasks in Excel. Some of these are Oldie-Goldies, but Hey, they’re all really Good Stuff!

1. Select Noncontiguous Cells and Ranges
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, and Kazam, mission accomplished!

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!

What’s Happening in the Background?...

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 nature.

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!):

Ctrl + ~

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…).