Home > Support > Hints and Tips > Tip of the Week

Weekly Computer Tip # 114
11 June 2005

Start each week with a free tip

To subscribe, please type your email address in the box below:

Automatically convert number entries to a formula (Microsoft Excel)

Thanks to all of you who noticed there was no tip last week ... Before starting my next big project developing a course on Records Management (SOX ROX ;-), I decided to have a holiday. Suppose I should have been suspicious when hubby offered to find a place to go. Turned out I was somewhere with no signal on my mobile. And no telephone in the hotel room to dial into the Internet to check my email or send the weekly tip. So here it is ...

About half a year ago the Finance Manager of Pembroke College (hi, Andrew!) showed me a way to automatically convert any entry that begins with a number to a formula, without preceding it with an equation symbol such as = or + . This obviously is particularly helpful if you mainly use Excel for calculations.

Here's how:

  1. Select the Tools, Options command.
  2. On the Transition tab, select the "Transition formula entry" check box.
  3. Press the OK button.

One bit of warning, though. Having switched on the "Transition formula entry" option, any dates you enter (i.e. a number entry in itself) will be treated as a formula. For instance, today's date (11/6) will be turned into 1.833333333 (i.e. 11 divided by 6). A workaround is to preface the date entry with the apostrophe character ('). But even better ... automate the abovementioned steps and create a macro. This is especially helpful as the "Transition formula entry" option only works for the workbook you switched it on for, so it's well worth the "effort". (It's easier done than said - honest!)

If you want to find out more about how to create a macro, see tip # 92 (http://www.roem.co.uk/tip_92.html). If you want to take it one step further, why not customize your toolbar and add a Macro button? (See tip # 96, http://www.roem.co.uk/tip_96.html.) Or why not book an hour with someone (me!) who knows how to do what you want to do with your computer. Just 75 pounds can be worth many hours of your time, not to mention the savings in energy that results from the frustration of trying to understand something you are not familiar with.

Finally, some time ago I was approached by IT Training Magazine, the UK's leading source of quarterly IT training news, whether they could kick off a brand new Trainer's Blog series using "Diary of a Training Consultant" (http://www.roem.co.uk/blog.html). Having heard nothing from the editor until earlier this week I was beginning to think my appearance in the April issue had been a one-off. (I'm sure my inferiority complex isn't as good as yours ;-) But apparently we hit it off and I'll be back in the next (July) issue. Ahh ... I love happy endings!

Until next week.

Email this page to a friend

Enter recipient's email address in the box below:

PS I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.

Home   Training   Support   Clients   Contact Details   News   Disclaimer  

© Roem Ltd. All Rights Reserved
June 2005