Most of us are familiar with the simple Filter tool found in the Sort & Filter group on the Data tab. If you apply the filter and use the SUM function, it will total Both visible And hidden data.

But what if you want total Only the Visible Data, and not the hidden rows that are shown in the filtered data? This is where the SUBTOTAL function becomes useful! One of the easiest ways (and little known ways) to apply the SUBTOTAL function is to:

1. First Filter your dataset
2. Select the cell directly below the range you wish to total
3. Click on the AutoSum button

Rather than using the SUM function, Excel will under these circumstances automatically use the SUBTOTAL function and will provide you with a total of Only the Visible Data. How Cool is that!

Just be sure to filter your data first, and then hit AutoSum. It couldn’t be easier!

Watch that Date!

Most of us these days have gotten into the habit of using four digits when referring to a year. It is convenient, of course, to use just two digits, but Beware, Excel uses some rules that may Surprise you when entering dates by using just two digits for the year. Two-digit year dates from 00 to 29 are interpreted as 21st century dates, but years from 30 to 99 are understood by Excel as 20th century dates.

For example, if you enter 7/4/29, Excel construes your entry as July 4, 2029. If, however, you enter 7/4/30, Excel interprets it to be July 4, 1930!

The reason this is so is because Excel uses a default boundary year of 2029. This can be changed, if you wish, by going to the Date, Time, Language, and Regional Options category on your Control Panel. Then click on Change the format of numbers, dates, and times and enter a different date on the Date tab after clicking the Customize button.

When working with data, Surprises can be Bad. The best practice is, obviously, to use four digits when entering the year. Of course, there is that Convenience thing…

Duplicates Revisited

Recently a reader wrote in saying he was combining two large mailing lists and wanted an easy way to remove the duplicates. When it comes to removing unwanted duplicates in a database, users of Excel 2007 and Excel 2010 have it easy. Here is how you accomplish this useful 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 columns 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

But what if you are still using Excel 2003? Well, it is not quite as slick, but there is a reasonably quick way to do this with the older software as well (by the way, this also works with the newer versions):

1.  Select the field with the duplicates
2.  Go to Data / Filter / Advanced Filter
3.  Check the box in the lower left that says Unique records only
4.  Put the starting cell of the new range in the Copy to dialogue box
5.  Click OK

Yes, it certainly is easier and more versatile removing duplicates using Excel 2007 or Excel 2010 (one more good reason to upgrade). It truly is easier to do this with the newer software (and Easy is Good!)

Whichever version of Excel you have, this will help you Maintain the Integrity of your database. So, give it a try: Get rid of those Duplicates!

Validation + Vlookup = Power!

Sometimes the combination of two easy-to-use fundamental Excel tools can produce powerful results in your workbook. Using a DropDown Box in an Excel report can add interactivity, efficiency, and professional flair to your worksheet.

A very simple way to do this is by using Validation. In Excel 2007, simply select the cell in which you want the dropdown, (such as the cell F2 directly below Sales Rep in the above graphic), click on the Data tab and choose DataValidation. Then choose List and select a range for your Source of dropdown entries.

Alacazam! Instant DropDown Box!

Now you can combine the dropdown with the elements of a Vlookup function to create a powerful and interesting report in Excel. For example, using the table below, you can put the following Vlookup formula in cell G2 directly under the 2011 Sales in the above graphic:

=VLOOKUP($B$13, $A$2:$D$6, 4, FALSE)

As a refresher, here is how the 4 arguments of the Vlookup function work:

=Vlookup(lookup_value, table_array, col_index_number, range_lookup)

1. lookup_value: In this case the Sales Rep
2. table_array: Use an absolute reference ($A$2:$D$6), Note: Be sure not to use the header of your table.
3. col_index_num: In this example, the 2011 Sales is in column 4 of our lookup table.
4. range_lookup: If you want an exact match, you should use “False”

Validation + Vlookup = Power! Give it a try!