Why to Use Auto Fill and Flash Fill in Excel
Auto fill is an awesome feature that helps you quickly fill data in Excel. You can fill single values or formulas (basically copying and pasting), or fill a series of common list values, such as numbers, dates, days of the week, and months of the year. You can even set up your own custom lists that you often enter manually, such as location lists, employee lists, etc.
Flash fill is similar, but it helps you combine and extract data. If you have first name and last name in separate columns that you need to combine, or vice versa, flash fill can help you do this very quickly. It learns as you type, suggesting potential data to enter based on patterns and common data. Your original data is left intact should you need it later.
How to Use Auto Fill and Flash Fill in Excel
To use auto fill, just drag the fill handle down, right, up, or left. The fill handle is the dot/icon in the lower-right part of the selected cell/range. You can also double-click on it if there is data to the left and it will fill the selected range down to the last row to the left. Another way is in the Home ribbon tab – in the editing section on the right, click the Fill button and select one of the options.
To make your own custom list that you can quickly fill, go to the File ribbon tab, then Options, then Advanced, then scroll to the bottom and click the Edit Custom Lists button. You can then manually add a list of items, or highlight a range of cells in your worksheet to use as a custom list.
To use flash fill, just type in the first item manually in the same row as the data you want to combine or extract. For example, to enter the full name when you have the first name and last name already, just type the full name for the top record/row. As soon as you start typing the next record, flash flill will suggest the rest of the records, and when they are displayed, you can just click Enter to apply them. Another way is to type the first record, and with that cell or one below it selected, chose flash fill from the same location as auto fill (Home ribbon tab, Fill button, flash fill option) or from the Data ribbon tab, click the flash fill button, or use the shortcut Ctrl+e.
The XLEV8 Excel add-in includes the Fill Picker macro, which lets you perform each of these commands with one picker prompt that you can assign to a keyboard shortcut. It’s like having all the fill-related commands available with one shortcut!
Recent Comments