How to Loop in Excel

A standard formula in Excel cannot repeat more than once; it is a static calculation that only changes when other cell values change. However, you can use macros to execute looping commands based on Visual Basic for Applications code that you enter. Macros automate a series of actions so you can repeat them quickly and easily. Note that enabling macros can compromise the security of your computer, so only use looping macros when you are confident that the source of the Excel spreadsheet is secure.

Laptop computer with spreadsheet on screen
Loop macros can take tedious work and automate it.
credit: Stockbyte/Stockbyte/Getty Images

Step

Launch Excel, open the spreadsheet where you want to use the looping command, and then press "Alt-F11" to open the Visual Basic Editor.

Step

Select the "Insert" menu, and then choose "UserForm." A UserForm window appears with a Controls Toolbox next to it. In the Controls Toolbox, select the "Command Button." Labels appear when you hover over the icons. Draw the Command Button in the UserForm window.

Step

Right-click on the Command Button you drew and select "View Code" to open the VBA code window for that button. Note that you can have multiple buttons per UserForm if you want to code and execute multiple loops.

Step

Enter the VBA code for the loop you want to perform between the "Private Sub" and "End Sub" lines in the code editor. For example, to fill a range of cells with data, you could enter the following code:

Step

Dim i As Integer i = 1

Step

Do While i < 5 Cells(i, 1).Value = 15 i = i + 1 Loop

Step

Press "F5" to execute the loop. Alternatively, click the "Run" menu, and then select "Run Sub/UserForm."