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

In the following dataset, we have 8 columns and 9 rows. We have some empty cells where we will repeat formula patterns in various ways to fill up the cells easily.

Method 1 – Use the AutoFill Tool

In the Date column, we have two dates with a gap of one week in the first two rows, and the date format is mm-dd-yyyy. Let’s fill up the other cells in this pattern.

Steps:

• Select the first two cells of the Date column
• Hovering on the bottom-right corner of the selection, and a plus sign will appear.
• Drag it down.

• The rest of the cells will be filled up.

Read More: How to Perform Predictive AutoFill in Excel

Method 2 – Repeat a Formula with the Flash Fill Feature

Suppose we have to add the First Name and the Last Name in the Full Name column.

Steps:

• Write the first name and last name of a person in the first row of the Full Name column.
• Start typing in the second cell until you get a suggestion to fill in the cell. This is the Flash Fill.
• Press Enter.

• The names will be automatically filled using the given pattern.

Method 3 – Drag or Double Click to Get a Formula Pattern

Steps:

• Use the following formula in E5.

• Go to the bottom-right corner of E5 and drag down the Plus sign over the empty cells. You can also do this by double-clicking on the Plus sign.

• This will AutoFill the cells.

Read more: How to Repeat Number Pattern in Excel

Method 4 – Use Copy and Paste

Steps:

• We have used a formula in E5.

• Select E5 and press Ctrl + C.
• Select the empty cells and press Ctrl + V.

• The other empty cells will be filled up with the autofill formula pattern as below.

Method 5 – Copy the Formula Pattern Through Excel Power Query

We want to complete the Total Income column by typing the formula only once.

Steps:

• Select the Data Tab and choose From Table/Range.

• The Create Table dialog box will appear. Select the whole range, then click on the My table has headers option.
• Press OK.

• The Power Query Editor will appear.
• Input the formula in E4 as below and press Enter.

• The formula will be repeated in all of the empty cells.

Read More: How to Fill Down Blanks in Excel 

Method 6 – Input Formula to Multiple Cells

Steps:

• Select all of the cells where you want to use the formula.
• Insert the formula in any of the cells and press Ctrl + Enter.

• The remaining cells will be filled up with the formula.

Method 7 – Apply the 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. We will multiply the first 3 cells of the Working hour with the first 3 cells of Income per hour, respectively, Then, we will continue the process repeatedly, which means multiplying C7, C8, and C9 of Working hours with the first 3 cells of Income per hour again.

Steps:

• Insert the following formulas in E5, E6, and E7, respectively.

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

Method 8 – Insert the SEQUENCE Function

Steps:

• In the ID column, use the following formula.

• After entering the function, the following table will appear.

Read More: How to Add Sequence Number by Group in Excel

Download the Practice Workbook

Related Articles

• Applications of Excel Fill Series
• How to AutoFill Months in Excel
• How to Create Automatic Rolling Months in Excel
• How to Increment Month by 1 in Excel
• How to Enter Sequential Dates Across Multiple Sheets in Excel
• How to Autofill Days of Week Based on Date in Excel

<< Go Back to Excel Autofill | Learn Excel