AutoFill and Flash Fill
AutoFill and Flash Fill are the most useful features in Excel which can save you a lot of time and reduce repetitive tasks such as creating a sequence of dates, weekdays, numbers etc.
Table of Contents
AutoFill in Excel
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.
Uses of AutoFill in Excel
- 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.
What is Fill Handle which is mostly useful to get our work done?
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.
Examples of AutoFill Series
1. Get the sequential numbers
- Start by entering in your first cell the number you want the series to begin. For example, let’s enter 1 in cell A2.
- Once you enter the required number select the cell containing the value and drag the Fill Handle to the bottom right to get the series from 1 to 10. If you need the data in column-wise, you can drag it to the right side as well.
- The Excel will fill the range by copying the same value instead of filling the series. To get a series from 1 to 10, simply click on the options and select Fill Series.
2. Get the Date Series
- Enter the Date you want in cell A2 and drag it as per your requirement to fill the series of dates in the range.
3. Get Alternative Date Series
- 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.
Note: You can enter any alternate number and date and Excel’s AutoFill feature will analyze the difference and fill it in for you.
4. Days, Months and Quarters (multiple AutoFill in one click)
- 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.
We learnt how to use AutoFill in ascending order, let us go through a few examples in Descending Order of Numbers and Dates. Even if there are any blank columns in between, Excel’s AutoFill feature still works as per your requirement.
How to Quickly and Easily copy a Formula for other cells with help of AutoFill?
- 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.
Flash Fill in Excel
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.
How to Use Flash Fill in Excel?
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.
Conclusion
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.