How to Repeat a Number Pattern in Excel (5 Methods)

Suppose you have the following dataset:

Method 1 – Use a Keyboard Shortcut to Repeat a Number Pattern

Steps (Single Repeated Cell):

• Select the cell where you want the repeated number pattern to start.

• Manually enter the data you want repeated.

• Place your cursor on the bottom-right of the cell. An AutoFill sign pops up.
• Hold Ctrl on your keyboard and drag the AutoFill handle downward. Release the Ctrl button.

Steps (Repeated Group of Cells):

• Repeating groups of cells is the same procedure.
• Select the group of cells. Press Ctrl on your keyboard, and drag the AutoFill handle downward.

The group is repeated.

Read more: How to Repeat Formula Pattern in Excel

Method 2 – Use the Fill command to Repeat a Number Pattern

Steps:

• Manually enter the data to set the pattern you want repeated.

• Hover your cursor on the bottom-right of the selected cell(s), and an AutoFill sign will pop up.

• Drag the AutoFill handle downward.

Read More: Applications of Excel Fill Series

Method 3 – Use the IF Function to Repeat a Number Pattern

Steps:

• Manually enter the data to set the pattern you want to be repeated.

• Select the first empty cell in the fill area.

• Enter the following IF function in the Formula Bar.

• In this example, C7=7 is the logical_test for repeating numbers.
• 5 is the starting number.
• C7+1 – +1 is the interval the repeated numbers will increase by.

• Press Enter.

• Drag the autoFill handle downward.

• The cells populate with the repeated pattern.

Read More: How to Perform Predictive AutoFill in Excel

Method 4 – Insert the MOD Function to Repeat a Number Pattern

You can easily apply the MOD function to repeat the number pattern. Let’s follow the instructions below to learn!

Steps:

• Manually enter the data to set the pattern you want to be repeated.
• Select the next cell in the fill area.

• Enter the following MOD function in the Formula Bar.

• In this example, C5 is the first number to repeat.
• 3 is the divisor (the length of the pattern in this example).
• +1 is the increase interval between numbers.

• Press Enter.

• Drag the AutoFill handle downward.

• The cells populate with the repeated pattern.

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

Method 5 – Use the MOD and ROW Functions to Repeat a Number Pattern

Steps:

• Select the first cell.

• Enter the MOD and ROW functions in the Formula Bar.

• In this example, 3 is the increase interval between numbers.
• ROW() returns the current row number.
• -2 is the decrease number.

• Press Enter.

• Drag the AutoFill handle downward.

• The cells populate with the repeated pattern.

Things to Remember

• Don’t forget to manually type the data needed to set the pattern first.
• If the divisor in the MOD function is 0, the function will return a #DIV/0 error.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Related Articles

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

<< Go Back to Autofill Numbers | Excel Autofill | Learn Excel