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.

Quickest Way To Repeat Formula Pattern in Excel is Using Fill Handle

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.

Result: Quickest Way To Repeat Formula Pattern in Excel

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.

See Formulas To Repeat Formula Pattern in Excel

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.

Visible Formulas To Repeat Formula Pattern in Excel

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.

Use CTRL+D Shortcut Key to Repeat Formula Pattern in Excel

Final Result

The CTRL+D shortcut has repeated the formula pattern in the cells of column E.

Result of Using CTRL+D Shortcut Key to Repeat Formula Pattern in Excel

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

Use Excel Table to Repeat Formula Pattern in Excel

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.

Create Excel Table to Repeat Formula Pattern in Excel

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]

Repeat Formula Pattern Using an Excel Table in Excel

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.

Use the Fill Command to Repeat Formula Pattern in Excel

Final Result

The Fill command has repeated the formula pattern in the whole column.

Using the Fill Command to Repeat Formula Pattern in Excel

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

Use CTRL+ENTER Repeat Formula Pattern in Excel

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.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *