Excel is great when you’ve got large amounts of data to maintain. But entering that data into your document and formatting it correctly can be time-consuming and tedious. Here are two automatic-fill features to ease that task.
Try this: Consider using Flash Fill if you have a list of first and last names (or other combined data) and you need to separate the first names and last names into different cells. With this feature, Excel can finish what you start.
To use Flash Fill, follow these steps:
- First, ensure that Flash Fill is enabled. Click on the File tab on the main ribbon, and choose Options.
- Choose Advanced from the list on the left side of the Excel Options dialog box.
- Select the Automatically Flash Fill check box and click OK.
- Begin typing your list of first names.
- After you type a few names, select them and then drag the selection box in the lower right corner to see if Excel can figure out what you’re doing and fill in the rest of the first names. Then do the same for the list of last names.
Remember: Flash Fill is a work in progress, and sometimes Excel can’t keep up with you. But it’s a pretty robust feature that’s certainly worth a try when you’ve got a monstrous and tedious job to do.
Another idea: When you need to create a list of data that follows a pattern, let Excel’s Auto Fill feature do the mundane work for you.
Example: To create a list from 1 to 10, follow these steps:
- Type the number 1 and hit [Enter], then select that cell and hover your mouse over the black box in the lower-right corner of the cell until the cursor forms a plus sign.
- Drag ten rows down, and release.
If Excel didn’t quite read your mind, don’t despair! You might be left with 10 rows of the number 1 instead of the consecutive numerals you expected. There’s a fix:
- Click on the Auto Fill Options button in the lower-right corner of your selection, and choose Fill Series from the resulting dropdown list.
- If your list is more obscure, simply provide more information. For example, to start at 0 and increase by 0.5 in each row, type the first two rows of data, select those cells, and use the selection box in the lower-right corner to invoke AutoFill.
Benefits: You can also use AutoFill with other number formats such as date and time. Excel provides many date-specific options on the Auto Fill Options dropdown list, such as Fill Days, Fill Weekdays, and Fill Months.
These two awesome features will help you deal with lengthy data in no time.
Master Excel and MS Office
For more ideas on how to make Excel’s untapped features work for you, check out Dartnell’s Inside Microsoft Excel-PLUS. Every monthly issue is delivered straight to your inbox and packed with easy-to-follow instructions to help you learn new techniques on the fly. Whether you want to pick up specific data analysis tricks or seek general guidance on simplifying complex and repetitive tasks, you’ll find what you need in the pages of this practical guide for serious Excel users.
And if you need hands-on training in Excel and other Microsoft Office applications, join AudioSolutionz’s “Microsoft Office Training Virtual Boot Camp” and learn time-saving tips and techniques for working with MS Office in more efficient and effective ways.
