Three Cool Tricks!

Here are three of my favorite little tricks for making life easier in Excel World…

#1 Selecting Data or the Entire Worksheet

Select any cell in your database and click “A” on your keyboard while holding down the Ctrl key; Bamm! You have selected the database. Want to select the entire worksheet? Just click “A” once again!

#2 Copy the Formatting of a Cell or Range and Apply it to Another Cell or Range
Select the cell or range with the formatting you wish to copy, (Note: This works for conditional formatting as well), and click the Paint Brush on your toolbar. Your cursor will then turn into a paint brush that you can use to “paint” any other cell or range with the formatting you have picked up from the previous cell or range (think of them as the paint can).

#3 Display Formulas So You can Troubleshoot Issues
This is so easy, you will laugh. Select any cell on cell on your worksheet and simply press the “~” key on your keyboard while holding down the Ctrl key. Presto! All of your formulas will be visible!

How cool is that! Take five minutes and give the tricks a try!

Life Beyond Microsoft

This blog is entitled Excel Enthusiasts, but let’s face it; there are other spreadsheet applications out there worth looking at.

Perhaps the most interesting is the Numbers application in the iWork suite by Apple. Numbers has over 250 functions, (comparable to Excel), including some unique ways to calculate time that you do not see in other applications. There are many terrific templates and, typical of Apple’s penchant for graphics, creating gorgeous charts is a breeze.

Since most of the computing world speaks Excel, it would be a drag if Numbers was not compatible with our favorite software. Happily, you can save and export your files in an Excel format, as well as in PDF and several other common formats. You can also share your work by uploading it to a new private iWork website that Apple has made available (you can send a special URL to anyone you wish to collaborate with).

Another late-breaking Numbers feature is that it will be available on the new Apple iPad (arriving April 3) in a new format that will allow you to use in a finger-friendly interface, (yes, I have mine on order…).

Now, is this Apple product truly cooler than our favorite tool, Excel? Probably not, but it does have some intriguing features you might be interesting in. Happy exploring!

Unique Names for a DropDown List

Getting a list of Unique Names for a DropDown Box is easy. As we discussed in the February 18 post, DropDown Boxes can be a real boon for a professional-looking report.

In order to obtain a list of Unique Names just do the following easy steps:

1) Select the original range of names (See column 'B' All Names).

2) Go to Advance Filter, and choose “Copy to another location” action.

3) In the "Copy to" box, select a cell for the starting point.

4) Check the checkbox for "Unique records only" and click OK.

Presto! A list of Unique Names to use for your DropDown Box!

Pivot Tables: The Good, the Bad, and the Ugly!

This is an introduction to an often disregarded Excel application. Much has been written on Pivot Tables, and much has also been misunderstood about this highly practical, but not perfect tool.


The Good
Every analyst or manager should have at least moderate skills at using pivot tables. You can use pivot tables to summarize, analyze, and explore what-ifs in your data. What is particularly “Good” about them is they are very powerful, lightning fast, and very easy to use. If you have never experimented with Pivot Tables, give them a try. I can guarantee that you will amaze yourself with how simple it is to manipulate your data.

The Bad
Pivot tables are not all things for all applications. Though powerful, they have some odd quirks, (such as resizing your columns when you change an entry), and often need to be rebuilt if your data significantly changes (the good news is, of course, that it is easy do so…).

The Ugly
Let’s face it, pivot tables are Ugly! Oh, sure, you can apply one of the stock formatting schemes that haven’t changed in ten years, or design your own, (beware, it may be lost when you update or pivot data), but it is still ugly. Now, this may not be terribly important to you if you are just doing some “quick and dirty” analysis, but it may not be something you want to show the board of directors.

Bottom Line
Though not perfect tool, pivot tables will often save you many hours of analysis time, and the other great news is that it truly is easy. Go on, give it a try!

Errors, Errors, Errors!

Okay, so you are humming along creating yet another grand Excel masterpiece when Bamm! You are alerted that there is an Error! But what does it mean? What does this cryptic warning by the Microsoft gods represent? The following is a concise list of Error Messages and what they mean:

 • #N/A A return value of the function is not available. (You messed up on the formula.)

• #NAME? Okay, you probably just misspelled a name in the function (It happens…).

• #NULL! You specified an intersection of two areas that do not intersect (Whoops).

• #DIV/0! Well, you divided by 0 or by an empty cell (You know better than that…).

• #VALUE! The wrong type of argument or operand is used in the formula (Right!).

• #REF! An incorrect range is mentioned in a function (A cell reference is not valid).

• #NUM! There is a problem with a number in a formula or function (Is it really a number?).

When a human works with Excel, errors are inevitable. After all, To Error is Human…

Using Validation to Create a DropDown Box

As an instructor of Excel classes that are as large as 250 professionals, it is common for me to field repetitive inquiries on certain Excel functions. One that I see a lot is “Is there an easy way to make a dropdown box in my Excel report?”

This is a good topic to revisit, as using a DropDown Box in an Excel report can add interactivity, efficiency, and a professional style to your worksheet.

The easiest and most effective way to do this is by using Validation:

1. Make a Source list that you want displayed in your DropDown box.

2. Select the cell in which you want the dropdown, (such as the green-shaded cell in the graphic).

3. Choose Validation / Allow List and then select the range from your Source list.

Presto! Instant DropDown Box!

By combining a dropdown and elements such as a VLookup function, you can create a powerful and interesting report in Excel. Give it a try, it’s Easy!

Excel 2010 Beta is Here!

Well, I have taken the plunge and downloaded the beta version of MS Office 2010! I have been exploring Excel 2010 this week and I have found numerous new or refined features that are commendable.

Among the many cool new features, I have found the “Sparklines” (funny name, but very useful) and the new Screenshot tool to be the most interesting and beneficial.

Sparklines are essentially mini-graphs that you can automatically insert in cells within your table, giving context to the numbers. Highly effective in giving at-a-glance information!

 The new Screenshot tool allows you to intuitively paste a picture of any open program or clipping from an open program. Very easy to use and all included within the application (no need to use a specialized program to accomplish this any longer). Manipulations of graphics have also been greatly enhanced (this can particularly useful in PowerPoint, but is also nice in Excel).

Go to Microsoft Office Beta Download to snag a copy. A word of caution, however: Be sure to backup all of your data and your current version, in case you wish to revert to your tried-and-true Office roots.

Happy Excelling!