Don’t you want to use Excel VBA and want to make a **FOR Loop** in Excel using Formula? In this article, I’ve shown how you can make **FOR Loop** using formulas.

If you know how to code with Excel **VBA**, you’re blessed 🙂. But, if you never wrote code in **VBA** or want to keep your Excel workbook free of Excel **VBA** code, then most of the time you have to think out of the box to create a simple** loop**.

## Download Working File

Download the working file from the link below:

## 3 Examples to Make FOR Loop in Excel Using Formula

Here, I will demonstrate **3** examples to make **FOR Loop** in Excel using a formula. Let’s see the detailed examples.

### 1. Applying Combined Functions to Make FOR Loop in Excel

Now, let me know the background that is encouraging me to write this example.

I am the author of some courses on Udemy. One of the courses is on Excel Conditional Formatting. The course title is:** Learn Excel Conditional Formatting with 7 Practical Problems.** [**to get free access to this course, click here**].

In the course discussion board, a student asked me a question as below [screenshot image].

Read the above question carefully and try to solve it…

__Steps to Solve the Above Problem:__

Here, I will use **OR**, **OFFSET**, **MAX**, **MIN**, and **ROW** functions as Excel Formula to create a **FOR Loop**.

- Firstly, your job is to open a new workbook and input the above values one by one into the worksheet [start from cell
**C5**]. - Secondly, select the whole range [from cell
**C5:C34**]. - Thirdly, from the
**Home**ribbon >> click on the**Conditional Formatting**command. - Finally, select the
**New Rule**option from the drop-down.

At this time, **New Formatting Rule** dialog box appears.

- Now, in the
**Select a Rule Type**window >> select**Use a formula to determine which cells to format**option. - Then, in the
**Format values where this formula is true**field, type this formula:

`=OR(OFFSET(C5,MAX(ROW(C$5)-ROW(C5)+3,0),0,MIN(ROW(C5)-ROW(C$5)+1,4),1)-OFFSET(C5,MAX(ROW($C$5)-ROW(C5),-3),0,MIN(ROW(C5)-ROW(C$5)+1,4),1)=3)`

- Now, select the appropriate format type by clicking on the
**Format…**button in the dialog box.

At this time, a dialog box named **Format Cells** will appear.

- Now, from the
**Fill**option >> you have to choose any of the colors. Here, I selected the**Light Blue**background. Also, you can see the**sample**instantly.*In this case, try to choose any***light**color. Because the dark color may hide the inputted data. Then, you may need to change the**Font Color**. - Then, you must press
**OK**to apply the formation.

- After that, you have to press
**OK**on the**New Formatting Rule**dialog box. Here, you can see the sample instantly in the**Preview**box.

Lastly, you will get the formatted numbers.

Let me show you the algorithm to solve the above problem:

- Here, to make you understand the algorithm easily, I will explain the whole thing with two reference cells: cells
**C11**and**C17**. In cells**C11**and**C17**, the values are**10**and**20**respectively (above image). If you are used to Excel formulas, then you can smell the**OFFSET**function, as**OFFSET**function works with reference points. - Now, imagine I am taking the values of cell ranges
**C8:C11**&**C11:C14**, and**C14:C17**&**C17: C20**side by side [image below]. Reference cells are**C11**and**C17**and I am taking a total of**7**cells around the reference cell. You will get an imaginary picture like the following. From the first part, you can find a pattern from the image.**C9–C12=3**,**C10-C13=3**, there is a pattern. But for the second part, there is no such pattern.

