Unique Values

There are times when manipulating Unique Values or Items in your database is essential to your analysis.

Counting Unique Values
For instance, let’s say you have a database of everyone who has accessed your website, and you would simply like to know the Number of Unique Users. A clever formula (assumes your data is in A1:A95) does this very efficiently:

        =SUMPRODUCT(1/COUNTIF(A1:A95, A1:A95))

Deleting Duplicates
If you want to know the specific items which are Unique Values, and you don’t mind deleting the duplicates, you can use the Remove Duplicates command in the Data Tools group on the Data tab.

Creating a Separate List of Unique Values
If you wish to retain the original list, and create a separate list of Unique Values, you can turn to the Advanced command on the Sort & Filter group on the Data tab. Select your range and 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

Unique Values can pose Unique Issues but, happily, they are easily solved.

All Dates are Not Created Equal

As anyone who has used our favorite spreadsheet application for a significant amount of time knows, Dates can be occasionally tricky in Excel. As we discussed in 2011, what may Look Like a Date, may not “Play Nice” with other dates that you have in your worksheet.

To paraphrase Gertrude Stein, A Date is a Date is a Date is simply Not true. A good example is a date with an apostrophe at the beginning will look like an ordinary date when entered on a spreadsheet. But it is not…

Let’s say that you are importing records from another database application and you want to perform some Brilliant Analysis (since you are doing this, it must be “brilliant”…) that save the organization countless hours and expense. The trouble is that the dates are saved and exported as Simple Text in the database, and Excel is not giving you the results you deserve. Unless you can rely on the consistency of the way Excel will be handling the “Dates” in the worksheets, you obviously Cannot Rely on the value of your results!

So what is a Brilliant Analyst to do? DATEVALUE to the Rescue! Yes, indeed, my friends, DATEVALUE will calm your jangled nerves, relieve your upset stomach, cure that nagging doubt that you are being watched, bring fast relief for your hangover, and make any Dates in your worksheet work in unison with all of the dates therein. (Your results may vary on some of the attributes listed, but it will make your dates get along with each other…). DATEVALUE will instantly convert any looks-like-a-date Date into the standard Excel serial number, and you can then format it as you wish.

How Totally Rad is that! It may not sound like a big deal, but it can save you a world of grief in many circumstances.

The DATEVALUE function: It’s not going to bring World Peace, but it is Good Stuff to know…

Choose CHOOSE!

Whether you are at a restaurant or working on a spreadsheet, having Choices is almost always a Good Thing.   As we discussed in this blog a couple of years ago, it can be accomplished in Excel by using Nested IF Functions, but they have limitations (e.g. there is a maximum 7 functions allowed in most versions of Excel) and they are cumbersome.

Fortunately, there is a preferable alternative choice to using these awkward IF functions. The CHOOSE function is often a better selection, and it is much more versatile! The CHOOSE function is straightforward and simple to use and is best when combined with other Excel functions. It quite simply returns a value from a list based on a given Position (Index Number).

Here is the Uncomplicated Syntax:

CHOOSE( Index Number, Value1, Value2, ... Value n )

Some Basic Examples:

=CHOOSE(3, “North”, “South”, “Central”, “East”, “West”) returns Central

It also works with ranges:

