If the Pattern of Blocks Continues Which Formula

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 the easiest ways to repeat formula patterns in Excel then you are in the right place. Let's get into the main article.

Download Excel Workbook

8 Ways to Repeat Formula Pattern in Excel

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

Dataset of repeat formula pattern in Excel

Method-1: Using Autofill

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.

autofill

Step-01 : To do this, I 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

Step-02 : 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)

Method-2: Using Flash Fill Feature to Repeat Pattern

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

flash feature

Step-01 : Then I 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.

flashfill

Step-02 : 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)

Method-3: Repeating a Formula by Dragging and Double Clicking

Step-01 : Here, I have typed a formula in E4 and I want to use this formula in other empty cells with their respective data. To do this I have to just select E4 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.

Drag+double click

Step-02 : In this way, the following table will be formed.

repeat formula pattern in Excel

Method-4: Copying and Pasting a Formula to Repeat Pattern

Step-01 : Here, I have typed a formula in E4 and I want to use this formula in other empty cells with their respective values. To do this I have to select E4 and press CTRL+C and then select the empty cells and press CTRL+V

copy and paste a formula pattern

Step-02 : In this way, the other empty cells will be filled up with the formula pattern as below.

repeat formula pattern in Excel


Similar Readings

  • How to Repeat Rows at Top in Excel (3 Suitable Ways)
  • Repeat Cell Values in Excel (6 Quick Methods)
  • How to Repeat Rows in Excel When Printing (3 Effective Ways)
  • Find Repeated Numbers in Excel (5 Easy Methods)
  • How to Repeat Formula in Every nth Row in Excel (2 Easy Ways)

Method-5: Using Power Query to Repeat Pattern

Step-01 : Here, I 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

creating table

Step-02 : 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.

create table dialog box

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

Power Query editor

Step-04 : In this way automatically the formula pattern will be repeated in all of the empty cells.

repeat formula pattern in Excel

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

Method-6: Entering a Formula to Multiple Cells

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

entering formula to multiple cells

Step-02 : After that, the remaining cells will be filled up with the formula.

repeat formula pattern in Excel

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

Method-7: Repeating Formula Pattern Using INDIRECT Function

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, C9  of Working hour with the first 3 cells of Income per hour respectively.
And this repetition will be continued .

repeating pattern

Step-01 : At first you have to write the formulas in E4, E5, E6 respectively. Here INDIRECT Function has been used.

= C4 *INDIRECT(" D4 ",TRUE)

= C5 *INDIRECT(" D5 ",TRUE)

= C6 *INDIRECT(" D6 ",TRUE)

After entering the functions the first 3 cells of Total Income will give values and then you have to just select the first 3 cells and drag the Plus sign down as below.

Indirect function

Step-02 : Now, the rest of the empty cells will be filled with this repeated formula pattern.

repeat formula pattern in Excel

Method-8: Using SEQUENCE Function to Repeat Pattern

Step-01 : In the ID column I want to fill up the cells with IDs by using the SEQUENCE function.

=SEQUENCE(rows,columns,start,step)

Here, rows=8, columns=1, start=121001, step=2

=SEQUENCE(8,1,121001,2)

SEQUENCE function

Step-02 : After entering the function the following table will appear.

repeat formula pattern in Excel

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. You can ask any question here. Thank you.


Further Readings

  • How to Count Repeated Words in Excel (11 Methods)
  • Repeat Rows a Specified Number of Times in Excel (4 Easy Ways)
  • How to Find Repeated Cells in Excel (4 Easy Ways)
  • [Fixed] Repeat Last Action Not Working in Excel (6 Solutions)
  • How to Repeat Rows in Excel at Bottom (5 Easy Ways)

mantopower1.blogspot.com

Source: https://www.exceldemy.com/repeat-formula-pattern-in-excel/

Komentar

More Articles

164.68 L27 15 : Kumpulan 111.90.l50.204 Japanese 164.68.l27.15 111.90150 ... - Anda akan melihan berbagai macam fitur di sajikan oleh situs tersebut, dan bukan hanya satu atau dua yang di sajikan pada halan situs.

On a Table if I Create a Line Does Will It Continue a Forumula

حاسبة جميلة - الة حاسبة جميلة وأنيقة : كلام جميل فيس بوك , وصور بوستات فيس بوك جميلة.

111.90 L.150.204 Viral / 111 90 L 150 204 Viral Variante Virus Covid Variante Virus Puo Essere Anche Apa Bila Anda Akan Melihat Sebuah Kumpulan Video Nada Tentu Bisa Menggunakan 111 90 150 204 Nonton Karena Banyak

Andai Kakak Ku Tidak Menggodaku Waktu Itu : Andai Saja Kakak Tidak Menggodaku : Jika kalian penasaran dengan apa yang terjadi pada hal yang viral ini, maka kalian harus simak pembahasan yang akan admin bagikan sampai akhir ya sob.

Локомотив Крылья Советов : Локомотив — Крылья Советов. Прогноз и ставка за 1.75 / 02 ... - Результаты матча, счет 3:1, обзор по минутам, видео.

Jack Grealish - Villa Star Jack Grealish Reveals He 'Almost Died' From ... - Jack grealish (born 10 september 1995) is a professional footballer who plays for premier league club aston villa as a midfielder.

How To Make Home Made Pork Rinds : How to Make Chicharrones (Fried Pork Skin) at Home | Fried ... : How to make pork skins.

New Cabinet Ministers - How many of the new Cabinet Ministers can you name?

Gambar Sketsa Tatto : Inspirasi Tato Sketsa Tato Joker Keren / 20 gambar sketsa kumpulan gambar sketsa bunga sketsa tato, polres karawang membuat sketsa tato simple sumber.




banner