- So, let’s build the algorithm with keeping the above pattern in mind. Before building the common formula, I shall show what the formulas will be for the cells
**C11**and**C17**and then will modify the formula to make it common for all. For a reference point (like**C11**or**C17**), I shall take a total of**7**cells around it (including the reference point) and place them side by side in the formula creating arrays. Then I shall find out the difference of the arrays if any of the differences is equal to**3**that the reference cell will be**TRUE**valued. - Here, I can do that easily using the
**OFFSET**function as the**OFFSET**function returns an array. Say for cell reference**C11**, I can write the formula like this:**=OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3)**. What will this formula return? The first offset function of the formula will return array:**{10; 11; 12; 15}**, second offset function will return array**{5; 8; 9; 10}**. And you know**{10; 11; 12; 15} – {5; 8; 9; 10} = {10-5; 11-8; 12-9; 15-10} = {5; 3; 3; 5}**. When this array is logically tested with**=3**then Excel calculates internally like this:**{5=3; 3=3; 3=3; 5=3} = {False; True; True; False}**. When the**OR**function is applied on this array:**OR({False; True; False; True}**, you get**TRUE**. So cell**C11**gets true values as returned. - So, I think you have got the whole concept of how this algorithm is going to work. Now there is a problem. This formula can work from cell
**C8**, above cell**C8**, there are**3**cells. But for cells**C5, C6,**and**C7**this formula cannot work. So the formula should be modified for these cells. - Now, for cells
**C5**to**C7**, we want that the formula will not take into consideration the upper**3**cells. For example, for cell**C6**, our formula will not be like the formula for cell**C11**:**=OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3)**. - Here, for cell
**C5**, the formula will be like:**OR(OFFSET(C5, 3, 0, 1, 1)-OFFSET(C5, 0, 0, 1, 1)=3)**. - Then, for cell
**C6**, the formula will be like:**OR(OFFSET(C6, 2, 0, 2, 1)-OFFSET(C6, -1, 0, 2, 1)=3)**. - After that, for cell
**C7**, the formula will be like:**OR(OFFSET(C7, 1, 0, 3, 1)-OFFSET(C7, -2, 0, 3, 1)=3)**. - Again, for cell
**C8**, the formula will be like:**OR(OFFSET(C8, 0, 0, 4, 1)-OFFSET(C8,-3, 0, 4, 1)=3)**; [this is the general formula]. - Then, for cell
**C9**, the formula will be like:**OR(OFFSET(C9, 0, 0, 4, 1)-OFFSET(C9,-3, 0, 4, 1)=3)**; [this is the general formula]. - Finally, do you find some patterns from the above formulas? The first
**OFFSET**function’s rows argument has decreased from**3**to**0**; the height argument has increased from**1**to**4**. The second**OFFSET**function’s rows argument has decreased from**0**to**-3**and height argument has increased from**1**to**4**. - Firstly, the first
**OFFSET**function’s rows argument will be modified like this:**MAX(ROW(C$5)-ROW(C5)+3,0)** - Secondly, the second
**OFFSET**function’s rows argument will be modified like this:**MAX(ROW(C$5)-ROW(C5),-3)** - Thirdly, the First
**OFFSET**function’s height argument will be modified like this:**MIN(ROW(C5)-ROW(C$5)+1,4)** - Fourthly, the second
**OFFSET**function’s height argument will be modified like this:**MIN(ROW(C5)-ROW(C$5)+1,4)** - Now, try to understand the above modification. These are not that tough to understand. All these
**four**modifications are working as**FOR LOOP**of Excel VBA but I’ve built them with Excel Formulas. - So, you got the ways how the general formula works for the cells from
**C5:C34**.

So I was talking about Looping in Excel spreadsheets. So, this is a perfect example of looping in Excel. Here, every time the formula takes **7** cells and works on the cells to find out a specific value.

### 2. Use of IF & OR Functions to Create FOR Loop in Excel

In this example, suppose you want to check if the cells contain any values or not. Furthermore, with Excel VBA **FOR Loop,** you can do this easily but here, I will do that using an Excel formula.

Now, you can use **the** **IF**, and the **OR** functions as Excel Formula to create **FOR Loop**. Furthermore, you can modify this formula according to your preference. The steps are given below.

**Steps:**

- Firstly, you have to select a different cell
**E5**where you want to see the**Status**. - Secondly, you should use the corresponding formula in the
**E5**cell.

