Find and Delete Rows

Who says you can’t teach an old dog new tricks? I just ran across this Fabulous way to select rows (or columns, cells, etc) in a database that contain a Key Word, and Delete Them All at Once.

Let’s say that you have a database or table that contains records you wish to delete. Furthermore, let’s say the records you wish to get rid of all have the word Excelite included in one of the cells. It may be possible to sort the database and delete the records, but this may not be convenient (or even impossible if the Key Word does not lead the string in a cell).

Here is What You Do:

1.   Choose any cell on the worksheet, and press Ctrl+F on your keyboard
2.   Type the Key Word (“Excelite” in our example) in textbox and click the Find All button
3.  Now Select an item in the list, and press Ctrl+A to select the entire list
4.   Go to the Home tab, click the down-arrow on Delete (in the Cells group on the ribbon), and then click Delete Sheet Rows
5.   Voile! Your unwanted records are history!

You will note when you click the down-arrow on Delete that you are also presented with options to:

• Delete Cells
• Delete Sheet Columns and
• Delete Sheet

These can also be useful, but you will probably find the Delete Sheet Rows to be the most handy. With the high cost of Energy, it’s always good to have an additional way to Save Some in Excel!

Happy Excelling!

Take Control with Your Ctrl Key

Some of us don’t take advantage of the humble Ctrl Key. It sits there on our keyboards in two different positions making it convenient for any of us to use it to Best Advantage.

So just what can you do with the Ctrl Key?   Many Cool Things; Here a Few…

1) Speedy Navigation: When you press Ctrl and any Arrow Key (up, down, left, or right), you Jump to the last populated cell in that direction. Zip! Just like that!

2) Fill the Contents and Format: Using the topmost cell of a selected range as reference, click Ctrl+D to Fill the Selected cells below. (An easy Crowd Pleaser…).

3) Simultaneous Data Entry: When you want to put the Same Information into multiple cells, all you need to do is Copy it, Select the cells you wish to paste it into, and use Ctrl+Enter to fill them simultaneously. This really Rocks!

4) Make Noncontiguous Selections: Select any cells you want, hold down the Ctrl key while you click on a cell or drag through a range of cells. As long as you hold down the Ctrl key, you can click and select Anything You Want on the spreadsheet.

5) Print Without the Mouse: Sometimes we still need to Print a worksheet. Rather than using the mouse, another simple Crowd Pleaser is to simply press Ctrl+P. Bamm! Ready to print!

There are many other Cool Ctrl Tricks that you can use to help you Control your worksheets, but I think you will find some of these discussed to be your Favorites.

Cheers!

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!