Save Your Wrist (And Look Cool Doing It…)

Yesterday while breezing through an Excel workbook and entering some repetitive data, I realized I have never mentioned a couple of my favorite Gee Whiz tricks. They not only have Wow Factor, they are also very practical and Ergonomic!

Any time you can use your keyboard and avoid your mouse, you are probably doing your wrist a favor. There is typically much less stress using the keyboard.

Elegant Navigation

Rather than using a mouse to navigate to another worksheet within an Excel workbook, you can simply use CTRL+PAGE DOWN to move one worksheet to the right or CTRL+PAGE UP to move one worksheet to the left. Not only is it efficient, but you will Look Cool doing it!

Enter in Bulk

Let’s say you want to enter the same numbers or same text in a block of cells. There are a number of inelegant ways of doing this, but if you want to save your wrist and be Totally Cool in the process, you can do the following:

1. Select the entire range of cells you want to contain the same content

2. Type your words or values (be sure to do this while having the entire block of cells selected)

3. Press CTRL+ENTER

Presto! You are a Genius! Go ahead, give it a try!

Cell Counting Techniques!

It is frequently handy to be able to count certain types of cells in a worksheet range. The following are four common Excel Functions that accomplish this quite efficiently:

1. COUNT: Simply counts the number of cells in a range that contain a number.
2. COUNTA: Counts the number of non-blank cells in a range (including those with text).
3. COUNTBLANK: Counts the number of empty cells.
4. COUNTIF: Flexible counting function (that we will explore with some examples)

So how can I use the COUNTIF function, you might be asking. For a bit of Inspiration, the following are some examples (in each case, the formula uses a range named Data):

Count the number of cells that contain the word "Charles" (not case sensitive):
=COUNTIF(Data,"Charles")

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

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

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

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

Count the number of cells that contain a value greater than or equal to 15:
=COUNTIF(Data,">=15")

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

Being able to count cells on your terms is a great tool to have in your Excel Tool Kit. Especially the versatile COUNTIF! (I could say, “Make your spreadsheets Count”, but that would be way too corny…).

Cheers!

You are the Excel Guru!

Magic
Using the keyboard to quickly effect major changes in an Excel workbook can leave the impression of Magic to the onlookers (including your boss) of a Skilled Excel Practitioner. Here are a couple new favorites of mine that can help you increase your status as Excel Guru (or Excel Master or Excel Boss if you prefer).

Let’s say that you have 12 columns of data for which you want to calculate the Sums. Simple matter, of course, but if you want to do it in True Guru Style, simply select the entire block of data, and press Alt and = on your keyboard. Presto! The sums for all of your columns will appear like Magic in the row below.

More Magic
You can do more Magic by once again selecting the entire range of data and choosing Average, Count, Max, or Min from the AutoSum icon on your toolbar. The calculations will Instantly Appear for all columns of data!

As with nearly all things Excel, there are several different ways of achieving the same results. The Key is to do it with Style and Panache! After all, that is only fitting for a true Excel Guru…

I am Here for You

Have you ever been working on an Excel project and gotten Stuck on making it work like you want it to? Have you ever thought to yourself, “There must be an easier way?” Have you ever wished you had the services of an Excel Expert (or another expert if you are one yourself)?

We have all been there, and if you have been doing Excel for a long time, you probably been there more than once.

I was looking over past posts and questions from readers and, (feeling curiously generous with my time right now), I have decided to make a Special Offer to All of My Kindle Blog Subscribers: Now through the end of June, if you have an Excel project or problem on which you could use some help, please email me at the following address. I am devoting up to ½ Hour to each of my subscribers for any Excel help they need through June.

rdelamartre@earthlink.net

This is NOT, I assure you, some sleazy way to get you to buy anything, (services or whatever), but merely an altruistic offer to my Kindle Readers for whom I am truly grateful.

I am here for you, so if you think I might be able to help, please send me an email. I will be delighted to hear from you!

All the best,

~Bob

Good Databases (Part 2)

Last week, we looked at what constitutes best practices in creating a Good Database. Now we are going to examine how to best Extract Information from your data.


