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!

PowerPivot

This week’s topic is for you Power Users out there. It is also intended to perhaps Inspire the rest of us mere mortals, and to bring your attention to a Free (“Free” is always cool) Powerful Tool.

There are times when being able to combine and analyze data from a number of sources would be, (as I like to quote Martha Stewart), "A Good Thing." Let’s say that you have several SQL databases housed on SharePoint and other sources, and you want to load the data and create interactive queries from within an Excel workbook. Scary Business? Well, a bit, but nothing beyond the capabilities of an Excel Enthusiast!

PowerPivot, available on Excel 2010, truly Empowers you to capture the data you need, gain greater insight into the meaning of the data, and do so without overtaxing your system’s resources. With PowerPivot, you can:

• Load very large databases from Nearly Any Source

• Efficiently process huge amounts of data in mere seconds

• Work in a Disconnected Mode once your data is imported

• Leverage your Familiarity with Excel to work with the data

• Use the new PowerPivot Analytic Capabilities

• Utilize the Power of contemporary multi-core processors

PowerPivot: Not necessarily for everyone, but if you work for a large company and need a new way to Slice and Dice your data, download it for Free from www.PowerPivot.com and give it a try!

Next week: Back to the mainstream with a cool, easy technique that will give you options you never had.

Is it Time to Upgrade?

So you have Excel 2007, (it seems fewer companies and individuals are upgrading as frequently these days…), and you are wondering if it is worth it to finally get Excel 2010.

Although I have had 2010 since it came out, the need to upgrade is not, in the opinion of some, to be overwhelmingly compelling. That being said, there are some Worthy New Features. The following are those I consider to be the most significant:

Function Enhancements:
The Accuracy of a number of the financial and statistical functions have been improved

Sparkline Charts:
Nifty little tool enabling you to create Small In-Cell Charts

Slicers:
New way to Filter and Display data in Pivot Tables

Image Editing Enhancements:
Since I have an appreciation of making my graphics look good in any Microsoft product, this is one of my favorites. You have much More Control over Graphic Images, including the ability to remove the background of an image.

New Version of the Solver Add-In:
Enables solving some Complex Problems (Cool!)

There are a few others that you may find helpful, but for my book, those listed are the Most Compelling. In any regard, I think it is wise to keep in pace with current upgrades. Otherwise the day will come when you may find yourself Sadly Out-Of-Touch (Never a good thing…).

Happy Thanksgiving All! ~Bob

The Invaluable DATEVALUE Function

A Date is a Date is a Date (well, that certainly wasn’t true back in my college days). Nor is it true in Excel. What may look like a Date, may not “play nice” with other dates that you have in your worksheet.

Let’s say that you have inherited an Excel workbook made by some Genius, (please note the thinly veiled sarcasm), and you want to Perform Some Analysis (in your case, truly genius work) that save the company countless hours and expense. The trouble is that unless you can Rely on the consistency of the way Excel will be handling the “dates” in the worksheets, you Cannot Rely on the efficacy of your results (the old “Garbage In, Garbage Out” cliché).

So what is an Excel Guru to do? DATEVALUE to the Rescue! Yes, indeed, my friends, DATEVALUE will calm your nerves, relieve your upset stomach, cure that nagging doubt that you are being watched, and make any Dates in your worksheet work in consistency with all of the dates therein. (Well, I may have exaggerated some of the attributes, but it will make your dates play nice with each other…). DATEVALUE will instantly convert any looks-like-a-date Date into the standard Excel serial number, and you can then format it as you wish.

How Cool is that! It may sound minor, but it can save you a world of grief in many circumstances.

Note of Caution: For you Apple Users out there, (I use Excel on a MacBook occasionally myself), Microsoft Excel for the Macintosh uses a different date system as its default (go figure…). The

The DATEVALUE function: Good Stuff!