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 – Ta Da! Flash Fill & Auto Fill.
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.
By the way – do you know what’s a formula?
(This post first appeared in a ProfEd blog)
By Julie D