Wednesday, April 27, 2011
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!
Wednesday, April 20, 2011
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…
Wednesday, April 13, 2011
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!
Wednesday, April 6, 2011
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!