Repeat Formula Pattern in Excel with 5 Methods

In Excel, “repeating a formula” refers to copying a formula from one cell and pasting it into multiple other cells to perform the same calculation or operation with different data points. It’s a way to apply the same formula logic to a range of cells, saving you time and effort when working with large datasets or performing repetitive calculations.

To repeat a formula pattern in Excel, you can follow these steps:

1. Enter your formula in a cell where you want to start the pattern.
2. Click on the cell with the formula to select it.
3. Hover your mouse cursor over the bottom-right corner of the cell until it changes to a black crosshair called Fill Handle.
4. Drag the Fill Handle to the cells where you want to repeat the formula pattern.
5. Release the mouse button to fill the cells with the formula pattern.

Excel will automatically adjust the references in the formula as you drag, so the formula pattern will adapt to the new cell locations.

Here’s the dataset I’m going to use for the article. It’s got all the info about a company’s employees, like their names (in column A), departments (in column B), basic salary (in column C), and bonus (in column D). I’m gonna use a simple formula to figure out how much each employee makes in column E, which is Total.

Repeat Formula Pattern in Excel Using Fill Handle

The quickest way to repeat the formula pattern in a column is to use the Fill Handle. To copy down a formula with changing cell reference, follow the steps below:

1. Write a formula in the top cell of a column.
   I have written this simple formula in cell E2: =C2+D2
2. Press ENTER.
3. Double-click on the Fill Handle to copy the formula down.

You’ll notice that all the cells in column E are filled with the same pattern. The pattern keeps repeating with different cell references in every cell.

Now if you want to show the formulas, you can follow the steps below:

1. Select the Formulas tab.
2. 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 changing cell addresses.
3. 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

Repeat the Formula Pattern in Excel By Using Shortcut Key

Using the CTRL+D key, you will be able to automatically fill the column with a repeating formula pattern. To do that, go through these steps below:

1. Type a formula in the top cell of a column.
2. Press ENTER to apply the formula.
3. Select all the cells you want to fill down with the formula.
4. Press CTRL+D.

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
• 5+ Proven Methods to Copy a Formula to Entire Column in Excel

Repeat Formula Pattern in Excel Using Excel Table

Another way to repeat a formula pattern is by converting a data range into an Excel Table. The Excel Table will automatically fill a column with the same formula pattern.

Now follow the steps below to repeat the formula pattern in Excel:

1. Select the entire data range.
2. Select the Insert tab.
3. Click on the Table command from the Tables group. The Create Table window will show up on the screen.
4. Check the table range.
5. Check the My table has headers checkbox has a tickmark.
6. Hit the OK button. The cells are arranged in a table now.
7. Write your desired formula in cell E2.
   I have typed a formula to find out the total salary of the employees: =[@[Salary]]+[@Bonus]
8. Press ENTER to apply the formula.

This way you can apply a formula pattern in an entire column.

Repeat the Formula Pattern in Excel with the Fill Command

The Fill command is an alternative to the Fill Handle. To repeat the formula pattern in Excel using the Fill command, follow these steps:

1. Write a formula in the top cell of an empty column.
2. Select all the cells you want to fill with the formula.
3. Click on the Home tab.
4. Click on the Fill drop-down from the Editing group.
5. From the Fill drop-down, select the Down command.

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)
• Copy Excel Sheet to Another Sheet with Same Format and Formulas

Repeat Formula Pattern in Excel with CTRL+ENTER Command

Another way to repeat the formula pattern is by using the CTRL+ENTER keyboard shortcut. Now, follow the steps below:

1. Select all the cells of column E.
2. Write the formula in any cell of column E.
3. Press CTRL+ENTER together.

Here is the final result. Take a look.

Conclusion

In this article, I have presented 5 ways to repeat formula patterns in Excel. I hope that these 5 ways will help you solve your formula pattern recurrence problem. If you have any confusion, please let us know in the comment section below. Thank you.

Frequently Asked Questions

How do you repeat the last action in Excel?

To repeat the last action in Excel, just press CTRL+Y or F4 key. If F4 doesn’t work, you might have to press F-Lock or Fn key and then F4. If you’re using the mouse, click the Redo button on the Quick Access Toolbar. (The Redo button only pops up after you’ve already undone something.)

How do I repeat cell formatting in Excel?

To repeat cell formatting in Excel, first, copy the source cell’s formatting by selecting it and using Ctrl+C or right-click and choose “Copy.” Then, select the target cell or cells and use Ctrl+Alt+V, or right-click, choose “Paste Special,” select “Formats,” and click “OK” to apply the formatting. Alternatively, use the Format Painter tool for quick formatting replication.

How do I repeat the same text in multiple cells in Excel?

Select the target cells, type the desired text in the formula bar, and press Ctrl+Enter (Cmd+Enter on Mac) to enter the text into all the selected cells simultaneously.

How to show the hidden Fill Handle in Excel?

If you can’t find the Fill Handle, maybe it’s hidden. To show it again, just click File > Options, then click Advanced. In the Editing options, check the “Enable fill handle and the cell drag and drop” box.