`=IF(OR(B5="",C5="",D5=""),"Info Missing","Done")`

- Subsequently, press
**ENTER**to get the result.

**Formula Breakdown**

Here, the** OR** function will return **TRUE** if any of the given logic becomes** TRUE**.

- Firstly,
**B5=””**is the**1st**logic, which will check whether the cell**B5**contains any value or not. - Secondly,
**C5=””**is the**2nd**logic, which will check whether the cell**C5**contains any value or not. - Thirdly,
**D5=””**is the**3rd**logic. Similarly, which will check whether the cell**D5**contains any value or not.

Now, the **IF **function returns the result which will fulfill a given condition.

- When the
**OR**function gives**TRUE**then you will get “**Info Missing**” as**Status**. Otherwise, you will get “**Done**” as the**Status**.

- After that, you have to drag the
**Fill Handle**icon to**AutoFill**the corresponding data in the rest of the cells**E6:E13**.*Or you can double-click on the***Fill Handle**icon.

Finally, you will get all the results.

### 3. Employing SUMIFS Function to Create FOR Loop in Excel

Suppose, you want to make the total bill for a certain person. In that case, you may use the **FOR Loop** using the Excel formula. Here, I will use **the SUMIFS function** to create the** FOR Loop** in Excel. The steps are given below.

**Steps:**

- Firstly, you have to select a different cell
**F7**where you want to see the**Status**. - Secondly, you should use the corresponding formula in the
**F7**cell.

`=SUMIFS($C$5:$C$13,$B$5:$B$13,E7)`

- Subsequently, press
**ENTER**to get the result.

**Formula Breakdown**

- Here,
**$C$5:$C$13**is the data range from which the**SUMIFS**function will do the summation. - Then,
**$B$5:$B$13**is the data range from where the**SUMIFS**function will check the given criteria - Lastly,
**E7**is the criteria. - So, the
**SUMIFS**function will add the payments for the**E7**cell value.

- After that, you have to drag the
**Fill Handle**icon to AutoFill the corresponding data in the rest of the cells**F8:F10**.

Finally, you will get the result.

## Conclusion

We hope you found this article helpful. Here, we have explained **3** suitable examples to make **FOR Loop** in Excel using formulas. You can visit our website **ExcelDemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Hi Kawser,

nice post. However, I couldn’t solve this loop using offset.. Any ideas?

