How To Iterate (Loop) in an Excel Spreadsheet

Visual Basic is where you usually solve a lot of your spreadsheet tasks such as iteration. However, most of the time, using Visual Basic is confusing and so much of a hassle. Good thing that iteration is possible to be done in MS Excel. Find out how through these steps:

  • Enable iteration in Excel. To do this, go to Tools and then Options in MS Excel. A window will appear where you must select the necessary settings. Check the boxes preceding "Iteration" and also "Manual." Though enabling manual calculations is not a requirement for the iteration process, it will make calculations easier for you later in the picture. When you are in manual mode, you must press F9 to calculate the cells. For the time being, choose manual and later switch to automatic. Also, do not forget to set the "Maximum Iterations" on the appropriate field. This way, Excel will easily know how many to calculate. For Excel 2007, click on Formula and then Calculations Options.
  • Enter the formula in the cells for the calculation. This step is very vital to the success of the iteration. The formula must be correct or else you will get all the results wrong. Take this as an example, if 1 is your first number and 100 is your last number, which are placed in D3 and E3 cells respectively, the formula will be as follows:
  • Current number. This must be inputted in G3. The formula is =IF(G3>E3,G3,G3+1). The current number will act as your tracker while you do the looping.
  • Running sum. This must fall on H3. Running sum refers to the overall sum of the current iteration number as you calculate. The formula is =IF(E3<G3,H3,H3+G3).
  • Set a reset cell. The problem in Excel is that there is no automatic reset button. However, a reset button is very important for ease of iteration tasks. Bu the good news is that you can input a formula in one of the cells to reset the calculations. To rest the current number, follow this formula =IF(B3=1,0,IF(G3>E3,G3,G3+1)). The formula for resetting the running sum is =IF(B3=1,0,IF(E3<G3,H3,H3+G3)). Click F9 on the cell to reset the current number and running sum to 0.
  • Input your own details for the iteration. Once the formula is already inputted, input your own details such as the first number and last number. Take note that when you will be calculating, you must press F9 several times. For instance, if you need to calculate 50 iterations, the F9 must be hit 50 times as well. However, holding the F9 button for a couple of minutes will prevent you from hitting the key a lot of times.
  • Save the Excel workbook for future use. If you will be doing this task a lot of times, it is best to save the Excel workbook for your convenience on the next iteration calculations. However, before you close Excel, bring back the calculation settings to automatic. If you don’t, you will need to press F9 for your other Excel tasks.

Free yourself from the hassle brought by Visual Basic in the iteration process. Thanks to Excel, this task is not at a tough one to accomplish these days. As long as you know the formula and methods in the calculations, you will likely not meet any problem along the way.


Share this article!

Follow us!

Find more helpful articles: