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

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.


Download Practice Workbook

You can download the practice book from the button below.


8 Easy Ways to Make a Pattern Repeat in Excel

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


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 Autofill 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


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


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. For getting 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 to 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.


Conclusion

In this article, we have tried to show you several ways 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. For more queries, kindly visit our website ExcelDemy. Have a great day!

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo