4+ Ways to Repeat Formula Pattern in Excel
Many of you don’t know that you don’t need to copy a formula manually to repeat a formula pattern. Excel has some handy approaches to do that. Using the proper method can help you to save a lot of time. This article will show you 4+ methods to help you repeat a formula pattern in Excel.
Introduction to Dataset
Here is the dataset that I will be using for this article. It has information about a company, such as its employee names (column A: Employee), their department (column B: Department), their basic salary (column C: Salary) and their gained bonus (column D: Bonus). I will be using an easy formula to calculate the total salary of the employees in column E: Total.
Quickest Way To Repeat Formula Pattern in Excel
The quickest way to repeat the formula pattern in a column is to use the Fill Handle.
Now Follow the Guide
Step_1: Write a formula in the top cell (cell E2) of a column (column E).
I have written this simple formula in cell E2:
=C2+D2
Step_2: Press ENTER.
Step_3: Double-click on the Fill Handle to copy the formula down.
Final Result
You will see the cells of column E are now filled with the same formula pattern. The formula is repeated with changed cell references in each cell.
To Make the Applied Formulas Visible in the Data Table
Step_4: Select the Formulas tab.
Step_5: From the Formula Auditing group, select the Show Formulas command.
All the formulas that are applied in the data table will show up instead of the values. Notice, that all formulas of column E show the repeated formula pattern with different cell addresses.
Step_6 (optional): Again click on the Show Formulas command to bring back the values of column E.
🔗 9 ways to Copy Formula Down in Excel without Dragging
🔗 6+ Ways to Copy a Formula in Excel with Changing Cell References
Alternative Way #1: Use CTRL+D Shortcut Key to Repeat Formula Pattern in Excel
The CTRL+D shortcut key will help to autofill a column with a repeated formula pattern.
Now Follow the Guide
Step_1: Type a formula in the top cell (E2) of a column (column E).
Step_2: Press ENTER to apply the formula.
Step_3: Select all the cells of column E.
Step_4: Press CTRL+D.
Final Result
The CTRL+D shortcut has repeated the formula pattern in the cells of column E.
🔗 3 Ways to Copy a Formula in Excel without Changing Cell References
🔗 4 Ways to Fill Formula Down to Specific Row in Excel
Alternative Way #2: Use Excel Table to Repeat Formula Pattern in Excel
Another way to repeat a formula pattern is by applying the Excel Table command. The Excel Table will automatically fill a column with the same formula pattern.
Now Follow the Guide
Step_1: Select the entire data table.
Step_2: Select the Insert tab.
Step_3: Click on the Table command from the Tables group.
The Create Table window will show up on the screen.
Step_4: Check the table range.
Step_5: Check the My table has headers checkbox has a tickmark.
Step_6: Hit the OK button.
The cells are arranged in a table now.
Step_7: Write your desired formula in cell E2.
I have typed a formula to find out the total salary of the employees:
=[@[Salary]]+[@Bonus]
Step_8: Press ENTER to apply the formula.
Final Result
This way you can apply a formula pattern in an entire column.
🔗 5+ Proven Methods to Copy a Formula to Entire Column in Excel
Alternative Way #3: Use the Fill Command to Repeat Formula Pattern in Excel
The Fill command is an alternative to the Fill Handle.
Now Follow the Guide
Step_1: Write a formula in the top cell (E2) of an empty column (column E).
Step_2: Select all the cells of column E.
Step_3: Click on the Home tab.
Step_4: Click on the Fill drop-down from the Editing group.
Step_5: From the Fill drop-down, select the Down command.
Final Result
The Fill command has repeated the formula pattern in the whole column.
🔗 Apply Same Formula to Multiple Cells in Excel (5+ Solutions)
🔗 How to Copy a Formula in Excel to Another Sheet (4+ Solutions)
Alternative Way #4: Use CTRL+ENTER Repeat Formula Pattern in Excel
Another way to repeat the formula pattern is using the CTRL+ENTER keyboard shortcut.
Now Follow the Guide
Step_1: Select all the cells of column E.
Step_2: Write the formula in any cell of column E.
Step_3: Press CTRL+ENTER together.
Final Result
Here is the final result. Take a look.
🔗Copy Excel Sheet to Another Sheet with Same Format and Formulas
Conclusion
I have discussed 5 methods to repeat a formula pattern in Excel. I hope these approaches will be enough to solve your problem of repeating a formula pattern. If you find anything confusing, please share it with us in the comment section. Thank you.
