How to AutoFill Cell Based on Another Cell in Excel (5 Methods)

final result

We often need to copy and paste our data in Excel but it can get irritating when you are dealing with a large database. In this situation Excel auto-fill features are the best solution to save time and work efficiently. You can do many things using this feature that you were probably doing manually. Today in this article we will discuss some of the methods of auto-filling cells based on another cell in Excel.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Auto Fill Cell Based on Another Cell (5 Methods)

In this section, we will show you five of the most effective ways to auto-fill cells.

1. Autofill to End-of-Data in Excel

Step-1:

In the following example, we have a set of names in the “Name” column. We need to put their “ID” serially.

creating table

Step-2:

Now we can manually insert their “ID” or we can do it by dragging the mouse cursor. But when you are working with 3000 or 40000 data, it can take all day. So to do it smartly, hold down the “SHIFT” key on the keyboard. Now when you take the mouse to the corner of the cell you will notice this cursor icon with two horizontal parallel lines icon

creating table

Step-3:

Now just double click the icon and the auto-fill data serially will be done by excel. Data will be inserted only down to where the adjacent cell column has data.

aauto-fill data

And our auto-filling the cells are done.

Read more: How to Fill Down to Last Row with Data in Excel

2. Using the CONCATENATE Function to Autofill Cell

Step-1:

Consider a new example where we have the “First Name” and “Last Name” of some candidate. Now we need to auto-fill the “Full Name” column based on those two other columns.

creating table

Step-2:

Now we will use the “CONCATENATE” function to auto-fill those full names.

Select cell “D4” and apply the “CONCATENATE” function.

The final formula is,

=CONCATENATE(B4,C4)

Where,

  • Text 1 is B4
  • Text 2 is C4

applying formula

Press “Enter” to apply the function. And the auto-filled result is here based on those two columns.

getting result

Step-3:

Now simply click and hold your mouse on the lower right corner of the formula cell and drag it down to apply the same formula.

final result

Read more: How to Use Autofill Formula in Excel

3. Auto Fill the Blank Cells Based on Another Cell

Consider a situation where you have to fill up blank cells in your datasheet. This task can be really tiresome when the database is large. But you can auto-fill those blanks easily. Follow these steps to learn.

Step-1:

In the following example, we have given the “Name” and “Subject” of some students. Most of them are from the “Science Faculty” and are given “YES”. Those who are not from the “Science Faculty” are given blank cells. We need to auto-fill those cells with appropriate words.

creating table

Step-2:

Select the data table, go to the “Home” menu, then click on the “FIND & SELECT” option and select “Go to Special”.

Select Data Table →Home → Find & Select → Go to Special

creating table

A new window popped. Select “Blanks” and click “OK”

creating table

Step-3:

Now we have selected all our Blank cells.

auto fill data

In one of the blank cells, insert the text “NOT APPLICABLE”.

final result

In the final step, press “CTRL+ENTER” to apply the same for the rest of the cells.

That’s how our blank cells are auto-filled.

Read more: How to Auto Populate Cells in Excel Based on Another Cell

4. Using the IF function to Autofill Cell

The “IF” function is also useful when we need to auto-fill cells based on other cells.

Step-1:

In the following case, the “Name” of some students, their “Subject”, “Faculty”, and “Availability” of these subjects are given. We are told that only science-related subjects are available. So we have to autofill those cells with appropriate texts.

creating table

Step-2:

In the cell “E4” apply the “IF” function. The formula is

=IF(D4=”Science”,”YES”,”N/A”)

Where,

  • Logical_test is D4=”Science”
  • [Value_if_True] is “YES”
  • [value_if_false] is “N/A”

applying formula

Press “Enter” to continue. So “Science” related subject will get the “YES” and others will get “N/A”.

getting result

Step-3:

Select and drag the mouse cursor to apply the same for the rest of the cells.

final result

5. Using the VLOOKUP function to Autofill Cell

Step-1:

Suppose we have some random serial number of some candidates. We need to arrange them serially. Now make another table with serial numbers. We will auto-fill the “Name” columns according to those serials using the “VLOOKUP” function.

creating table

Step-2:

In the cell “F4” apply the VLOOKUP function. Insert the values. The final formula is

=VLOOKUP(E4,$B$4:$C$18,2,FALSE)

Where,

  • Lookup_value is E4
  • Table_Array is $B$4:$C$18
  • Col_index_num is 2
  • We want the EXACT value (FALSE)

creating table

Press “Enter”. The name with respect to the serial number is here

getting result

Step-3:

Now apply the same formula to all cells. We will then have our final result.

final result

Things to Remember

➤ In auto-fill to end-of-data, first, fill up 2 or 3 numbers manually to auto-fill in series. If you insert only one data, the Excel then won’t understand the series.

➤When you select your “Table_Array” you have to use the absolute cell references ($) to block the array.

Conclusion

Auto-fill cell based on another cell is really useful and effective. It saves a lot amount of time. We discussed five effective ways to auto-fill your cells. If you have any confusion or thoughts, let us know in the comment section.


Further Readings

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo