AutoFill Cell Based on Another Cell in Excel (5 Easy Methods)

Method 1 – Autofill to End-of-Data

We have a set of names in the Name column. We need to assign an ID serially.

Steps:

Select cell C5.
Write down the first ID in that cell. We wrote 1.

Write down the first employee id manually to autofill the cell based on another cell

Double-click the Fill Handle icon to fill in all the relevant cells in a column.

Click on the Auto Fill Options icon on the bottom-right and choose the Fill Series option.

Excel will autofill the cell automatically.

Method 2 – Using the CONCATENATE Function

We have an employee name dataset in separate columns, and we are going to fill the cell of column D.

Steps:

Select cell D5.
Use the following formula in the cell.

=CONCATENATE(B5,C5)

Press Enter.

Double-click on the Fill Handle icon to copy and repeat the formula up to cell D18.

The formula will autofill the cells of column D.

Method 3 – AutoFill Blank Cells Based on Another Cell

We have the “Name” and “Subject” information of some students. Most of them are from the “Science Faculty” (indicated by “YES”). Those who are not from the “Science Faculty” are given blank cells. We are going to auto-fill those cells.

Steps:

Select the range of cells B5:D19.
In the Home tab, click on Find & Select and choose the Go To Special option from the Editing group.

A small dialog box called Go To Special will appear.
Select the Blanks option and click OK.

All the blank cells will be selected.
Write down your text. We put Not Applicable.

Press Ctrl + Enter.
All the blank cells will fill with the desired text.

Read More: Fix: Excel Autofill Not Working

Method 4 – Utilizing the IF function

We have a dataset with the Names of some students, their “Subject”, “Faculty”, and “Availability” of these subjects. We’ll check if the Faculty is Science and apply text in the Availability column.