How to autofill cells in Excel

Nothing is as mundane and boring as filling Excel cells one by one. Fortunately, you don’t have to spend the rest of your life copying data from one spreadsheet to another. You can take advantage of Excel autocomplete to save time and complete your task more efficiently.

How does this feature work in Excel? Keep reading to find out.

How to autofill cells in Excel

Using Excel’s autocomplete feature can be as easy as dragging your cursor. All you need to do is fill in the first cell or two and the program will identify what the rest of the cells should contain.

To autofill cells with the same information, follow these steps.

1. Fill in the first cell.
   
2. Move the cursor over the bottom right corner of the cell to see it turn into a “+” sign.
   
3. 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 sequence instead of the same value.

1. Fill in the first two cells of your sequence.
   
2. Select the cells.
   
3. Click and hold the bottom right corner of the selection and start dragging it.
   
4. Excel will deduce the pattern based on the cells you filled out and continue the sequence.
   

If the program did not identify the correct pattern, adjust autocomplete by clicking the autocomplete menu that appears when the process completes. Select the correct option and Excel will adjust the sequence accordingly.

Excel autocomplete cell based on another sheet

Do you want to copy data from one Excel sheet to another? Forget about copying and pasting each cell and manually updating your spreadsheet every time the original document changes. Here’s how to autocomplete cells based on another sheet.

1. Create a new sheet for your database.
   
2. Place the cursor in one of the cells and type “=” followed by the sheet name, an exclamation point, and the cell number you want to reference. For example, a simple formula would be: “=Sheet1!A1 ”
   
3. Press “Enter” and the cell will display the information located in the cell you just referenced.
   
4. Grab the bottom right corner of the cell and drag the selection to import data from subsequent rows and columns.
   

Although this is a great way to import data from another sheet, sometimes you may need to filter or organize the data instead of simply copying it. By changing the formula a little, you can filter data from another sheet in Office 365 and only import relevant information. Follow these steps to get started.

1. Create a new sheet.
   
2. Click on a cell and type the formula “=FILTER()”.
   
3. Switch to the sheet you want to reference and select the entire table without its headers.
   
4. Type a comma into the formula, then select the column you want to filter your data based on. Make sure to exclude the header again.
   
5. Type “=” and define the criteria for your filter. If it is a number, simply type the number. If the column contains text, use quotes in your formula. For example, “=FILTER(Sheet1!A1:C3,Sheet1!A1 :A3=1)”. Each row marked with the number 1 in column “A” will be imported to the new sheet.
   
6. Close the parenthesis and press “Enter.”
   

List-based Excel autocomplete cell

Do you have an Excel sheet with a long list of data that you need to compile into a specific format? Don’t worry. You will not have to re-enter information manually. Excel’s “Quick Fill” feature takes autocomplete to the next level and will make your job easier. Here’s how to autocomplete cells based on an already existing list.

1. Fill in the first cell any way you want, using the information from your list.
   
2. Press “Enter” and start typing the next entry.
3. Excel will fill in the rest of the cells following the same pattern. Press “Enter” to accept the suggestion and your list is complete.
   

Autofill Excel cells based on dropdown list selection

Creating a drop-down list in Excel that helps you fill cells with the information you select can save you a tremendous amount of time. Let’s see how to autocomplete cells based on dropdown list selection.

First, you’ll need to create a separate sheet for the information that your dropdown list will cover.

1. Open your Excel document and add a new sheet.
   
2. Paste the database you want to include or create one from scratch on the blank sheet.
   Next, convert your spreadsheet to a table. This is important because it will be easy to edit or expand this way. It also ensures that the dropdown you are about to create will automatically update every time you modify the data.
   
3. Select any of the cells in your spreadsheet.
   
4. Press Ctrl and “T” and press “OK” to create a table.
   

With your database ready, it’s time to create the dropdown list.

1. Select the cell where you want to place your dropdown list.
   
2. Switch to the “Data” tab at the top.
   
3. Find the “Data Tools” section.
   
4. Click the “Data Validation” icon and select this option from the drop-down menu.
   
5. Select the “List” option under “Allow.”
   
6. Click on the “Source” field and switch to your database worksheet.
   
7. Select the column with the items you want to appear in your drop-down list.
   
8. Click “OK” in the Data Validation window.
   

Now, insert the rest of the data that you want to appear when selecting an item from your list.

1. Select an empty cell for your first data.
   
2. Click “Formula,” then “Search and Reference” and select “VLOOKUP.”
   
3. Complete the fields as follows and then press “OK.”
   • The “lookup_value” should be the cell of your dropdown list.
   • For “Table_array”, select your entire database.
   • For “Col_index_num,” type the number of the column where the information you want to appear is located.
   • Make sure “Range_lookup” is “false”.

You have just created a reference to a column. Copy and paste the formula into the following cells to display the data in the rest of the columns. In each copy, 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 will automatically populate with information about the item in question.

Get the most out of Excel

Learning how to autofill Excel cells can significantly reduce the labor you’ll need to invest in your spreadsheets. Follow the instructions above to take advantage of these advanced Excel features and avoid monotonous tasks.