The Count on Halloween

Happy Halloween All!

The COUNTIF function is an Extremely Useful, and sometimes misunderstood, way to derive valuable information from your data.

COUNTIF is a highly flexible counting function and, although it can be a little Tricky, getting familiar with its use can be a real Treat!

The reason this function can be challenging to some users is because of the prevalent use of Quotation Marks within the formulas. The following examples will demonstrate how it works (in each case, the formula uses a range named Data):

Count the number of cells that contain the first name of Dracula - "Vlad" (BTW, not case sensitive):
=COUNTIF(Data,"Vlad")

Count the number of cells that contain the words "Vlad" and “Igor”:
=COUNTIF(Data,"Vlad") + COUNTIF(Data,"Igor")

Count the number of cells containing Any Text (ignoring the numbers):
=COUNTIF(Data,"*")

Count the number of 3-letter words:
=COUNTIF(Data,"???")

Count the number of cells containing text that begins with the letter "V":
=COUNTIF(Data, “V*”)

Count the number of cells that contain a value Greater Than Or Equal to 13:
=COUNTIF(Data,">=13")

Count the number of cells that contain a value from 13 to 33:
=COUNTIF(Data,">=13")-COUNTIF(data,">33")

With a little imagination, COUNTIF can indeed be a Treat to use. So, this Halloween, Don’t forget the Count!

Better Charts

If you read this blog with any regularity, you know that the construction of Better Charts is of keen interest to me. It should be to you, as well. Charts, after all, are the Best Communicators of information that any Excel user has in his or her arsenal. Charts Visually Convey your data in the most quickly comprehended (if done well) manner.

It is, therefore, worth any Excel Guru’s time to assure that they are created with skill and insight. Of course, no one has any time to waste on frivolous enhancements, so how can you get the most Value for your Chart-Improvement Efforts?

Here is a Quick, 2-Minute Drill for Polishing Your Charts: 

1.  Chart Type: Quickly scan the types of charts available and consider which (it may not be the one “that we always use”…)

2.  Legends seldom add any additional information to a well-constructed chart. Assure that the chart is communicating well and then Right-click and Delete the Legend! 

3.  Gridlines tend to Clutter your visual information. Consider if that is the case, then Right-Click and Delete the Gridlines!

4.  Rounded Corners can add a bit of Bravura to your chart and set it apart from the mundane. Go to Format Chart Area, select Border Styles, and put a check mark next to Rounded Corners.

5.  Formatting:  Add a little Zest to your charts by Formatting Your Plot Area. Right-Click and choose a Gradient Fill that adds a touch of Finesse while maintaining a professional look.

Taking a couple of extra minutes can Separate Your Work from the commonplace charts that we all see too often. A mere 2 minutes effort can Make Them Shine!

Formatting Keyboard Shortcuts

Greetings Excel Enthusiasts! I had a big Excel class of 54 professionals start this week at Continuing Education Group (CEG), so I have been a busy boy.

If you read this blog regularly, you know that I’m a huge fan of Keyboard Shortcuts. Over time, these shortcuts can save you literally hours of work time (and even improve your image as the resident Excel Guru…).

We all need to Change the Number Format in a cell or range of cells on a regular basis. Right-clicking and pulling up a context-sensitive menu is a good solution, but using the keyboard is Even Faster!

The shortcuts all start with “Ctrl+Shift”, so they are pretty easy to remember. The following are the most commonly used tricks for quick number formatting:

1.   General = Ctrl+Shift+~

2.   Number = = Ctrl+Shift+!

3.   Time = Ctrl+Shift+@

4.   Date = Ctrl+Shift+#

5.   Currency = Ctrl+Shift+$

6.   Percentage = Ctrl+Shift+%

Although it may take a little time to become fully accustomed to using these shortcuts, the time invested will certainly be worth it in the long run. They may even Free up some time for a Vacation!

Cheers!

Picture Charts Revisited…

This is a topic that is So Cool, it deserves another look! As we all know, Excel provides a great many Chart Types to choose from. But is that all there is? What if I want to make my chart really Stand Out?

Picture Charts to the rescue! You can Add a Lot of Zing to your charts with a very little used, (but Uber-Cool), trick. You can accomplish this cure to apathetic charts is by Replacing the Series Element (use Columns or Bars as your base chart, as they tend to work the best) with your own Graphic.

You can easily Add Vitality to your charts, and once again establish yourself as The Excel Guru, by doing the following:

1. Create a simple, no-background graphic

2. Copy the Graphic to your clipboard

3. Create your Chart using columns or bars (recommended)

4. Select the Chart Column or Bar Series

5. Go to your Home Tab and Paste your custom image

6. For Extra Impact, Format the Data Series by going to Series Options and choosing 0% Gap Width

Living in Southern California now, I chose to illustrate this technique with a Surfboard Graphic for the illustration above. To further demonstrate the difference this can make, I did a Side-by-Side illustration below. I chose a Bag of Money to replace the Boring, Boring, Boring column to depict the sales by month.

Picture Charts are another Cool Trick that can make your work get noticed. Another way to Add Interest and Inspiration to your Excel worksheets.

Try it out once and I think you will be hooked!

Concise, Convenient, and Controllable

A Sad Story 
Once upon a time in Corporateville, there was a hard-working, but underappreciated Excel professional. This fine Excel pro would create wonderful, detailed reports that went on and on in glorious detail for many pages in this professional’s workbook report. The professional’s work was of the highest caliber, absolutely impeccable. All the information was there, every last bit of it! So why then (Why Oh Why) did the stakeholders not make Good Use of all of the fine information?

The Answer 
The reason is really very simple: This fine professional’s work was not provided in a Concise, Convenient, or Controllable format.

The Solution 
The solution is to create what is typically known as a Dashboard. The Key Features in a Dashboard make it user-friendly and fit on a single computer screen. The user should be able to monitor and understand it at a glance.

Dashboards
Though user-friendly and simple in appearance, 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 Six Key Features You Should Strive for in your Dashboard: 

 1.  Although the data is the Star, one should keep in mind other elements

 2.  It should be Interactive with drop-downs, spinners, scroll bars, etc

 3.  Make it engaging so it will hold your audience’s interest, but…

 4.  Do not make it so stylized it muddles the message

 5.  Avoid 3-D charts (elegant, but 2-D versions are more functional)

 6.  Consider using some of the new Conditional Formatting features where desirable

When properly constructed, a Dashboard is Concise, Convenient, and Controllable. It is all about providing the stakeholders with Information, not just data. A dashboard should make the user’s life noticeably easier (and bring you the Appreciation you deserve!)

Excel Lists for Email

This topic came up again in one of my Excel classes this week and, considering how incredibly useful it can be to so many of us, I thought I would share it with you in this post.

Whether they are employees, clients, or vendors, Lists of Names of people are often kept in Excel. There are times, of course, when you may want to send an email via Outlook to a group of folks that you have in one of these Excel databases.

Outlook and some other email systems requires that the names be entered in a Last Name/Comma/First Name format. So what do you do if that is not the way your Excel list is arranged?

Let’s say you have a list of employees with the First Names in column A and Last Names in column B. Combining them into an Email-Friendly column of names in a “Last Name, First Name” format is so easy you’ll laugh. Here is what you do…

Let’s assume your table starts in cell A1. Using the magic of Concatenation, (there’s a word for yah!), put the following formula in C1:

=B1&", "&A1

Note: Be sure to use the quotation marks and insert a space after the comma.

This formula combines the contents of B1 (last name) with a comma, space, and contents of A1 (first name). Drag the formula down to fill Column C, and then copy and paste it into Outlook.

Bamm! Your Outlook Mailing List is Ready to Go!

INDEX + MATCH = INFORMATION

There are many ways to look up information in tables and databases. Excel provides us with functions such as VLOOKUP, DSUM, DGET, SUMPRODUCT, and several others. There are pluses and minuses when working with any of these tools, but in certain circumstances one or more of them can be an excellent solution. It’s all about returning Information in the way you want it.

Flexibility is always a plus, of course. When it comes to Versatility, the combination of using INDEX and MATCH can be hard to beat! This is especially true when comparing these powerful tools with the decidedly more limited VLOOKUP and HLOOKUP functions.

 For instance, using this combination alleviates the need to stay locked into the first column for a reference point. As I discussed in this blog in years past, a simple example goes a long way in illustrating this piece of Excel Magic:

 INDEX and MATCH can serve as your able-bodied servants. Let’s take a look at a simple example using the Illustration above.

 For instance, MATCH returns the Row Number of the location in an array of value you specify. Using the example above, the number “4” is returned by the formula:

 =MATCH("Tampa", $A$2:$A$8,0) 

INDEX, on the other hand, returns the Value that you identify by row number in an array. Using the example above, “Tampa” is retuned by the formula: 

=INDEX($A$2:$A$6,4) 

Combining the INDEX and MATCH functions is where the Real Power comes in. Let’s say we want the Code for San Diego. We can set up a Code Retriever as shown in cells E1:F2, by inserting the following formula in cell F2 (This will return "141"):

 =INDEX($C$2:$C$8, MATCH(E2, $A$2:$A$8, 0)) 

Using the INDEX and MATCH functions together enables the crafty Excel Guru to extract information from a table or database of nearly any size or dimension. It gives you the ability to sidestep some of the shortcomings of other techniques, and lets you Have it Your Way!