what i’m trying to do is add the letter “A” in front of the string “BCD” until length of the string becomes 10. (for which A would need to be added 7 times in front of “BCD” to make it “AAAAAAABCD”

If cell A1 holds your string “BCD” and you want to show “AAAAAAABCD” in cell B1, input this formula in cell B1: =REPT(“A”, 10-LEN(A1))&A1.

To solve this problem, you don’t have to use any looping. Or, do you have some other requirement for this problem? Let me know.

Best regards.

hi,

Thank you for details information.

I have 2 columns of numbers , say each column 10 numbers , total 20 numbers.

I want delete the similar numbers in both columns and keep just the numbers are different. Numbers need be sorted from small to large too.

this is urgent case.

Any comment I appreciate.

Hi, BIJAN!

Thank you for your query.

You can achieve your desired result following the workflow below:

Hometab >>Editinggroup >>Sort & Filtertool >>Sort Smallest to Largest option.Home tab >>Conditional Formattingtool >>Highlight Cells Rulesoption >>Duplicate Values…option.Duplicate Valueswindow will appear. Choose your desired format and click on theOKbutton.Ctrlkey >> select the duplicate cells >> press theDeletekey.I hope, it helps.

Regards,

Tanjim Reza

can i know how can i make the rows change as per the set values. Ex:

If i entered 5, rows in excel=5 & if 6, rows in excel=6 & so on…

Hi, BANDEET POUDEL!

Thank you for your query.

I am a little bit confused about your query. Are you asking if you can jump to a row upon writing the row number at an instant?

If you are asking this, then the solution is:

You can write the preferred cell’s reference number in the

name boxand press theEnterbutton to jump there in an instant.Please let us know the feedback if your problem is solved or if you meant some other things in your query.

Regards,

Tanjim Reza

Great formula but I can’t understand how to incorporate it into a SUMIFS formula that I need to perform a loop.

Hi, JEFF!

Thank you for your query.

I am afraid you can not incorporate this article’s formula into the

SUMIFSformula to perform a loop because we have used theMAXfunction in this article. But, it is a limitation of Excel to use theSUMIFSfunction with theMAXfunction.To know about some other Excel limitations, you can follow the link below:

https://www.exceldemy.com/top-20-excel-limitations-that-might-frustrate-you/#15_No_sumifscountifsaverageifs_equivalents_for_functions_such_as_max_or_median

Regards,

Tanjim Reza

I think there is a slight error in this formula, for cell A2 u r using =OR(OFFSET(A2, 3, 0, 1, 1)-OFFSET(A2, 0, 0, 1, 1)=3). It means if A5 – A2 = 3 then highlight. In the given case the first four values are 1,2,3,5 hence it worked. If the values are like 1,3,2,4 then it will not work as it highlights the cells even when they do not meet the criteria

Hi, BHANU!

Thank you for your query.

As the problem discussed here is sorted from smallest to largest, you would also need to sort your numbers before applying this article’s approach.

Regards,

Tanjim Reza

I think there is an error in the formula, for cell A2 you are using =OR(OFFSET(A2, 3, 0, 1, 1)-OFFSET(A2, 0, 0, 1, 1)=3). It means if Cell A5-A2=3 then highlight, it works for the above given data as the first four values starting from A2 are 1,2,3,5. But if the values are 1,5,2,4 (for example) it highlights them even if they do not meet the criteria.

Hi, BHANU!

Thank you for your query.

As the problem discussed here is sorted from smallest to largest, you would also need to sort your numbers before applying this article’s approach.

Regards,

Tanjim Reza

The criteria of this problem supposed that the data are sorted. Your examples aren’t in sorted order.

Hi, JAMAL!

You have pointed out the correct thing in response to BHANU’s problem.

Thank you for your valuable feedback!

Regards,

Tanjim Reza

How to subtract 2 arrays using offset?

Because when I use MAX(OFFSET(A3, 2, 0, 2, 1)-OFFSET(A3, -1, 0, 2, 1)) it isn’t working

Hi, KRISHNA!

Thank you for your query.

About your query, please check if you have put an equal sign (=) in the cell before inserting the formula. Because, on my end, your formula looks perfect. And, I checked it in my Excel sheet and it worked! If it still doesn’t work, please send us your excel file.

Regards,

Tanjim Reza

Thank you for you example. I have done something similar to cumulate a set of IDs (e.g. V1, V2, V3, V4, V7) to ranges (e.g. V1 to V4, V7).

I have a table that looks like

ID | error group 1 | error group 2 | … | error group 10

———————————————————

V1 | x1, x2 | x7 | … |

V2.1 | x2 | x1 | … |

D1 | x1 | | … | x7, x5

The number of rows in this table is known, but not fixed (normally between 10 and 100).

To Input for an existing sheet is:

error group | error | ID

1 | x1 | V1, D1

1 | x2 | V1, V2.1

2 | x1 | V2.1

2 | x7 | V1

10 | x5 | D1

10 | x7 | D1

With only 1 error type in a field of the error group I’m doing something like:

ID=IFERROR(TEXTJOIN(“, “,TRUE,FILTER(ID array ,error group 1 array=”x”,””)),””)

But I don’t know how to do for multiply error and the error code is not really fixed.

I’m not allowed to use any macro. All calculation has to be done in Excel-formulas Excel 365).

It would be nice if you give me a hint how to solve this kind of problem.

Thank you very much!

Nico