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.
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.
- Then, select the entered value and use the Fill Handle tool to Autofill the pattern down the cells.
- Hence, your cells will return the result filling the pattern of series.
Read More: How to Repeat Rows in Excel Based on Cell Value
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.
- Then, just type the first letter of the first second person and Excel will automatically recognize the full name for the others.
- Hence, press ENTER, and cells will return you the results.
Read More: How to Repeat Multiple Rows 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
- Now, drag the Fill Handle tool to Autofill the formula, or just double-click on the formula to fill the pattern.
Read More: How to Repeat Rows at Top 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. 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.
- Then, copy the cell and select the cells where you want the same formula as the copied cell.
- Now, paste the formula and the cells will return you the pattern.
Read More: How to Repeat Rows in Excel at Bottom
Similar Readings
- How to Repeat Formula in Excel for Whole Column
- [Fixed] Repeat Last Action Not Working in Excel
- Repeat Text in Excel Automatically
- [Fixed!] Excel Rows to Repeat at Top Feature Greyed Out
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.
- Now, hit ENTER and your cells will return you the pattern.
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.
Read More: How to Repeat Formula for Each Row in Excel
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.
