AutoFill and Flash Fill in Excel

Introduction to AutoFill and Flash Fill

AutoFill extends data based on patterns and formulas, while Flash Fill learns by example and reshapes the data. AutoFill and Flash Fill in Excel can be used for data cleanup or quick data entry. Both tools improve efficiency and reduce manual work when managing data in Excel. In this post, we will cover what Autofill and Flash Fill are, what options they provide for quick data entry, and how to use them.

AutoFill in Excel

AutoFill is a feature in Excel that lets you quickly fill cells with data that follows a pattern or is based on data from other cells. It can copy values or formulas and extend recognizable patterns like numbers, dates, weekdays, etc.

• It uses the small square in the bottom-right corner of a selected cell (+ the fill handle).
• It is best used for entering sequences, date/time series, copying formulas down a column, or creating quick lists.

How to use AutoFill

Method 1: For Basic fill and Copying

• Click on the cell you want to AutoFill.
• Hover your mouse to the bottom-right corner of the selected cell.
• When the cursor turns into a plus sign (+), click and drag horizontally or vertically to fill the adjacent cells

Method 2: Manually choosing what gets filled

After dragging, click the small Auto Fill Options button that appears, which has the following options:

Copy Cells with AutoFill

• Copy Cells will duplicate the selected cells.
• For example, if the number 1 is already present in a cell when using the ‘Copy Cells‘ option, Excel will duplicate the selected cells based on that value.

Excel Fill Series

• Excel Fill Series feature works on numbers, dates, Time, etc.
• This will add an increment of 1 and extend the series.
• For example, if the number 1 is already present in a cell and the ‘Fill Series’ option is used, Excel will increment the values by 1, filling the cells with 1, 2, 3, 4, 5, and so on in the selected Cells.

Using “Fill Formatting Only” Option

• The Fill Formatting Only option will copy the formatting of the selected cells but not their values.
• In the image above, only the cell formatting, such as borders and the green shading has been copied.

Using “Fill Without Formatting” Option

• If you select the Fill Without Formatting option, it will copy values and patterns, but will not copy the formatting of the selected cells.
• In the above image values have been copied for Numbers, 4,5, and 6 without formatting.

AutoFill Days/ Weekdays/ Months/ Years

• These options appear when a date is selected; they can fill up the cells by days, Weekdays, Months, and Years.
• Click on the cell you want to AutoFill.
• Hover your mouse to the bottom-right corner of the selected cell.
• When the cursor turns into a plus sign (+), click and drag horizontally or vertically to fill the adjacent cells.
• Select the option days, Weekdays, Months, or Years.

AutoFill Date in Excel

• Fill Days in Excel is used to Autofill Date in Excel and this function automatically populate cells with the days of the week, like Monday, Tuesday, Wednesday, etc., either horizontally or vertically.
• In the image above, the date 05/11/2021 was entered, and when the ‘Fill Days‘ option was selected, Excel automatically filled the subsequent cells with consecutive dates like 05/12/2021, 05/13/2021, and so on in the selected cells.

AutoFill Weekdays

• The Fill Weekdays option automatically fills a series of dates, but skips weekends (Saturday and Sunday).
• It’s useful when you want to generate a list of working days only.
• The image above shows that the date 05/11/2021 was entered, and when the ‘Fill Weekdays‘ option was selected, Excel automatically filled the subsequent cells with consecutive dates in the selected cells while ignoring the weekends (Saturday and Sunday) on 05/15/2021 and 05/16/2021.

AutoFill Months

• Fill Months is an Excel AutoFill feature that automatically fills a series of dates by increasing the month while keeping the day and year constant.
• The image above shows that the date 05/11/2021 was entered, and when the ‘Fill Months‘ option was selected, Excel automatically filled the subsequent cells with dates 06/11/2021, 07/11/2021, and so on in the selected cells.

AutoFill Years

• Fill Years automatically fills a series of dates by increasing the year, while keeping the day and month constant.
• The image above shows that the date 05/11/2021 was entered, and when the ‘Fill Years‘ option was selected, Excel automatically filled the subsequent cells with dates 05/11/2022, 05/11/2023, and so on in the selected cells.

Flash Fill in Excel

• Flash Fill creates non-dynamic and static results; it transforms data by recognizing patterns.
• To use this feature, place your cursor in a new column next to the data, then type the desired result for the row.
• For Example, as shown in the image above, if you want to separate First Name from First and Last Name.
  • Place your cursor in a new column next to the data.
  • Then enter First Name in one cell.
  • When the cursor turns into a plus sign (+), click and drag vertically
  • After dragging, click the small Flash Fill Options that appear.
  • The first names will be filled in the selected cells by extracting them from the column containing full names.

Shortcut Key for Flash Fill

• In a column next to your data, type the desired result manually in the first row.
• Then Press Ctrl + E Shortcut key for flash fill
• Excel will detect the pattern and fill in the rest of the column.

Using Flash Fill from the Excel Ribbon

• Enter the data in one cell
• Then select the cell where you want Flash Fill to begin.
• Go to the Data tab on the ribbon.
• Click Flash Fill located in the Data Tools group.

Summary

• AutoFill helps in quick data entry by extending sequences and formulas with the fill handle. It’s perfect for series, dates, and copying formulas.
• Flash Fill cleans and reshapes text-based data; it is ideal for splitting, joining, and reformatting.
• You can use AutoFill for ongoing, formula-driven work, and use Flash Fill for quick, one-time transformations.

To explore all our posts covering the fundamentals of Excel, please follow this link.

FAQs