AutoFill and Flash Fill

In Excel, the autofill feature can be used to fill the common patterns such as numbers, dates, days, and much more. It is not just limited to downwards but you can fill data upwards, right and left by using the Fill Handle.

• It is a time-saving feature that can help you fill data in the rest of the cells without typing manually.
• It is not just limited to numbers, dates or days but also you can use it to fill the formulas as well with the use of proper cell references.
• You can AutoFill data in two methods:
  • Using your mouse to drag the Fill Handle for the rest of the required cells which is more user-friendly.
  • Use the Home Tab and then select the direction to fill (Left, Right, Down or Up) which is not much preferred by users.

It is a small square that is located at the bottom-right corner of any selected cell or a range of cells in your Excel. You can use the Fill Handle by dragging into your required cells or even double-click to quickly autofill until Excel finds the correspondence last row.

• Start by entering in your first cell the number you want the series to begin. For example, let’s enter 1 in cell A2.

• Enter the Date you want in cell A2 and drag it as per your requirement to fill the series of dates in the range.

• To get alternate dates, enter 2 dates such as 01-09-2024 and 03-09-2024 and then select both the cells that contain the date, drag the Fill Handle and it will AutoFill the series with a date by skipping as Excel understood the sequence from the starting two cells.

• Enter the Day in cell A2, the First Month in cell B2 and the First Quarter in C2.
• Select all 3 cells from A2 to C2 and use Fill Handle this time see the magic happening where Excel will restart the list such as Days and Quarters as shown in the below image.

• Enter the formula in the first cell as per your requirement. For the below example, we are calculating the Total Amount for each Product so we are multiplying the Quantity with the Rate.
• Once the formula is created (B2*C2), select the cell and you can use the Fill Handle to either drag it for the rest of the cells or simply double-click so it will automatically fill until it finds the last row.

This amazing feature in Excel was introduced in version 2013 which senses the patterns of the data and suggests you fill. It is turned on by default but it can also be used manually from the Home Tab.

Let’s say you have a list of Employee Names containing the First and Last Name. And the list goes on for a couple of hundred employees and you need to get the First Name of all the employees. Excel gives many options to get the First Name from the list such as Text to Columns, we get another option Flash Fill.

• Enter the First Name in the correspondence cell and make sure the pattern should match.
• Press Enter and go to the next cell and if Excel gives a preview, enter the TAB button or you can follow the below methods to trigger Flash Fill.
  • Use the shortcut key Ctrl + E an easier method to apply Flash Fill.
  • Within the Home Tab, under the Editing group, click on Flash Fill located in the Fill menu.
  • Alternatively, you can also go to the Data Tab and under the Data Tools click on Flash Fill.
• Once the event is triggered, Excel will analyze the pattern and return all First Names within a second or two.

Both AutoFill and Flash Fill features in Excel will help you save time and remove repetitive tasks. AutoFill can help you quickly generate a pattern of numbers, dates and even formulas whereas the Flash Fill will identify the pattern in the data and suggest the output which can be generated with a single click.