How to Repeat Number Pattern in Excel (5 Methods)

While working with a large dataset in Microsoft Excel, sometimes we need to repeat the number pattern. Our today’s dataset is about information from several workers. We can easily repeat the number pattern in Excel by using keyboard shortcuts, the Fill command, and the IF, MOD, and ROW functions. In this article, we’ll learn five quick and suitable ways that Excel repeat number patterns with appropriate illustrations.

How to Repeat Number Pattern in Excel: 5 Methods

Let’s say, we have a dataset that contains information about several workers. We will calculate the repeated working days and salary of those workers. Here’s an overview of the dataset for today’s task.

1. Use the Keyboard Command to Repeat Number Pattern in Excel

Using the keyboard command to repeat the number pattern in Excel, is the easiest and most time-saving way. We will calculate the repeated working days of several workers from our dataset by using the keyboard command. Let’s follow the instructions below to learn!

Step 1:

• First of all, select a cell to repeat the number pattern in Excel. We will select cell C5 from our dataset.

• After that, manually type 5 in the selected cell.

• Hence, place your cursor on the bottom-right of cell C5, and an AutoFill sign pops up. Now, press Ctrl on your keyboard, drag the AutoFill handle downward, and release the Ctrl button.

Step 2:

• Again press Ctrl on your keyboard, and drag the AutoFill handle downward simultaneously.

• After completing the above process, release the Ctrl button and AutoFill handle also, you will be able to repeat the number pattern that has been given in the below screenshot.

Read more: How to Repeat Formula Pattern in Excel

2. Perform the Fill command to Repeat Number Pattern in Excel

In this method, we will learn how to repeat the number pattern by performing the Fill command. Please follow the steps below.

Steps:

• First, manually write down the salary of Bob and John and then select the cells D5 and D6.

• Further, place your cursor on the bottom-right of the selected cells, and an AutoFill sign will appear in front of you.

• Hence, drag the AutoFill option downward, and you will be able to repeat the salary sequence that has been given below screenshot.

Read More: Applications of Excel Fill Series

3. Apply the IF Function to Repeat Number Pattern in Excel

We can easily calculate the repeated working days of several workers by applying the IF function. From our dataset, we will calculate Bob, John, and Anny’s repeated working days to the entire column C. Let’s follow the steps below.

Step 1:

• Firstly, manually type the working days of Bob, John, and Anny in cells C5, C6, and C7 respectively.

Step 2:

• Hence, select cell C8.

• After selecting cell C8, type the IF function in the Formula Bar. The IF function is,

• Where C7=7 is the logical_test for repeating numbers.
• 5 is the number where the repeating number starts from
• C7+1 means the repeated number is increasing with

• After that, simply press Enter on your keyboard, and you will get the repeated number 5 as the return of the IF function that has been given below screenshot.

Step 3:

• Further, drag the autoFill sign downward.

• While completing the above process, you will get the repeated numbers which is the return of the IF function.

Read More: How to Perform Predictive AutoFill in Excel

4. Insert the MOD Function to Repeat Number Pattern in Excel

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

Step 1:

• First, manually type 1 as the working days of Bob in cell Then, select cell C6.

• After that, type the MOD function in the Formula Bar. The MOD function is,

• Where C5 is the first number to repeat
• 3 is the divisor and +1 is the increasing interval.

Step 2:

• Hence, press Enter on your keyboard, and you will get the repeated number 2 as the return of the IF function.

Step 3:

• After that, drag the AutoFill sign downward to repeat the number pattern.

• Finally, after completing the above process, you will get the repeated numbers which is the return of the MOD function that has been given in the below screenshot.

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

5. Use the MOD and ROW Functions to Repeat Number Pattern in Excel

Undoubtedly, applying the MOD and ROW functions to repeat the number pattern is an easy task. The MOD function returns the remainder of a number when divided by the divisor and the ROW function provides the row number in Excel. Let’s follow the steps below.

Steps:

• Select cell C5 first.

• Further, type the MOD and ROW functions in the Formula Bar. The formula is,

• Where 3 is the difference between two numbers.
• ROW() provides the row number and -2 is the decreasing number.

• After that, press Enter on your keyboard, and you will get 6 as the return of the MOD and ROW functions.

• Hence, drag the AutoFill sign downward to repeat the number pattern.

• At last, you will get your desired output to the entire column C.

Things to Remember

👉 While working with keyboard command and Fill command, manually type data 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.

Conclusion

I hope all of the suitable methods mentioned above to repeat the number pattern will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• How to Autofill Dates in Excel Without Dragging
• 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