=Sum(Choose(2, A1:A30, B1:B30, C1:C30) returns the Sum of B1:B30

You can, of course, link it to the value in a cell, making it much more Flexible. For example, you could link it to A1 which contains the Index Number.

If A1 contains the number 4, then =Choose(A1, “North”, “South”, “Central”, “East”, “West”) would return East.

The function can handle up to 29 options, which makes it a great choice in many real-life situations. The CHOOSE function is one of the Undiscovered Treasures in Excel. Give it a try sometime…

Integrity through Validation

Maintaining the integrity of your data is essential in the creation of meaningful reports. The old GIGO adage, “Garbage In, Garbage Out” is as relevant today as it was when an IBM instructor named George Fuechsel first used it 50 years ago.

Using the List option in the Validation tool in Excel is an excellent way of maintaining consistency and integrity within a spreadsheet. Combining it with a Named Range can make it more Effective and Efficient!

Let’s say you are using a dropdown box in a cell that refers to a List of customer service representatives in a large department. For convenience and to assure consistent spelling, you have created the dropdown box using the List feature in Validation.

If the department is large enough, updates to the Validation List will probably be a frequent and tedious task. Rather than having to put up with the hassle of adding/deleting employees from the reference list and then having to adjust the list range, you can simply Name a Range “CSReps”, (or whatever strikes your fancy), and refer to “CSReps” in the List dialogue box in Validation.

Just be sure to allow sufficient room in your CSReps range to add names in the future, and give it an occasional Sort to maintain an Alphabetical Ease-Of-Use.

This is one of those simple techniques that will further establish you as the Excel Guru you truly are.

Happy Excelling All!

Five Quick Solutions!

I am reasonably sure that all of us would like to be more productive, especially if it requires no additional effort. Often, this entails being aware of a Quick Solution to the situation on which you are working.

Whether you are employed in Human Resources, Analysis, Marketing, or another area, we can always use a quick solution at times. The following are 5 of my All-Time General Favorites:

1. Format a Number as SSN: Go to Format Cells / Custom and use the custom format code 000-00-0000 (Timesaver for HR…)

2. Remove Grid Lines from Worksheet: Go to View / Show and Uncheck Gridlines (Great for making a professional report…)

3. Freeze Rows on Top: Select row and go to View / Window / Freeze Panes (Improves functionality of your worksheet…)

4. Transpose Range of Cells: Simply copy the cells, go to the new location, and press Alt+ese (Voila! Rows to Columns or Columns to Rows!)

5. Protect Your Workbook: Go to Review / Changes / Workbook and assign a password (Excellent way for preventing unwanted changes by “creative” colleagues)

Make your life a little easier and take advantage of a Quick Solution whenever you can!

Page Break Control

Although most of us would like to have a truly Paperless world, that day hasn’t come yet, and probably won’t for many years. In the meanwhile, even the savviest Excel Enthusiast may need to Print a worksheet from time to time.

So, let me ask you something: Have you ever printed an Excel worksheet and not gotten the results you wanted? For instance, some of your data on the right side (perhaps just a couple of columns) may print on its own page. If your intent is to create an easily-referenced document to distribute in a meeting, this can be a Real Drag!

Now, by resizing your fonts and row/column widths, you can use Trial and Error to get your print the way you want it. There is, of course, a far better way. Simply go to View / Workbook Views / Page Break Preview. In this mode, you can easily exercise maximum control with a minimum of effort!

In Page Break Preview you get a view of your worksheet with page breaks shown as Blue Lines. Initially, the lines will be Dashed, indicating Automatic page breaks. By hovering you mouse pointer over one of the lines, you can select it and move it wherever you wish. The lines will then become Solid Blue, indicating Manual page breaks (i.e. those that you have set).

This can be particularly useful when you have just a few columns or rows that don’t initially get onto the printed page you want to present. A Small Word Of Caution, however: It may be tempting to manually slide your Page Breaks to include a great deal of columns or rows, so that you can have an Elegant One-Page Handout for your meeting. Whereas you will, indeed, get all of your data printed on one page, your audience may need strong Magnifying Glasses to read it!

Page Break Control; Another tool that enables you to have Excel do things Your Way!

(You may even have time for another cup of coffee before the meeting…)

The Surprising Ease of Database Functions

This week we are going to revisit what is typically referred to as an “Advanced Excel” function. The word “Advanced” can make it sound a bit scary, but it is, in fact, really quite Easy To Use. Database Functions are particularly useful in creating Interactive worksheets. The more commonly used of these special functions are DSUM, DAVERAGE, and DCOUNT.

A good place to start is to look at the DCOUNT database function, since once you know how to use this, the others will be simple additions to your Excel Arsenal. DCOUNT counts the cells that contain numbers in a database that match conditions that you specify, and has the following syntax, (Note that if Field is omitted, DCOUNT counts all records in the database that match the criteria):

DCOUNT(Database, Field, Criteria)

Let’s say that you have a database of 5,000 records and 4 Columns (or “Fields”), and your 4 columns have the following headers in cells A1:D1

Month, SalesPerson, Product, SalesValue

Refering to the Syntax,  DCOUNT(Database, Field, Criteria):

Database is your entire range, with the first row of the database containing labels for each column.

Field refers to which column is used in the function. Enter the column label enclosed between double quotation marks, such as "SalesPerson" or "SalesValue," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so forth.

Criteria is the range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as the argument includes a column label.

Now, let’s say that you want to count all sales for Smith which were greater than $1,000. Here is what you do:

Create a Criteria block in cells F1:I2 like the following illustration:

Then go to an open cell (e.g. F4) and put the following formula in place: =DCOUNT(A1:D5001, “SalesValue”, F1:I2)

Bamm! That is all there is to it! Try it out sometime, and you will be amazed at the new Muscle it will give you in Excel.  And not only that, it's Easy!