Auto-Populate Data in Excel Based on Another Cell

If you are manually populating your Excel sheet with data – you’re probably not in line with modern data entry techniques.

Microsoft Excel offers various features to users to auto-populate their Excel sheets. You can do so by using the built-in Excel tools and functions.

Let’s walk down to the end of this article to learn all these ways that you can employ to automate data population in Microsoft Excel. And as you continue reading, don’t forget to grab your free practice workbook here 📚

Using the Auto-Fill feature

The Auto-Fill feature of Excel is a very handy and time-saving tool.

It senses a simple pattern or formula based on the data you provide and fills the same into the remaining cells in a snap. It is most commonly used to fill cells with numeric data, dates, months, years, etc.

For example, I have entered a loan agreement where I am supposed to make a periodic payment against the loan for $5000 for the coming 10 years 🏦

I made the first payment on 20 February 2024.

First date defined in Excel

Since every payment is made after the one-year interval, I will populate the next date on which the payment is due as 20 February 2025.

Second date defined in Excel

After we have supplied two dates, Excel senses a pattern that each date falls after a yearlong interval.

Don’t go on populating the remaining dates all by yourself. To auto-populate the remaining dates:

Step 1) Move your cursor towards the bottom right of the last cell of the defined pattern.

Step 2) You’d spot a small black plus “+” icon (what we call the Fill Handle).

Fill Handle Excel spreadsheet

Step 3) Drag it down across the list of cells that you want populated with dates.

Double-click Fill handle

Initially, Excel might just auto-fill the column with dates with a day or month interval. If this happens:

Step 4) Spot the Auto Fill Options icon on the bottom right of this list.

Auto Fill Excel drop-down list

Step 5) Click on it to find the options to fill the next dates by the interval of a day, a month, or a year.

Step 6) As we want the dates arranged at the interval of a year, select the option “Fill Years”.

Auto Fill years

And Tada! Excel auto-populates the remaining list for you at the one-year interval 📆

Dates populated

The Auto-Fill feature is an excellent one that not only saves you time and effort but, is easy to use.

Using the Flash Fill feature

The Flash-fill feature is like the Auto-fill feature but much smarter than it.

The Auto-Fill feature only senses a simple pattern and populates cells based on the same. However, the Flash-fill feature senses patterns where users are attempting to adjust data and then continue the same 📝

Let’s see a quick example of how this works.

List of Names

I have the names of 10 people collated in the above list.

However, I want to segregate the first and the last names in separate columns. This can be easily done using the Flash-Fill feature – check this out.

Step 1) In the column next to this column, begin writing the first name as below.

Writing first name in first cell

Step 2) In the next row, repeat the same.

Repeating the action

Step 3) As soon as you activate the next cell to do this, Excel will identify a pattern and show you the greyed-out Flash-fill results.

Flash fill pattern

Step 4) Press Enter and Excel will populate the remaining list based on the same pattern.

Remaining list populated based on cell value

Step 5) Repeat all the above steps for the list of Last Name.

List of last names

Step 6) As soon as Excel identifies the pattern and shows the greyed-out list, press enter to have it auto-populated, too.

Last names auto-populated

That’s how the Flash-fill feature of Excel works to automatically identify and auto-populate cells for you based on the data in another cell 🧐

The list of names before us in this example was a short one but had it been a little longer, you’d have known what a blessing it is to have the flash-fill feature in Excel.

Using the IF Function

The IF function runs a logical test, and based on the results of that logical test, it returns a value_if_true if the test turns out true and value_if_false if the test turns out false.

Both these values are to be specified by us, and using the same criteria we can automate data population in Excel.

For example, I have some students and their grades populated in Excel.

Students and their grades in Excel

Whether a student passed the exam or failed it depends on this score. All students who scored 70 or more stand passed. Everyone else shall be deemed failed 👩‍🏫

Manually scanning down the list of scores to identify the students who passed or failed and then populating the pass/fail list is a “No”.

This is something we will tell Excel to populate for us using the IF function.

Step 1) Begin writing the IF function as follows:

Click to copy
IF formula in Excel

Step 2) Specify the logical test as “equal to or greater than 70”.

Click to copy
Excel formula for value greater than or equal to 70

Step 3) Define the value_if_true as “Pass”.

Click to copy
Value if true for column a and column b

Step 4) Define the value_if_false as “Fail”.

Click to copy

Excel will run the logical test on Cell B2 to see if the value of B2 is equal to or greater than 70.

If the logical test comes out true (value in B2 equals or exceeds 70), the IF function will return “Pass”.

And if the logical test turns out false (value in B2 is less than 70), the IF function will “Fail” 🎯

Step 5) Hit Enter to see the results for B2.

Results of the IF function

Rightly returned – Jason passed the exam as he scored 82 (above 70).

This is a basic example of using the IF function to auto-populate cells in Excel. You can take this a step ahead to specify multiple criteria by nesting in the AND function or the OR function. Additionally, you can also use the IFS function (that supports multiple logical tests) for the said cause.

Kasper Langmann, co-founder of Spreadsheeto

Step 6) Drag the formula down across the whole list of students to see the pass / fail status for them, too.

Pass/Fail for all students

There you go! Excel automatically calculated the result for all the students and auto-populated the list of Pass/Fail based on the criteria supplied 💪

Pro Tip!

There are many other functions that can be used to auto-populate cells in Excel. For example, you might use VLOOKUP function or XLOOKUP function to lookup values from the lookup table in a worksheet or workbook or even in another workbook and populate cells based on the same.


Done learning the methods that you can use to auto-populate data across various cells in Excel?

Define a pattern for Excel and let the autofill do the remaining job. Or use the IF function to tell Excel which value should be populated under which circumstance. All the methods discussed above have their utility and what suits you best, depends on your circumstances.

Learn more about how you can make the best of these features of Excel by reading the following Excel tutorials dedicated to them.