An Excel formula generator writes the formula for you. Choose what you want to do, such as add up sales for one region, look up a price or give grades from scores, then type the cells your data is in. The Excel formula generator builds a formula you can copy and paste, with a short explanation of how it works.
How to use the Excel formula generator
- Choose a task from the list, such as SUMIF, VLOOKUP or a date difference.
- Type your cells and ranges, such as B2 or A2:A20, and any values the formula needs.
- Copy the formula and paste it into a cell in Excel, then press Enter.
- Copy it down to other rows if you need it for each row. Lookup ranges have $ signs so they don't move.
Formulas you can build
| Task | Example formula |
|---|---|
| Add up a range | =SUM(B2:B20) |
| Add up if a condition is met | =SUMIF(A2:A20,"North",C2:C20) |
| Count if a condition is met | =COUNTIF(B2:B20,">55") |
| If, then, otherwise | =IF(B2>=50,"Pass","Fail") |
| Grades with several levels | =IFS(B2>=90,"A",B2>=80,"B",TRUE,"F") |
| Look up a value | =VLOOKUP(F2,$A$2:$D$20,3,FALSE) |
| Look up in any direction | =XLOOKUP(F2,$A$2:$A$20,$D$2:$D$20) |
| Complete years between dates | =DATEDIF(A2,B2,"Y") |
| Remove duplicates | =UNIQUE(A2:A20) |
| Rank a score | =RANK.EQ(B2,$B$2:$B$20,0) |
The generator also builds AVERAGE, COUNT, MAX and MIN, ROUND, percentages and percentage change, INDEX and MATCH, TEXTJOIN, TEXTSPLIT and conditional formatting rules.
Writing conditions
SUMIF and COUNTIF take a condition, called criteria. Microsoft's guidance is that text, and any condition that uses symbols such as > or <, must be in double quotation marks, while a plain number doesn't need them. So a condition is written as ">55" or "apples", but 32 needs no quotes. To compare with a cell, join the symbol to it with &, as in "<>"&B4. You can use wildcards: a question mark matches any single character and an asterisk any sequence of characters. The generator adds the quotes and & for you.
VLOOKUP, XLOOKUP or INDEX and MATCH?
- VLOOKUP looks for a value in the first column of a table and returns a value from a column to its right, counted by number. Use FALSE for an exact match; TRUE, an approximate match, needs the first column sorted.
- XLOOKUP searches one column and returns from another, whichever side it is on, and finds exact matches by default. It isn't available in Excel 2016 or Excel 2019.
- INDEX and MATCH together do the same job as XLOOKUP and work in older versions.
Nested IF or IFS?
To give grades or levels, you can nest IF functions inside each other, or use IFS, which checks conditions in order and returns the result for the first one that is true. IFS is easier to read and works in Office 2019 and Microsoft 365. The generator gives both, ending IFS with TRUE so every value gets a result. Put the levels in order from highest to lowest, as the first true condition wins.
Dates
Excel stores dates as numbers, so subtracting one date from another gives the days between them. For complete months or years, such as an age, DATEDIF counts them with the units "M" and "Y". Microsoft keeps DATEDIF to support older workbooks and notes it can give wrong results in some cases, mainly with the "MD" unit, which this tool doesn't use.
Conditional formatting formulas
A conditional formatting rule can use a formula that returns TRUE or FALSE. Select the cells, choose New Rule, then Use a formula to determine which cells to format, and paste the formula, which must start with an equal sign. A $ before the column letter, as in =$B2>100, keeps every cell in the row checking the same column.
To combine spreadsheets, use merge Excel files, and to convert between Excel and CSV, the Excel converter.
Worked examples from the Excel formula generator
Here is what the generator writes for a few common jobs. Each formula is exactly what the tool shows for these entries.
- Percentage change. Choose Percentage or % change, set Calculate to % change from old to new, and enter B2 as the Old value and C2 as the New value. You get =(C2-B2)/B2. Format the cell as a percentage so 0.25 shows as 25%.
- Count cells that contain a word. Choose COUNTIF, set the condition to contains and type phone. You get =COUNTIF(A2:A50,"*phone*"). The asterisks match any text before and after the word.
- Flag a note that mentions a word. Choose IF, set the condition to contains urgent, show Call if true and leave the other box empty. You get =IF(ISNUMBER(SEARCH("urgent",C2)),"Call","").
- Grades with three levels. Enter 90 for A, 75 for B and 60 for C, with Retake for anything lower. The IFS version is =IFS(B2>=90,"A",B2>=75,"B",B2>=60,"C",TRUE,"Retake").
Commas or semicolons between arguments
Depending on its settings, your copy of Excel may show semicolons between arguments instead of commas. If a pasted formula gives an error, choose Semicolon under Argument separator and copy it again. The generator only swaps the commas between arguments. A comma inside quoted text stays as it is, so =IF(B2>=50;"Pass, well done";"Fail") keeps the comma in its message.
Common formula mistakes
- Typing a value where a cell is needed. Boxes such as Value to find expect an address like F2. The tool tells you when an entry isn’t a cell or range.
- Counting the VLOOKUP column wrongly. The first column of the table is 1, even if the table starts in column C.
- Grade levels in the wrong order. Put the highest level first. The first condition that is true wins.
What next
Use this often? Add Excel Formul to your Home screen.
Frequently asked questions
How do I use the Excel formula generator?
Choose a task, type the cells or ranges your data is in, and copy the formula it builds into a cell in Excel.
Why does my formula show an error in Excel?
Check that the cell addresses match your sheet. If your Excel uses semicolons between arguments, switch the separator to Semicolon and copy the formula again.
What is the difference between VLOOKUP and XLOOKUP?
VLOOKUP looks in the first column of a table and returns from a column to its right. XLOOKUP can return from either side and finds exact matches by default, but isn’t in Excel 2016 or 2019.
Can the Excel formula generator make a nested IF formula?
Yes. Choose Grades or levels, enter each level from highest to lowest, and it gives both a nested IF and an IFS formula.
Why does the generator add $ signs to my range?
The $ signs fix the lookup or rank range, so it doesn’t move when you copy the formula down a column. The cell you look up, such as F2, stays relative and moves with each row.
Can I compare with a value in another cell?
Yes. Type the cell address, such as G1, as the value. For SUMIF and COUNTIF the generator joins the comparison sign to the cell with an ampersand, so you can change the limit in that cell later.
Sources
Was this tool helpful?
What went wrong?
We only receive the reason you pick, never what you typed into the tool.
Thanks for your feedback.