Some Lesser Known Time Saving Tips In Excel

Tue 24th May 2011

Excel is renowned for its familiar time saving features such as AutoFill and AutoComplete. There are, however, some less well known time savers hidden within this vast application. This article looks at some of these corner cutters.

One of Excel's greatest attributes is its ability to save the user time. Features such as AutoFill, where predictive data can be inserted automatically, and AutoComplete, which will prompt the user with a suggestion after only a few letters, or even just one. The latter is particularly useful when entering data that will be repeated often, such as towns or cities when entering addresses. In such a case, the name of any town that has already been entered in that column will appear in the cell after typing only one or a few letters, depending on whether or not there are other towns beginning with the same letters.

While AutoComplete is a commonly used time saving technique, there are less well-known ways that you can save time, depending on the data you are entering.

From 1942 on, a series of seven Hollywood films were made that became known as the 'road' movies. These musical comedies saw their stars, Bob Hope, Bing Crosby and Dorothy Lamour get into scrapes in a series of exotic settings. The first was The Road to Singapore and the last was The Road to Hong Kong, with Zanzibar, Morocco, Utopia, Rio and Bali in between.

I mention these films to highlight the fact that if you were compiling a spreadsheet of songs from these films, then AutoComplete would not be of much use as the first three words of each film title are identical and would have to be typed every time. Excel has yet another trick up its capacious sleeve though, and you can perform a simple operation that will save you having to type each title repeatedly.

In the scenario above you would type into a column the title of each film. Then, with the cell below the list selected, hold down Alt and press the down arrow key. This will being up a list of each entry in that column, from which you can select the desired title using either the mouse or arrow keys and when you press Enter the title will appear in that cell. Move down to the next cell and repeat the process.

Another little known way that you can save time is to get Excel to enter the zeroes if you are entering a set of values that adhere to certain properties.

For example you may have to enter data that has been rounded up to the nearest thousand, perhaps the approximate populations of towns and cities. A simple adjustment to Excel's settings will automatically fill in the zeroes for you after you have entered the number of thousands. So if you enter 1 and press return, Excel will convert this to 1000; enter 175 and this will come up as 175000. It is a simple operation to instruct Excel to do this, but there are a few points to bear in mind.

If you configure Excel to enter zeroes in this manner, you should be wary that the new settings will apply to all new worksheets so it should be switched off when not required. The setting up and disabling of this feature is such a simple process, however, it is certainly worth doing if you have to enter a lot of values with similar properties.

In the examples I give here, Excel will only add the zeroes to whole values. Anything entries that deviate from this, such as decimal points or dashes, will not activate the

For pre-2007 versions of Excel, go to Tools/Options and select the Edit tab. Check the Fixed Decimal Places box to activate the value display and set this to -3 (minus three) Click OK and your entries in that particular worksheet will appear as thousands.

For Excel 2007, click the Office button and select Excel Options (In 2010, you should display the File tab of the ribbon and select Options). Click Advanced in the dialog box. Make sure the Automatically Insert a Decimal Point box is checked and set the value as above

If you were entering data that had been rounded up to the nearest million, such as Premier League footballers' transfer fees, then you would enter -6 to instruct Excel to show six zeroes after the initial figure.

As the information above shows, as well as those commonly used time savers, such as AutoFill and AutoComplete, Excel has other, less well known ways to make life easier for us all. Exploring the nooks and crannies of this huge application, and finding rare gems like those above is an exciting and worthwhile task.

Author is a freelance copywriter. For more information on excel training course london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1688-lesser-known-time-saving-tips-excel.html