Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Repeat Formula Pattern in Excel (8 Easy ways)

In Excel, it’s very handy to repeat a formula pattern to calculate a large set of data. If you want to fill formulas in every cell it will become very tedious and time-consuming work. If you are looking for some easy ways to repeat formula patterns in Excel, then you are in the right place. This article will show 8 simple methods to repeat the formula pattern. Let’s get into the main article.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.


8 Easy Ways to Repeat Formula Pattern in Excel

In the following dataset, we have 8 columns and 9 rows. Here we have some empty cells where we will repeat formula patterns in various ways to fill up the cells easily. We will explain different ways by taking these empty cells as an example.

sample dataset to Repeat Formula Pattern in Excel

In-depth information on this technique is provided in this section. To enhance your ability to think critically and your familiarity with Excel, you should learn and use these. Although we use the Microsoft Office 365 version here, you are free to use any other version of your choice.


1. Use AutoFill Tool

Here, in the Date column, I have two dates with a gap of one week in the first two rows and the date format is mm-dd-yyyy. Suppose I want to fill up the other cells in this pattern of date with a gap of one week.

📌 Steps:

  • To do this, we have to select the first two cells of the Date column and after hovering the mouse around the end of the second cell a plus sign like below will appear.
  • You have to just drag it down.

autofill tool to Repeat Formula Pattern in Excel

  • In this way, the rest of the cells will be filled up by using this pattern of dates.

 Repeat Formula Pattern in Excel

Read More: How to Repeat Number Pattern in Excel (5 Methods)


2. Repeat Formula with Flash Fill Feature

Suppose we have to add the First Name and the Last Name in the Full Name column. So, we have written the first name and last name of a person in the first row of the Full Name column.

Flash Fill feature to Repeat Formula Pattern in Excel

📌 Steps:

  • Then we will start typing in the second cell as below and after that, the following suggestions will appear.
  • This is called the Flash Fill feature of Excel.
  • After that, you have to press ENTER.

  • Then like the following figure, the names will be automatically filled using the given pattern.

Repeat Formula Pattern in Excel

Read More: Repeat Text in Excel Automatically (5 Easiest Ways)


3. Drag or Double Click to Get Formula Pattern

Here, we will demonstrate how to get a formula pattern by dragging and double-clicking. Floor the following process.

📌 Steps:

  • Here, we have typed a formula in E5 and we want to use this formula in other empty cells with their respective data.

=C5*D5

double clicking to Repeat Formula Pattern in Excel

  • To do this we have to just select E5 and drag down the Plus sign all the way over the empty cells. You can also do this by double-clicking on the Plus sign.

  • In this way, the following table will be formed.

dragging to Repeat Formula Pattern in Excel


4. Utilize Copy and Paste Feature

Here, we will utilize the copy and paste feature to get a repeat formula pattern in Excel.

📌 Steps:

  • Here, we have typed a formula in E5 and we want to use this formula in other empty cells with their respective values.

=C5*D5

copy and paste feature to Repeat Formula Pattern in Excel

  • To do this we have to select E5 and press CTRL+C and then select the empty cells and press CTRL+V.

  • In this way, the other empty cells will be filled up with the formula pattern as below.

 Repeat Formula Pattern in Excel


Similar Readings


5. Copy Formula Pattern Through Excel Power Query

In this section, we will illustrate how to use a power query to get a repeat formula pattern in Excel.

📌 Steps:

  • Here, we want to complete the Total Income column by typing the formula only once.
  • To do this you have to select as following Data Tab >> From Table/Range.

using power query to Repeat Formula Pattern in Excel

  • Then Create Table dialog Box will appear.
  • Then you have to select the whole range and click on the My table has headers option and press OK.

  • Then Power Query Editor will appear and then you have to just type the formula in E4 as below and press ENTER.

  • In this way, automatically the formula pattern will be repeated in all of the empty cells.

finally Repeat Formula Pattern in Excel

Read More: How to Repeat Formula in Excel for Whole Column (5 Easy Ways)


6. Input Formula to Multiple Cells

This method is the simplest and most effective way to input formulas into multiple cells. Let’s walk through the following steps to do the task.

📌 Steps:

  • At first, you have to select all of the cells where you want to use the formula.
  • Then start typing the formula in any of the cells and then press CTRL+ENTER.

  • After that, the remaining cells will be filled up with the formula.

enter formula to Repeat Formula Pattern in Excel

Read More: How to Repeat Multiple Rows in Excel (4 Effective Ways)


7. Apply INDIRECT Function to Repeat Formula Pattern

Suppose you have the following dataset where you have a column named Working hour and another column named Income per hour where only the first 3 cells have value. You have to multiply the first 3 cells of the Working hour with the first 3 cells of Income per hour respectively. You have to continue the process repeatedly, which means multiplying C7, C8, and C9  of  Working hours with the first 3 cells of Income per hour respectively. And this repetition will be continued. Here, we will use the INDIRECT function.

apply indirect function to Repeat Formula Pattern in Excel

📌 Steps:

  • First, you have to write the following formulas in E5, E6, and E7 respectively. Here the INDIRECT function has been used.

=C5*INDIRECT("D5",TRUE)

=C6*INDIRECT("D6",TRUE)

=C7*INDIRECT("D7",TRUE)

  • Now, the rest of the empty cells will be filled with this repeated formula pattern.

output of indirect function


8. Insert Excel SEQUENCE Function

In this method, we will use the SEQUENCE function to get a repeat formula pattern in Excel. Let’s follow the following process.

📌 Steps:

  • In the ID column, we want to fill up the cells with IDs by using the SEQUENCE function.

=SEQUENCE(8,1,121001,2)

insert sequence function to Repeat Formula Pattern in Excel

NOTE: Here, rows=8, columns=1, start=121001, step=2.
  • After entering the function the following table will appear.


Conclusion

In this article, I tried to cover the easiest ways of repeating formula patterns in Excel. Hope this article will help you a lot. If you have any further ideas related to this topic then you can share them with us.  Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

2 Comments
  1. I’ve been looking for those SEQUENCE and INDIRECT operations for years thinking they had to exist but not knowing what they were and how to find them. I appreciate you making this page!

    • Hello Dan,
      Thank you so much for your appreciation. Hope you will be benefitted more by staying with our Exceldemy site.

Leave a reply

ExcelDemy
Logo