Errors, Errors, Everywhere!

We’ve all Been There, Done That. You are creating an Excel masterpiece formula when you are troubled, nagged, and thoroughly bugged by some Error Messages.

The following is a handy Alphabetical Listing of Error Messages and what they mean:


• #DIV/0! - You divided by 0 or by an empty cell (Everyone knows you can’t do that…)
• #N/A - A return value of the function is not available (You probably messed up the formula)
• #NAME? - Okay, you might have used an undefined range or cell name (easily corrected…).
• #NULL! - You specified an intersection of two areas that do not intersect (Curious error…).
• #NUM! - There is a problem with a number in a formula (Is it really a number?).
• #REF! - An invalid cell reference is mentioned in a function (A cell reference is not valid).
• #VALUE! - The wrong type of argument or operand is used in the formula (Yeah, yeah, yeah)

Now let’s look at a few Handlers you can use to Attack Errors:

1.   ISERR - Any error value except #N/A
2.   ISERROR - Any error value, including #N/A, #VALUE!, #REF!, NAME?, etc
3.   IFERROR – Introduced in Excel 2007, this combines the IF and ISERROR functions

Let’s use the IFERROR in an example. Suppose you have two columns of data which you are simply dividing in a third column. If you Divide by 0 or Divide by a Blank Cell, you will obviously get an Error Message.

 If, however, you want the message to be Perfectly Clear, (as our former President Nixon was fond of saying…), you use IFERROR to define a Custom Response such as “Error in Calculation” or “Problem with 2nd Value”, or something more clever that you invent for your circumstances. In the example shown, the formula would be:

 =IFERROR (Column_1 / Column_2, “Error in Calculation”)

By not simply accepting Errors and Error Messages, you can better Take Control of your Excel workbooks. Always a good thing…

Macro Robots

Although the topic of Excel Macros is a bit overly ambitious for a blog of this type, it is a subject about which many individuals are interested. Macros are not part of most Excel users skillset, but they can help make your day-to-day tasks much easier and the basics should not be intimidating.

Many of us have workbooks which we need to continually update with new data and repeat certain tasks over and over. A Recorded Macro can be your ticket to lessening your repetitious work and simplifying your routine chores.

A Macro can record your mouse clicks and keystrokes while you work, and then allows you to play them back in future renditions of your workbook. When you run the macro, it will play the commands in the same order that you recorded them. It’s like having a Macro Robot at your command!

For example, let’s say you track the performance of the representatives in a call center. Every week a report must be created for management, and it involves routine updates. Here's how to record a macro to apply these updates:

1.   In the report workbook, click the start of the cells you are going to update.
2.   Point to the Developer tab, and then click Record Macro.
3.  In the Record Macro dialog box, enter a Name that applies to your operation. For ease of operation later on, assign a custom Shortcut Key.
4.   Now perform the calculations, formatting, moving, etc that applies to the repetitive and monotonous update.
5.   Finish recording the macro by clicking the Stop Recording button.

To Run your Macro, simply go to the update start cell and enter your Custom Shortcut Key.

Bamm! Instant update! Your newly created Macro Robot has just done all of the work that may have taken you several minutes or an hour to complete. Give it a try – Totally worth the effort!

Give Me the Green Light!

Let’s face it, Excel worksheets can at times be a Bit Drab. A worksheet can serve as a Decision Tool and showing a Yes, No, or Maybe next to your agenda items can get the job done, but it’s really Ho-Hum, isn’t it? Since the introduction of Excel 2007 you have some very interesting options, however. You can use sets of Graphical Icons with your decision lists, and give your reports some Much Needed Life!

For an example, let’s suppose we have a proposed agenda for an upcoming annual corporate meeting. Possible topics have been submitted from numerous sectors, and you want to use a Semaphore for an icon when make a preliminary worksheet showing a:

• Green Light for a definite inclusion in the program
• Yellow Light for a possible inclusion
• Red Light to exclude the topic from the agenda

Here is How to Accomplish This:

1.  Select the range of cells in which you want a Semaphore to appear
2.  Click Conditional Formatting on the Home tab, choose Icon Sets and pick the set that includes the Semaphore
3.  Go back to Conditional Formatting and choose Manage Rules
4.  Click Edit Rule and put a check in the Show Icon Only box and Apply
5.  By default, a “1” will be a Red Light; “2” will be a Yellow Light, and “3” will be a Green Light

Your Decision Lists will instantly take on a New, Engaging Look and will be sure to garner the closer interest of any of the users. And, of course, the cool thing is that it is Easy (Isn’t anything when you know how…).

The Missing Data Entry Form

The highly useful Data Entry Form was a favorite of many Excel users in the past, and for many savvy gurus, it still is.

Besides being an efficient way to enter data into an Excel database, the Form also allows you to:

   • Edit or delete individual records
   • Scroll through each of your data records
   • Locate records containing specific information

The primary use for most Excel aficionados is, of course, Data Entry (ergo the name of the form…).

Back in the day of Excel 2003 (and prior versions), the Form was neatly located in the Data dropdown list on the toolbar. Starting with Excel 2007, however, it was not shown on any of the default ribbons, and many users simply forgot about this great little tool (or thought it was no longer available).

So Where Did It Go?

Good News: It is still available, even on Excel 2013. To add it onto the ribbon of your choice, simply do the following:

   1. Go to Options / Customize Ribbon
   2. Choose Commands Not Shown on the Ribbon
   3. Scroll down to Form and add it to a New Group in the location of your choice

This handy little form will now be readily available whenever you may need it. If you have never used it, give it a try!

Why You Should Use Subtotals

Quick and Easy. Always a good thing. Using Excel’s built-in Subtotal feature can save you an enormous amount of time and give you a remarkably easy way to Organize Your Data with practically no effort. Here are 3 Reasons Why you should put Subtotals in your Excel tool belt:

1. Versatility: As you would expect, Subtotals are able to summarize your data by SUM. Additionally, however, your data can also be reviewed by Count, Average, Max, Standard Deviation, and others.

2. Dynamic: With the ready access to controlling what type of data and which field at your fingertips, you have the ability to slice and dice your information on the fly (Good Stuff!).

3. Sharable: Often what an analyst or manager wants is a “Quick and Dirty” way of reviewing their data in a logical manner. Moreover, however, it is typically desirable to share the Information (“Information” is clearly superior to mere “Data”) with others. Subtotals are a sharp, concise way of doing exactly that!

As with so many marvelous tools in Excel, Subtotals are far too often misunderstood, ignored, or simply not thought of. Give it a try, but here’s an Important Tip: Since you need to initially sort your data according to what you wish to summarize, it might be a good idea to save it to a Practice File before trying this out for the first time.

Subtotals. Another example of Excel Gold.