More Tips From Excel's Rarely Used File

This article explores some formulas and tips and tricks that are quirky and fun in Excel, although possibly rarely used!

Some time ago I wrote an article on here titled Tips From Excel's 'Rarely Used' File, in which I demonstrated some of the things Excel can do, but which you will quite possibly never have reason to do. For example, I can't see there being a rush of operators wanting to change the date separators from strokes to hyphens, but I showed how it could be done anyway. And I have unearthed some more precious gems from the darkest corners of Excel's vast vault for you to try.

You may want to convert numbers into Roman numerals and, if this is your bag, Excel can do this very easily. To convert numbers to numerals for the current year, for example, simply type =ROMAN(2010) in the formula bar, and you will see that the numbers convert to MMX. It should be noted, however, that when you create these Roman numerals Excel converts them to text values and they cannot be used in calculations.

How about changing the orientation of your worksheet? (This tip only works with Excel 2002 & 2003). If you want to your A1 cell at the top right of your screen, go to Tools, Options and in the dialog box click on International. On Default Direction click Right to Left. The changes won't happen immediately, but the next workbook you open will have the A1 cell in the top right corner.

Have you ever noticed that when Excel thinks you have made an error (and it may be the case that what looks like an error to Excel is actually intended by you) it flags the errant cell with a small green triangle in the top left corner. You can remove these very easily by doing the following:

In Excel 2002 & 2003 choose Tools, Options, and select the Error Checking tab. Uncheck the Enable Background Error Checking box and hit OK. Done.

For Excel 2007 click Office then Excel Options, then click Formulas. Uncheck the Enable Background Error Checking box and click OK. And there you go; all your errors from now on will go unnoticed.

Those scroll bars at the bottom and right side of the worksheet can be removed if you so wish. For all pre-2007 versions of Excel, go to Tools, Options and select the View tab from the dialog box. In the area marked Options, uncheck the Vertical Scroll Bar box and the Horizontal Scroll Bar box and click OK. Now you must move through the worksheet using the cursor control keys. This action does actually serve a useful purpose in that it does make maximum use of the screen space.

As I said in the first of these articles, although you may never have cause to use any of these tips, trying them out is a useful way to gain a better understanding of how Excel works. These totally different tips also serve to demonstrate the vast range of Excel's capabilities, something that would perhaps be best got to grips with on a training course.