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 of several workers. We can easily repeat the number pattern in Excel by using keyboard shortcuts, the Fill command, the IF, MOD, and ROW functions also. In this article, we’ll learn five quick and suitable ways that Excel repeat number patterns with appropriate illustrations.


Download Practice Workbook

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


5 Suitable Ways to Repeat Number Pattern in Excel

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.

excel repeat number pattern


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.

Use the Keyboard Command to Repeat Number Pattern in Excel

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

Use the Keyboard Command to Repeat Number Pattern in Excel

  • Hence, place your cursor on the bottom-right on 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.

Use the Keyboard Command to Repeat Number Pattern in Excel

  • 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 Autofill Numbers in Excel without Dragging (5 Quick Methods)


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.

Perform the Fill command to Repeat Number Pattern in Excel

  • 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.

Perform the Fill command to Repeat Number Pattern in Excel

Read More: How to AutoFill Numbers in Excel with Filter (2 Methods)


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.

Apply the IF Function to Repeat Number Pattern in Excel

Step 2:

  • Hence, select cell C8.

Apply the IF Function to Repeat Number Pattern in Excel

  • After selecting cell C8, type the IF function in the Formula Bar. The IF function is,
=IF(C7=7, 5, C7+1)
  • 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.

Apply the IF Function to Repeat Number Pattern in Excel

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.

Apply the IF Function to Repeat Number Pattern 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.

Insert the MOD Function to Repeat Number Pattern in Excel

=MOD(C5,3)+1
  • Where C5 is the first number to repeat
  • 3 is the divisor and +1 is the increasing interval.

Insert the MOD Function to Repeat Number Pattern in Excel

Step 2:

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

Insert the MOD Function to Repeat Number Pattern in Excel

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.


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.

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

  • Further, type the MOD and ROW functions in the Formula Bar. The formula is,,
=9+3*MOD(ROW(),-2)
  • Where 3 is the difference between two numbers.
  • ROW() provides the row number and -2 is the decreasing number.

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

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

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

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

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

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

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

Read More: How to Number Rows Automatically in Excel (8 Methods)


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.


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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo