Excel Quick Tricks!

Season Greetings All! I hope everyone is having a safe and enjoyable holiday season.

I have always appreciated brief “Aha!” tricks that can reveal a Quick Easy Way to accomplish a task in Excel. Here are four little known or used tricks, plus the results of a survey:

1. Select Noncontiguous Cells
Selecting noncontiguous cells in a worksheet is simple by holding down the CTRL key and click on the cells you want.

2. Format Individual Characters
Click the F2 key and use your cursor to highlight the character to want to format. Right-click and the Format drop down menu is at your command!

3. Align Text Your Way!
Once again, Right-Click and use access the Alignment tab on Format Cells. It’s a Snap Aligning your text in any orientation you want.

4. Save Your Chart as a Picture
Do you want to Use Your Chart in Another App or location? Copy / Paste as a Picture, and feel confident it will stay true to the original (and take up less space…).

Extra: Survey Results
I set up a survey of More than 3,000 business folks recently. Over 82% of the responders voted that being efficient in Excel can help you in your job “A Great Deal” or “To a Large Extent”.

That survey philosophy can make you glad you are an Excel Enthusiast!

Keeping Things Proper

Having a list of names imported from a source other than Excel can result in all upper-case, all lower-case, or even a mixture of both! Whereas this may not affect recordkeeping or data calculations, it certainly doesn’t look very professional.

So what do you do when you download 5,000 names that are in something other than proper case? (You sure don’t want to change everything manually…). The answer is, of course, use the PROPER function!

It is so simple, you will laugh:

Let’s say that you list of names runs from A2 to A5002. In cell B2, insert the following formula:

     =PROPER(A2)

Then simply copy this formula down to the bottom of your data with a quick double-click. Voila! Proper Names!

One final note of caution, if your database list contains names such as McCarthy or MacNamara in it, you will probably need to change those manually. And if you have any really Odd Names like DeLaMartre, then you will for sure need to make some adjustments by hand…

Cheers for your holidays!

Bob DeLaMartre

A Micro-Graph for All Versions

This is so cool. Here is a way to make a simple Micro-Graph that resides in your table and Works in Any Version of Excel!

This is really easy. Let’s say you have your Products (or sales reps) in Column A as illustrated above, and in Column B you have the Units Sold. Here is the formula you should put in Cell C2:

  = REPT( “l” , B2/10) and then copy it to C7

For Each Approximate Count of Ten, the formula puts a Hash Mark, (using an Arial font works well), in Column C. The result is a simple, easily read, Micro-Chart!

Try it out in the office, and wait for the Kudos to Roll In!

Double-Click Tricks!

This is one of my New Favorite Posts for this blog. If you would like to have some more Mouse Tricks Up Your Sleeve, (not that you would actually like to have mice in your clothing…), then I think you will like some or all of these. Unless noted, these Double-Click Tricks work for Excel 2007 and 2010. We will start off with an old favorite…

1. Perfectly Adjust Column Widths – Just select Multiple Columns and Double-Click on the separators; Works for adjusting row heights too.

2. Insert a Split - Double-Click just above scroll-bar to include a horizontal split; Works for a vertical split too, by clicking on the little bar shape next to the right of horizontal scroll-bar.

3. Close Excel 2007 (only) – Simply Double-Click the Office Button.

4. Collapse Ribbon to Get More Space – I like this one. Just Double-Click on ribbon Menu Names.

5. Lock Format Painter – Save a Ton of Time by Double-Clicking on the Format Painter icon, making it Reusable. (So Cool!...)

6. Jump to Last Row / Column in Table – Another old favorite: Just select a cell, and Double-Click on the cell-border in the direction you want to go. Bamm! You’re there!

Double-Click Tricks Rock! Give Them a Try!