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 📚

Table of Contents

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.

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.

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).

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

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.

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”.

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

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.

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.

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

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.

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

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

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

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.

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:

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

Step 3) Define the value_if_true as “Pass”.

Step 4) Define the value_if_false as “Fail”.

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.

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

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

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.

Conclusion

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.

• How to Use Flash Fill and Autofill in Excel Step-By-Step
• Excel Guide: Clicking and Dragging the Fill Handle (2024)