How to Make a Pattern Repeat in Excel (8 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Excel is used vastly for calculating a large number of data without using a calculator. Performing the same calculation for each and every input value is actually very time-consuming and Excel has made it easier for us to save time and energy. In this article, we will show you how to make a pattern repeat in Excel.


How to Make a Pattern Repeat in Excel: 8 Easy Ways

Let’s say, we have a dataset of various types of products ordered by different customers from an online store, their units, and the price per unit.

Dataset for Making a Pattern Repeat in Excel

We want to create a pattern repeated for the date of order, the ID of the customers, their full names, and the total price of the relevant products.

In this section, you will find 8 easy and quick methods to make a pattern repeat in Excel. So, let’s check them now!


1. Use Autofill Feature to Make a Pattern Repeat

Let’s say, the customers have ordered the products in a common pattern (i.e. keeping a gap of one day). We have the first two dates and we want to make the pattern repeat.

In order to demonstrate this method, proceed with the following steps.

Steps:

  • First of all, enter the first two dates.

Make a Pattern Repeat in Excel

  • Then, select the entered value and use the Fill Handle tool to Autofill the pattern down the cells.

Make a Pattern Repeat in Excel

  • Hence, your cells will return the result filling the pattern of series.

Make a Pattern Repeat in Excel

Read More: How to Repeat Rows for a Specified Number of Times in Excel


2. Use Flash Fill Feature to Repeat a Pattern

In our dataset, we have the first name and the last name of the customers and we want to combine the names and make the full name by using the Flash Fill feature. In order to demonstrate this method, proceed as follows.

Steps:

  • First, enter the full name of just one person and press ENTER.

Make a Pattern Repeat in Excel

  • Then, just type the first letter of the first second person and Excel will automatically recognize the full name for the others.

Make a Pattern Repeat in Excel

  • Hence, press ENTER, and cells will return you the results.

Make a Pattern Repeat in Excel

Read More: Repeat Text in Excel Automatically


3. Formula Drag or Double Clicking

In our dataset, we have the units of the ordered products and their respective price per unit. We want to get the total price of the products by dragging or double-clicking the formula. In order to do so, follow the steps below.

Steps:

  • First of all, enter the following formula into the selected cell and press ENTER.

=D5*E5

Here,

  • D5= Unit
  • E5= Per Unit Price

Make a Pattern Repeat in Excel

  • Now, drag the Fill Handle tool to Autofill the formula, or just double-click on the formula to fill the pattern.

Make a Pattern Repeat in Excel

Read More: How to Repeat Rows in Excel at Bottom


4. Copy and Paste a Formula to Make a Pattern Repeat

Let’s say, we have figured out the total price for just one cell by applying the formula. To get the total price, we will now copy the formula and paste it to get the result. Follow the steps below for doing so.

Steps:

  • Firstly, select the cell containing the formula.

Make a Pattern Repeat in Excel

  • Then, copy the cell and select the cells where you want the same formula as the copied cell.

Make a Pattern Repeat in Excel

  • Now, paste the formula and the cells will return you the pattern.


5. Enter a Formula to Multiple Cells to Make a Pattern

Here, we will now figure out the total price of the ordered products by entering just one formula into multiple cells. So, let’s proceed with the steps following.

Steps:

  • Firstly, select the cells where you want the pattern, and then ENTER your formula with any of the cells.

Make a Pattern Repeat in Excel

  • Now, hit ENTER and your cells will return you the pattern.

Make a Pattern Repeat in Excel


6. Repeat a Pattern by Using Power Query Tool

We will now show the use of the Power Query tool to repeat the pattern of the total price of the products.

Steps:

  • First of all, select the data> go to the Data tab> click Form Table/ Range.

  • Here, click OK on the Create Table box.

  • Now, the Power Query Editor will open up. Switch back to the Excel sheet and apply the following formula:

=[@Unit]*[@[Per Unit Price]]

  • Hence, your cells will return the result.


7. Applying INDIRECT Function

For our previous dataset, if we don’t have the per unit price for all the products, then you can also get the total price by applying the INDIRECT function to make a pattern repeat.  For this, follow the steps below.

Steps:

  • Firstly, calculate the total price for the first 3 cells as we have the value of the first 3 prices per unit.

  • Apply the following formula:

=D5*INDIRECT("E5",TRUE)

Here,

  • D5= Unit
  • E5= Per Unit Price

  • Now, select the 3 cells and drag the formula down.

  • Hence, Excel will make the pattern repeat.


8. Using SEQUENCE Function to Repeat Pattern

Now, we want to make the pattern repeat for the ID of the customers by using the SEQUENCE function. For this, follow the steps below.

Steps:

  • Firstly, enter the following formula into the first cell:

=SEQUENCE(7,1,141201,1)

Here,

  • 7= Number of Rows
  • 1= Number of Columns
  • 1412001= first ID
  • 1= Step (difference between ID numbers)

  • Then, hit ENTER to get the result.


Download Practice Workbook

You can download the practice book from the button below.


Conclusion

In this article, we have tried to show you several ways of how to make a pattern repeat in Excel. I hope from now on you can quickly make a pattern repeat in an Excel workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles.


Related Articles


<< Go Back to Repeat in Excel | Excel Cell Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo