Flash fill and Autofill are all Microsoft Excel provide tools that can help excel users fill data automatically. If you master how to use them, it can save you a lot of time. But there are a lot of differences between them. In this article, I will tell you how to use excel flash fill and autofill to fill data automatically and what is the difference between them.
1. How To Use Autofill To Fill Data In Excel Example.
1.1 Autofill Numbers In Excel Cells Example.
- Open an excel workbook, input number 1 in the first excel cell A1 in Sheet1.
- Select excel cell A1, and drag the small square on the bottom right corner of excel cell A1.
- Your mouse pointer will be changed to a black cross icon, press and move the mouse pointer from A1 to the right side until cell F1.
- When you release the mouse pointer, there will show the Auto Fill Options square on the bottom right corner of the last excel cell.
- Click the Auto Fill Options square to show the options list. The options include Copy Cells, Fill Series, Fill Formating Only, and Fill without Formating.
- The Copy Cells option will fill all the selected excel cells with the first cell value.
- The Fill Series option will fill the series number (1, 2, 3, 4, 5, 6) in cells A1 to F1.
- The Fill Formating Only option will just format the excel cells using cell A1‘s format.
- The Fill Without Formating option will fill the later cells using the first cell A1‘s value only.
- If you input numbers 1, and 3 in cells A1, and B1 and do the above operations, then Excel will input numbers 1,3,5,7,9,11 in Excel cells A1 to F1.
- If you input number 10 in cell F1, and do the above actions from right to left, then you will get the number series 5,6,7,8,9,10 in cells A1, B1, C1, D1, E1, and F1.
- So the excel autofill feature can guess your inputted number pattern and then input the related numbers in the selected cells.
1.2 Autofill Dates In Excel Cells Example.
- Besides autofill numbers, excel can also autofill dates in cells.
- Input date value 1-Jan-22 in excel cell A1, select cell A1 and press and move the black cross icon on cell A1‘s bottom right corner to cell F1.
- Click the Auto Fill Options square on the bottom right corner of excel cell F1, you can see the autofill options.
- Besides the autofill options in section 1.1, there are some other options Fill Days, Fill Weeks, Fill Months, and Fill Years.
- You can click the above autofill options to see the changes, it will automatically fill the date value by changing the day, week, month, and year value.
2. How To Use Flash Fill To Fill Data In Excel Example.
- Flash fill is similar to autofill, but it is more powerful than autofill.
- Flash fill can be used to batch combine other excel cells’ values automatically.
- Flash fill can also be used to extract other excel cells’ values using the first cell value pattern.
2.1 How To Use Flash Fill To Combine Excel Cells Values Example.
- There are 3 columns in this example, First Name (Jerry), Last Name (Zhao), and Mailbox domain (Gmail).
- Columna A is a list of the user’s first name, column B is a list of the user’s last name, and column C is a list of the user email’s domain.
- Now we want to combine columns A, B, and C‘s values to construct the user email address and input it in column D.
- First, input [email protected] in excel cell D1.
- Click the D title to select column D, then click the Data —> Flash Fill item in the Data Tools group.
- Then you will find excel will fill all the column D‘s cells with the value [email protected].
2.2 How To Use Flash Fill To Extract Excel Cell’s Value Example.
- In this example, column A contains a list of the user’s SSN numbers such as 296-25-6489.
- We want to extract the first 3 digits (296) and the last 3 digits (489) from column A and construct a new number (296—489) and input it into column B.
- Now we can use the excel Flash Fill tool to implement it.
- Input the text 296—489 in excel cell B1.
- Click column B‘s title to select column B.
- Click the Data —> Flash Fill item in the ribbon, then you will find all the cells in column B will contain the extracted digits from column A using the B1 cell’s format.