First order of business is to Name Your Ranges. An easy trick to use is to select your entire database (including the names of the ranges in the header) and go to Define Names and choose Create Names from Selection. Put a check next to Top Row and click OK – Voila, you have just named all of your ranges!

Now is when the fun begins! (This may sound a bit complex at first, but the concept is actually very simple). What we will be working with is called the SUMPRODUCT function.

SUMPRODUCT is an amazingly versatile function which can be used in many different ways to extract information from databases. You use its simple logic of “Yes or No” (0 or 1) to multiply corresponding components in user defined arrays, and return the sum of those products. The format is expressed as =SUMPRODUCT((array1)*(array2)* ...).

So enough talk! Let’s actually use it and see what it can do. Duplicate the database below, (or use one of your own), and name the ranges. Then, (using the example database), in any cell outside the database (if you wish, it can be on a separate worksheet in the same workbook) enter =SUMPRODUCT((Office = “West”)*(Sales)). Please note the use of Double Parentheses and Quotation Marks around West.

So, what is the SUMPRODUCT formula doing in this case? It is going through your entire database (it would work just as well with a database of 10,000+ records) and each time the first parameter of (Office = “West”) is true it assigns the number 1 and each time it is false it assigns the number 0. Then it multiplies times the Sales figure and Adds them all up!

Please hear me when I say this: If you are into Excel, please give this a try. I can guarantee you that you will be a Total Rock Star in your company!

Next week: One last, really cool example (a bit more complex) of using SUMPRODUCT with a database. See you then!

Good Databases (Part 1)

It has recently occurred to me that the design, construction, maintenance, and information-mining of Good Databases are the quintessential keystones of knowledge for any advanced Excel user. Today will be the beginning of a Three-Part Series on this vital topic.

Creating a Database in an ongoing Excel workbook can save you time, money, and frustration. By creating a database for information that is routinely updated, you can automate your reports and simplify your users’ interface.

Today we will look at what constitutes a Good Database, and what pitfalls to watch out for.

First of all, a database should contain data, and that is all! No formulas should exist in a database, just pure Data waiting to be turned into Information on a separate worksheet.

Secondly, there should be no blank rows (called “Records” in a database) and no blank columns (called “Fields” in a database).

Thirdly, put only one piece of data in each field. This will eliminate the need for repeating fields, and make your information-mining much easier.

Lastly, make sure the information is entered in the proper field. If the data entry person (maybe you) cannot find the right place for a piece of data, perhaps the database needs some redesigning.

Next week, we will look at some phenomenal ways of deriving information from your new, improved database. You can use one of your own, or create a database using the example below.

Fantastic stuff coming! Cheers!

AutoFilter and Beyond!

AutoFilter is a quick and easy tool that allows you to display only the desired rows of data that fit into your defined grouping. By filtering out the rows that do not interest you, your view of your data is instantly enhanced.

To access AutoFilter, go to Data, choose the Filter menu, and click on AutoFilter. AutoFilter Arrows will appear at the top of each column, allowing you to filter on whichever column you choose. Filtering by multiple choices in the columns can be done to narrow your focus on the data.

Now, this is terrific, but wouldn’t it be great to be able to use the functionality of AutoFilter whereby you merely select the value in an active cell for your filter criteria?

Although it is easy to do this in Excel 2007 and the upcoming 2010 versions, (improved functionality), you can use this cool feature in prior versions of Excel versions of Excel as well (if you know where to look).

For instance, in Excel 2003, you should:

1. Click on Tools and go to Customize
2. Choose Data under the Categories and find the “AutoFilter” in Commands
3. Drag the AutoFilter icon to your toolbar

What is confusing is that this flavor of “AutoFilter” is that it works differently than the one you most familiar with! Bear with me, though, as it is worth the bit of extra effort in getting it set up.

Here is what you can do with it:

1. Point to any cell in your table
2. Click on your new “AutoFilter” icon
3. The table will automatically filter according to your selection
4. Cumulative filters can be added by selecting another cell and clicking on the icon again

How absolutely Cool is that?!? Try it out; you will find how truly Amazing AutoFilter can be!