Nothing is more mundane and uninspiring than filling in Excel cells one by one. Fortunately, you don’t have to copy data from one spreadsheet to another for the rest of your life. You can use Excel autocomplete to save some time and do your job more efficiently.
How does this function work in Excel? Read on to find out.
Autofill cells in Excel
Using Excel’s auto-complete feature can be as simple as dragging your cursor. All you need to do is fill in the first or two cells and the program will identify what the rest of the cells should contain.
Follow these steps to automatically fill cells with the same information.
- Fill in the first cell.
- Move your cursor over the lower right corner of the cell to see it change to a “+” sign.
- Grab the corner of the cell and drag it out to fill in the rest of the cells.
The process is similar if you want to fill cells with a series instead of the same value.
- Fill in the first two cells of your sequence.
- Select the cells.
- Click and hold the bottom right corner of the selection and start dragging.
- Excel will infer the pattern based on the cells you filled in and continue the sequence.
If the program did not identify the correct pattern, adjust the autocomplete by clicking the autocomplete menu that appears when the process is complete. Select the appropriate option and Excel will adjust the order accordingly.
Excel autocomplete cell based on another sheet
Want to copy data from one excel sheet to another? Forget copying and pasting each cell and manually updating your spreadsheet every time the original document changes. Here’s how to autofill cells based on another sheet.
- Create a new sheet for your database.
- Place your cursor in one of the cells and type “=”, followed by the name of the sheet, an exclamation mark, and the cell number you want to refer to. For example, a simple formula would look like this: “=Sheet1!A1”
- Press “Enter” and the cell will display the information contained in the cell you just referenced.
- Grab the bottom right corner of the cell and drag the selection to import data from subsequent rows and columns.
While this is a great way to import data from another sheet, sometimes you need to filter or organize the data instead of just copying it. By tweaking your formula a bit, you can filter data from another sheet in Office 365 and import only relevant bits of information. Follow these steps to get started.
- Create a new sheet.
- Click in a cell and type the formula “=FILTER(“.
- Switch to the sheet you want to reference and select your entire table without the headings.
- Type a comma in the formula, then select the column by which you want to filter your data. Be sure to exclude the head again.
- Type “=” and define the criterion for your filter. If it’s a number, just write the number down. If the column contains text, use quotation marks in your formula. For example: “=FILTER(Sheet1!A1:C3,Sheet1!A1:A3=1)”. Each row marked with number 1 in column “A” will be imported into the new sheet.
- Close the parenthesis and press “Enter”.
Excel Autofill cell based on list
Do you have an excel sheet with a long list of data you need to collect in a specific format? Do not worry. You don’t have to manually retype the bits of information. Excel’s “Flash Fill” feature takes auto-fill to the next level and will make your work easier. Here’s how to autocomplete cells based on a pre-existing list.
- Fill in the first cell as you wish, using the information from your list.
- Press “Enter” and start typing the next item.
- Excel fills in the rest of the cells following the same pattern. Press “Enter” to accept the suggestion and your list is complete.
Excel Autofill cells based on dropdown selection
Creating a drop-down list in Excel that allows you to populate cells with the information you select can save you a huge amount of time. Let’s see how to autofill cells based on dropdown selection.
First, you need to create a separate sheet for the information your drop-down will contain.
- Open your Excel document and add a new sheet.
- Paste the database you want to include or create one from scratch on the blank sheet.
Then convert your spreadsheet to a table. This is important because it is easy to edit or extend it this way. It also ensures that the drop-down list you are about to create is automatically updated every time you change the data.
- Select one of the cells in your spreadsheet.
- Press Ctrl and “T” and press “OK” to create a table.
Now that your database is ready, it’s time to create the drop-down list.
- Select the cell where you want to place your drop-down list.
- Go to the “Data” tab at the top.
- Find the “Data Tools” section.
- Click the “Data Validation” icon and select this option from the drop-down menu.
- Select the “List” option under “Allow”.
- Click on the “Source” field and go to your database worksheet.
- Select the column containing the items you want to appear in your drop-down list.
- Click “OK” in the Data Validation window.
Now enter the rest of the data you want to display when you select an item from your list.
- Select an empty cell for your first piece of data.
- Click on ‘Formula’, then on ‘Lookup & Reference’ and select ‘VLOOKUP’.
- Fill in the fields in the following way and press “OK”.
- The “Lookup_value” should be your dropdown cell.
- For “Table_array”, select your entire database.
- At ‘Col_index_num’, type the number of the column that contains the information you want to display.
- Make sure “Range_lookup” is “false”.
You have just created a reference to one column. Copy and paste the formula into subsequent cells to display the data in the rest of the columns. In each instance, replace the index number with the number of the column you want to reference.
Each time you select an item in your drop-down list, the cells you equipped with the formula are automatically populated with the information about that item.
Get the most out of Excel
Learning how to autofill Excel cells can save you a significant amount of the labor it takes to invest in your spreadsheets. Follow the instructions above to take advantage of these advanced Excel features and save yourself from tedious tasks.
Have you used Excel’s autocomplete option before? What are some of your favorite ways to take advantage of this feature? Let us know in the comments below.