In Microsoft Excel app, you may have come across its Flash Fill or AutoFill features. These two features help reduce the time it takes to complete data entry tasks in a spreadsheet. However, don’t mistaken for each other and try to use them interchangeably.
Both Fill features can sense and repeat data patterns. However, where Flash Fill can sense, repeat and make adjustments to a data pattern according to prior entries by the user, AutoFill cannot. It only senses and repeats patterns. Therefore, the more complex your data pattern, the less likely you can use AutoFill. Here’s how to use both in Excel.
How to Use AutoFill in Microsoft Excel
Microsoft Excel’s Auto Fill feature is suitable to fill cells with data that follows a simple pattern or sequence. You can also use it to fill cells that rely on data in other populated cells. For example, if you have numbers (1,2,3) or days of the week (Monday, Tuesday) in a cell, Auto Fill can help populate the next occurrence in the series.
At the same time, if you just want to repeat the same data across multiple cells, AutoFill can help with that. Here are the different ways you can use AutoFill in Microsoft Excel:
To Auto-Populate Cells With the Same Data
If you want the same data replicated across cells in your Excel worksheet, instead of copying and pasting, AutoFill is a faster option. Here’s how to do so:
Step 1: Click the Start button on the Taskbar and type excel to search for the app.
Step 2: From the results, click on the Microsoft Excel app or document to open it.
Step 3: In the Microsoft Excel worksheet, enter a sample of the data you want to replicate across cells. In this case, we are using days of the week as an example and have entered Monday into a cell.
Step 4: Place your cursor on the cell with the data entry and click it for a border to show around the cell.
Step 5: Move your cursor to the bottom right side of the cell unill you see a + sign representing the fill handle.
Step 6: Drag the fill handle to the next cell, and Excel will populate it. Note that when working with data with a known pattern e.g., days of the week, when using AutoFill, Excel will populate the cells with the next item in the series. If this happens trying to just replicate data, move on to Step 7.
Step 7: After using the fill handle, you will see the Auto Fill Options icon pop-up. Click the icon.
Step 8: From the Auto Fill Options shown, select Copy Cells. This ensures that as you continue using the fill handle across cells, your data gets copied and not filled as a series.
Step 9: Drag the fill handle to the next cell to continue copying your data.
To Fill a Series
If you have a simple series or sequence you want to populate in cells, AutoFill can perform the task for you. Here’s how:
Step 1: Click the Start button on the Taskbar and type excel to search for the app.
Step 2: From the results, click on the Microsoft Excel app or document to open it.
Step 3: In the Microsoft Excel worksheet, enter a sample of the data you want to replicate across cells. In this case, we are using days of the week as an example and have entered Monday into a cell.
Step 4: Place your cursor on the cell with the data entry and click it for a border to show around it.
Step 5: Move your cursor to the bottom right side of the cell until you see a + sign representing the fill handle.
Step 6: Drag the fill handle to the next cell, and Excel will populate it.
Step 7: To see other fill options, click the Auto Fill Options icon.
Step 8: Select the type of fill you want from:
- Fill Series
- Fill Formatting Only
- Fill Without Formatting
- Fill Days
- Fill Weekdays
Note that Fill Days and Fill Weekdays will only show up if your initial data entry has to do with the days of the week. If your initial entry had to do with days of the month, you would have seen the Fill Months option.
How to Use Flash Fill in Microsoft Excel
Excel’s Flash Fill feature is suitable to fill cells with data that follows a complex pattern or sequence. For example, if you are trying to split data into various columns or combine data across multiple columns into one. As long as you have a sample of what you want and consistency in the data, Flash Fill can help you replicate the information across multiple cells.
Here are the different ways you can use FlashFill in Microsoft Excel:
To Split Data Into Columns
If you are trying to split numbers, dates, or any form of text into different columns, here’s how to do so:
Step 1: Click the Start button on the Taskbar and type excel to search for the app.
Step 2: From the results, click on the Microsoft Excel app or document to open it.
Step 3: In the Microsoft Excel worksheet, ensure you have the data to split in one column. Also, create another or multiple columns for the data you want to split. In this case, we are using dates as an example and are trying to split them into the days, month, and year columns respectively.
Step 4: Enter an example in the next columns where you want to split the data. That is, how you want the data split.
Step 5: Click a column containing one of your new sample datasets.
Step 6: On the Excel Ribbon, click the Data tab.
Step 7: Within the Data Tools group, click Flash Fill, and Excel will replicate the same pattern for the other rows. Alternatively, you can click Ctrl + E keyboard shortcut to use Flash Fill.
Step 8: Repeat this for the other columns you have till all columns are populated.
To Combine Data In One Column
If you are trying to combine numbers, dates, or any form of text into one column, here’s how to do so with Flash Fill:
Step 1: Click the Start button on the Taskbar and type excel to search for the app.
Step 2: From the results, click on the Microsoft Excel app or document to open it.
Step 3: In the Microsoft Excel worksheet, ensure you have the columns with the data you want to combine. Also, create another column to house the new data combination. In this case, we are using names as an example and are trying to have the First Name and Surname feature in one Name column.
Step 4: In the column where you want to combine the data, enter an example. That is, how you want the data combined.
Step 5: Click the Ctrl + E keyboard shortcut or click the Flash Fill button in the Data tab of the Excel Ribbon to flash fill for the other cells.
Using AutoCorrect and AutoComplete in Excel
Two other features in Excel like Flash Fill and AutoFill that get mistaken for each other are AutoCorrect and AutoComplete. These two features are different as they serve varying purposes. However, they both help save time with data entry.
Categories: How to
Source: thpttranhungdao.edu.vn/en/
