The Intersect Operator!

This is an under-used Excel feature that (I’m guessing) you probably have never run across. Although there are several ways to look up information in Excel tables, the Intersect Operator is a very Convenient and Versatile way to do this. The whole idea is to use the vertical and horizontal ranges in a cross-tab Table or Database, and find the value at the Intersection.

The syntax is simply: = RangeName1 RangeName2 (Note: No brackets or commas…) Now, you can do this by using the generic names of the ranges (i.e. =B2:B34 B10:H10), but that is a bit clumsy (and who needs Clumsy…).

A much better way is create Named Ranges. This can be easily done by Highlighting your entire database including the headings with Ctrl + A and then click Ctrl + Shift + F3. This will bring up a dialogue box as shown below (Click OK). Very Cool!

Once you have your ranges Named, you can then (as with the example below) find a value with the elementary (my dear Watson) function: =East Quarter3

But That’s Not All! You can combine the useful Intersect Operator with statistical functions to find even more information! For instance, if you wanted to find the overall average quarterly sales for the “East” region, you could use: =AVERAGE((East Quarter1):(East Quarter4))

Yes, there are other ways of doing this in Excel, but any Excel Geeks worth their pocket protectors should have the Intersect Operator in their bag of tricks.

All the best!

Removing Duplicate Records

Based on email that I receive, Removing Duplicates in databases is one of the most popular and useful topics for Real-Life Excel professionals. Being able to do this, enables a user to Combine Databases with repetitive data into a properly structured format. It is also a great Troubleshooting Tool for eliminating unwanted duplicate entries.

The really good news is that doing this with the last three versions of Excel (2007, 2010, and the beta 2013) is Remarkably Easy. Here is all you have to do to complete this simple task:

1. Select the entire database (be careful not to select any self-generating key fields)
2. Go to the Data tab in the Data Tools group and click Remove Duplicates
3. Select the column(s) on which you want to base your removal and click OK
4. A message will pop up telling you how many (if any) values were found and removed

There are always little twists that can pop up in Excel World, of course. One such instance was raised by a reader earlier today, where he had a database in which he wanted to remove Duplicate Records based on the Absolute Value of a field. In other words, he wished to remove any record where field values had the same absolute value but different sign, e.g. 7 & -7, 11 & -11, etc.

Although there are several ways to approach this, one Straightforward Method is to:

1. Add an Additional Field and use the ABS( ) Absolute Value function.
2. Then it is a Stress-Free procedure to use steps #2-4 above to weed out the unwanted data

At first glance Removing Duplicates to maintain the Integrity of your data may seem like a challenging endeavor. As is the case with nearly everything in Excel, however, once you know how to do it, it seems so Simple, you laugh…

Interactive Dashboards

A Parable:
There once was an Excel professional, (you may have known him or her), who would create wonderful, detailed reports that went on and on and on for many pages in the professional’s workbook. The professional’s work was impeccable. All the information was there, every bit of it! So why (why, why, why) did it Not get used and appreciated like it should?

The problem with this fine professional’s work was it was not Concise, Convenient, or Controllable.

The solution is to create what has become popularly known as a Dashboard. A key part of the definition of a Dashboard is that they can fit on a single computer screen so it can be monitored at a glance.

Dashboards can be linked to complex (or comparatively simple) formulas that work in the background gathering information from a centralized database. The formulas may be comprised of Database functions, Boolean arrays, IF functions, or several others.

Here are some Key Features you should strive for in your Dashboard:

1. The data is the Star, but keep in mind the other elements
2. Make it Interactive with dropdowns, spinners, scrollbars, etc.
3. Keep your audience in mind (it’s Their Dashboard…)
4. Don’t clutter the screen with unnecessary components
5. Make it attractive enough to hold your audience’s interest, but…
6. Don’t make it so stylized it muddles the message
7. Avoid 3-D charts (fancy, but 2-D tend to be more functional)
8. Experiment with the new Conditional Formatting features

A truly fine Excel professional will always make a Dashboard that is Concise, Convenient, and Controllable. It's all about Information, not just the data. A dashboard is only as valuable as the Information that can be obtained from it!

IS Functions!

As has been discussed in philosophy (and used by certain politicians), “It all depends on what your definition of “Is” is”. In this blog, however, we are going to look at some useful IS Functions, and how they are used.

Simply put, IS Functions check the type of value in each cell in a range, and returns TRUE or FALSE depending on the outcome. For example, the ISNUMBER function returns the logical value TRUE if value in a cell is number; otherwise it returns FALSE (if it is text or blank for instance).

IS functions have many uses in Excel, including Handling Errors in formulas and working in conjunction with Conditional Formatting to highlight various data. The following are 5 of My Favorites:

1. ISERR  -  Any error value except #N/A
2. ISERROR  -  Any error value, including #N/A, #VALUE!, #REF!, NAME?, etc
3. ISNUMBER  -  Refers to a number
4. ISTEXT  -  Refers to text
5. ISBLANK  -  Refers to an empty cell

Although they can all be used in several ways, I find ISERR and ISERROR to be particularly useful in Handling Errors in formulas. For instance, ISERROR can be used in an IF function allowing you to return the value “0”, rather than a awkward “#N/A”.

The other IS Functions mentioned are highly utile when using Conditional Formatting to identify/highlight certain types of data.

As with so many things in Excel, the Key is to keep IS Functions in mind when working with your data, as you never know when they will just the right fit! (ISn’t that always the case?...).