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?...).

Three Terrific Tricks!

In the 4+ years this blog has been published, three of the most popular Excel Tricks have been the following. They are Easily Mastered, and they can definitely make your life a bit easier in Excel World…

#1 Selecting Data or the Entire Worksheet
Select any cell in your database and click Ctrl+A on your keyboard; Bamm! You have selected the database. Want to select the entire worksheet? Just click the “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 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 the selection 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 while holding down the Ctrl key, press “~” on your keyboard. Alacazam! All of your formulas will be visible!

Are these Simple Tricks off the Hook, or What! Take five minutes and give them a try!

Top Ten Keyboard Shortcuts for 2012

As any of you long-time readers of this blog know, every year or so, I like to take a look at what my Current Favorite Excel Keyboard Shortcuts are. This changes over time as I learn new tricks and incorporate them into my daily work with Excel.

Have no doubt about it, learning and using Keyboard Shortcuts in Excel will greatly enhance your speed and efficiency. Using shortcuts also will help relieve stress from overuse of the mouse, and will Set You Apart from Mere-Mortal Excel Users.

So, in Reverse Order, here are my current favorite (and highly recommended) Top Ten Excel Keyboard Shortcuts for 2012:

10.  CTRL+SHIFT+% (Applies the Percentage Format with no decimal places – Handy!)
 9.   CTRL+; (Enters the Current Date – I’m a time freak…)
 8.   ALT+F1 (Creates an Embedded Chart of the selected data – Far Out!)
 7.   Ctrl + H (Find and Replace – I wouldn’t want to live without it!)
 6.   CTRL+9 (Hides the selected rows – Tidies up your worksheet…)
 5.   F5 (Brings up the Go TO dialogue box – Great for navigating to a named range!)
 4.   SHIFT+TAB (Moves to the Previous Cell – I use it all of the time…)
 3.   Ctrl + Home (Brings you to the Start of the Worksheet – Oldie, but a Goodie!)
 2.   ALT+F4 (Closes Excel – Hey, you’ve got to knock off work sometime…)

And My Number One Favorite Shortcut for 2012 is (you may think this is a curious…):

 1.   CTRL+K (Displays the Insert Hyperlink dialog box for new hyperlinks – Very useful when making sophisticated, interactive workbooks!)

There you have it! Try a few, and see how they can make your Excel life Even Better!

The Efficacy of Goal Seek

After our stroll through one of the cool new features of Excel 2013 last week, I thought it would be good to take a look at a Classic Management Tool that has been available in Excel for several years.

"What If” scenarios are essential instruments of analysis for nearly any business. Excel provides a great tool called Goal Seek which allows you to “Set” the value of the Output at a particular value and find out what value of the Input variable achieves that output. As with so many little-used tools in Excel, it can save you a ton of work, and make you look good in the process.

Now we will utilize a very Simple Example to explore how this tool works. With reference to the example below, let’s say that you are a Call Center Sales Manager, and you know the approximate number of calls you are going to receive for the next month, as well as the sales goal that has been set by the executive branch. You wish to know the Call-To-Sale-Conversion-Rate so you can advise your staff. An efficient Goal Seek calculator can be created in the cells of your Excel worksheet as follows:

Assumption: The Number of Calls x Conversion Rate = Number of Sales (e.g. B2*B3 = B4)

1) Put the number of Expected Calls into Cell B2
2) Insert the formula, =B2*B3 into Cell B4
3) Using the Goal Seek tool find the Conversion Rate by Setting the Cell B4 to the Sales Goal of 485, and have it reach that goal by “changing cell” B3.
4) The result for the Conversion Rate is 12.1% in this instance.

Obviously, this tool can be used to greater advantage with more complex scenarios, but you can see by our the example that Goal Seek can be a handy tool for the boardroom or any planning venue. Give it a try sometime!