Conditional Formatting Basics

Occasionally it is a good idea to Go Back to the Basics. By Highlighting important data in your tables or databases, you can quickly draw attention to the information you want to study.

Let’s say that you have a table showing the results of your sales force, and you wish to exemplify the Above-Average performers in your group. Here is an easy way to highlight these values in a range:

1.     At the bottom of your range, enter the formula for Average: =Average(range)

2.     Select the values in the range (not including the Average formula)

3.     On the Home tab, choose Conditional Formatting from the Styles menu

4.     Select Highlight Cells Rules / Greater Than

5.     Select the dialogue box and click on the cell in which you have the Average Formula

6.     Choose the Formatting (e.g. Bold font with a Yellow fill…) you wish to apply

7.     Click Enter and Bamm! Values that are Above-Average are highlighted!

Use this simple method to highlight Above-Average values (You are, after all,  Above-Average Yourself!)

Not Your Uncle’s Filter

Most any Excel user knows how to use the Filter tool to quickly sort a table or database. Using a Filter can be very effective and can save a great deal of time in many instances.

But what if you need more Flexibility and Power, but still want to do your analysis with a fast, easy-to-use filter? The answer is, of course, the Advanced Filter (definitely Not your Uncle Jim’s filter…).

The beauty of this tool is that it is Advanced, but still very quick and easy to use. We will take a look at a couple of simple examples of how to use the Advanced Filter to help get an immediate understanding of how it works.

With the realization that this tool can work equally well with databases containing thousands of records, we will assume you have the following small database:

As is the case with any well-designed database, each field (column) has a header/name.  Not surprisingly, this is a necessity when using this tool.

Next, we will access the Advanced Filter by going to the Sort & Filter group on the Data tab, and clicking on Advanced. 

In the List range field put the location of your database (in this case it is B3: C11).

For the Criteria range, let’s assume you want to see all of the sales for the North, West, and East regions, and you want to place this filtered result below your database (use B13 for a starting point).  Simply set up a range such as in the following cells E3:E6 (or wherever you wish):

Your result will appear as follows in your chosen location:

You can also use multiple field names and criteria to extract a wealth of information in a mere three clicks.

Another one of the myriad ways you can use the Advanced Filter it to Create a Separate List of Unique Values.  If you wish to retain the original list, and create a separate list of Unique Values, you can click on our new favorite tool, and select your range. Then do the following when the menu appears:

1. Select the Copy to another location option
2. Insert the starting cell in the Copy to dialogue box
3. Put a check mark in the Unique records only box
4. Your unique items will appear in the newly-created range

Pretty Cool, you say. Absolutely! Give the Advanced Filter a 5 minutes tryout, and you’ll be Hooked!

Deleting Old Records

There are often times in the real world when you want to update your databases or tables by deleting data that is no longer pertinent to your operation. Let’s say that you work in the HR department of your company and you want to tidy up your records to show only your Present Employees.  How can you do this without a great deal of effort?

Here is Fabulous way to select rows (or columns, cells, etc) in a database that contain a Key Word, and Delete Them All at Once. In our example the records you wish to delete all have the word Grohowski (who found greener pastures in another company) included in one of the cells.

Here is What You Do:

 

1)  Choose any cell in the database, and press Ctrl+F on your keyboard

2)  Type the Key Word (“Grohowski” 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)  Voila!  Your unwanted records are history!

This remarkably simple trick can save you a great deal of work and help you maintain up-to-date records in your database. Try it out some time!

A Dashboard 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 (Oh, Alas, Alas…) 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 even relatively 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 7 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) 

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!

Subtotals Rock!

As I often say, there are so many great tools in Excel that get surprisingly little notice. The built-in Subtotal feature can save you an enormous amount of time and give you a remarkably easy way to Organize Your Data.  The really great thing is that it takes practically no effort. Here are 3 Reasons Why you should put Subtotals in your Excel tool belt:

1. Flexibility: As you would expect, Subtotals are able to Summarize your data by SUM. But that’s not all! Your data can also be reviewed by Count, Average, Max, Standard Deviation, as well as other functions.

2. Energetic: On the fly accessibility whenever you need to control your data and fields at your fingertips, you have the ability to slice and dice your information in real time!

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” as I always say is clearly an analyst’s goal, rather than presenting mere “Data”) with others. Subtotals are a sharp, concise way of doing exactly that!

Subtotals are often misunderstood, ignored, or simply overlooked. 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 first trying this out. It is always good to have a backup.

Subtotals. Another example of a Fine Excel Tool that you should test out soon!

Leading Zeros

By default, when keying data with the first digit being a zero, Excel will always drop the leading zero (or series of zeros).  But suppose that you have a range of zip codes, account numbers, phone numbers, or other type of data – What do you do then?

Here are Three Different Ways to Handle this Type of Situation:

1.   Place an apostrophe (AKA a tick mark) initially, and then add the leading zero. 

2.   Format Cells by selecting Custom/Type:  Choose 10 zeros (if you have, for example,10-digit account numbers) and hit Okay.

3.   The third method (I recommend this for most cases…) is to format the cell range as Text.  This automatically corrects this potentially annoying problem.

That’s all there is to it!  The next time you want to keep your Leading Zeros, remember these simple tricks.