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.

Use Auto Fill Options to autofill cell

Excel will autofill the cell automatically.

Autofill Cell Based on Another Cell up to End-of-Data

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.

Using CONCATENATE function

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

Using Fill Handle tool to copy formula

The formula will autofill the cells of column D.

Using CONCATENATE function to autofill cell based on another cell

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.

Lunching GoTo Special dialog box to autofill cell

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

Choosing suitable option to autofill cell based on another cell

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.

AutoFill the Blank Cells Based on Another Cell

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.