Where are My Worksheets?

It is easy to create an Excel workbook with so many worksheets you can’t see them all at a glance. Scrolling to find them is a drag, so the creators of Excel have provided a convenient alternative.

If you want to see/use an inventory of your worksheets, all you need to do is right-click ("right-click is our friend") the tabs navigation buttons and a floating list of all the worksheets will appear.

Where are my worksheets? Right-click navigation and Bamm! – There they are!

Ready for Prime Time?

Taking a walk down Geek Lane this week with a look at exploring Prime Numbers in Excel. Specifically, Mersenne Prime Numbers. A Mersenne Number is a number in the form of Mn=(2^n)-1. Although Excel is not robust enough to explore this in depth, it is nonetheless interesting to see what can be done.

So, assuming you have a bit of geek in you, here is what you can do.

1. Create a simple spreadsheet with a similar format to the following starting in A1:

2. Starting in A3, insert consecutive numbers starting with 2 into Column A.
3. In B3, place the formula, “=(2^A3)-1”and copy down with relative references
4. Go to the Blue Moose Technology site (Thanks, Blue Moose!) and copy the prime code into a new module in your workbook
5. Finally, use the resulting “ISPRIME” custom function starting in C3 to determine if the numbers in the B column are prime

Obviously, this exercise is not of interest to everyone, but if you find both Excel and Prime Numbers exciting, give it a try. You might find you are “Ready for Prime Time”.

Microsoft Excel 2010 Technical Preview

Reading and speculating on future versions of Excel (and Microsoft Office in general) can heighten anticipation for what users always hope to be a panacea for all of their work-related challenges. The latest version of Excel is due to be distributed to select users for a Technical Preview this July.

The new application is purported to offer a forward-looking browsing experience and an essentially unprecedented open source interface. There are reportedly many cool new features, including much more powerful Pivot Table tools. This is particularly exciting for anyone doing business analysis in Excel.

Also of note, the new MS suite will be released in both 32 and 64 bit versions, so hardware that can run the current Office 2007 will be able to handle 2010 as well.

If you are interested in learning more or being considered for taking part in the Technical Preview, you can get additional information and register by following the link below:

http://www.office2010themovie.com/

Happy Excelling!

Paste Special is Our Friend

Have you ever copied some cells in a workbook and pasted it elsewhere, and gotten errors in the target location? If you want to copy what you see and have it appear in the new location exactly as it appeared in the source, you probably should give Paste Special a try.

The problem with using ordinary Copy/Paste is that it will duplicate any formulas contained within the data and transfer them to the new location in a relative manner (I know, I know, let’s not get the relatives involved…). Here’s what you do:

1. Select your data, right-click and choose Copy
2. Select your target cell, right-click and choose Paste Special
3. From the Paste Special menu, choose Values and click OK
4. Bamm! What you see is what you get!

The next time you find yourself in a Sticky Situation (“Sticky” and “Paste” get it… ), try using Paste Special. It really is Our Friend!

Removing Duplicates

When it comes to removing unwanted duplicates in a database, users of Excel 2007 have it easy. The following is a best practice to accomplish this useful task:

To get an idea of what you are working with, first, use the Conditional Formatting:

1. Select the key field(s) in the database containing the possible duplicates
2. Go to Conditional Formatting command in the Style group on the Home
3. Choose Highlight Cell Rules / Duplicate Values
4. Choose a Cell Format (bright yellow fill is good for this purpose) and click OK

If you then want to remove the highlighted duplicates you can do so quite simply by:

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 all of the columns and click OK
4. A message will pop up telling you how many (if any) values were found and removed

This is a good way (Easy is always good) to help maintain the integrity of your database. Give it a try: Get rid of those Duplicates!