Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Excel Data Entry Tricks

Linked In member Victor Chan publishes an excellent blog at Launch Excel. In two of his recent articles,

 

Working with Data in Excel Part 1- 10 Excel Data Entry Tips Everyone Should Know

and

Learn how to become an Excel Power User with our Top 20 Mouse Tricks for Excel

Victor discusses very useful Excel Data Entry techniques. Check out Victor’s articles to learn how to improve your Excel efficiency. He also offers a nicely organized collection of Excel shortcuts free if you subscribe to his blog.

Here’s another nice trick I recently learned about. Create and use a custom list to manage lists of names that you use repeatedly in a worksheet.

 

Create and Use Custom Lists

You can create and use custom list for sorting and for speeding data entry

For example, you are using an Excel Worksheet to manage shift assignments for the same group of people every week. Occasionally you need to sort the list by the names but you do not want the sorted list to be in alphabetical order. A custom list will give you that flexibility.

image

 

Start by typing the list of names, in the order you would like them, in an out of the way place. This list can be located on an otherwise unused worksheet for example. Just type one name in a cell and press enter and type the next name. When you are finished you should have a vertical list of names with no blank cells. I have created a list of six names but your list could be much longer.

If you select this list before starting the next step you will be able to import it into your custom lists with just the click of a button.

 

image

 

Now, in Excel 2010, click the File button (in 2007, click the Office button) and click Options (in 2007, Excel Options).

Select Advanced. You will need to scroll down a bit. Look for the Edit Custom Lists button and click it.

That will bring up the Custom lists dialogue. There will be at least four custom lists already there if Excel was properly installed on your system.

 

 

image

 

You should see the range you selected already filled in beside the caption, “Import list from cells.” Click the Import button and your newly created list will appear at the bottom or your custom lists.

 

 

 

 

Sort Using a Custom List 

Whenever you want to sort a list that contains these names into the order of your custom list, all you need to do is select the Sort button on the Ribbon and specify custom for the sort  level that refers to the column containing these names.

 

image

When the custom list dialogue appears, select your custom list.

Use a Custom List for Data Entry

If you have ever used autofill to create a list of months or days, you will love this added benefit of having a custom list. Once you have create a custom list, you can type one of the names form the list and then use autofill to create a list of all the names in the order of the custom list.

So, for the shift assignment example, when it comes time to create new shift assignment list, all you need to do is type one of the names from you custom list and use autofill to type the rest for you either vertically or horizontally.

Don’t Discard Your Original List

If you must occasionally add or remove names from your roster, do it in the list you built to import into custom lists. When you have that list up to date, select it and go to the Custom Lists dialogue. Click on your original custom list and delete it, then Import the revised list.

Using this technique will save you a few minutes every time you have to create a new roster for you shift assignments. The time it took you to build and import the list as a custom list will quickly be paid back ten-fold.

Comments are closed.