OmniDocAdvanced Excel Formulas List With Examples
A browsable guide to advanced Excel formulas grouped by task, with worked examples showing how each one works in a real sheet.
Searching for an advanced Excel formulas list with examples usually means you have a sheet to fix and need to know which function to reach for. A printable cheat sheet tells you the names. It rarely shows how a formula sits inside a real workbook, what the arguments refer to, or why the result came out wrong. This guide groups the formulas by task and shows each one working against a sample sales sheet you can rebuild in a few minutes.
The examples assume a simple layout: column A holds the region, column B the sales rep, and column C the order amount, with data in rows 2 through 100. Change the ranges and text to match your own data. Where a formula needs a newer version of Excel, the text says so.
If your source table lives in a PDF, you can pull it into Excel first. The last section of this article covers that step.
Which Excel formula fits your task? The short answer
Start with the question you are trying to answer, then pick the function that matches it. Lookups, conditional totals, and text or date cleanup each have a small set of functions that cover most cases.
| Your situation | What to do | Why |
|---|---|---|
| You need one value from another table by matching a key | Use XLOOKUP (Excel 2021 or a current subscription edition), or INDEX with MATCH in older versions | Both find the row by a key and return a value from another column |
| You need a total or count that depends on one or more conditions | Use SUMIFS, COUNTIFS or AVERAGEIFS | They take multiple criteria pairs and avoid helper columns |
| A calculation sometimes divides by zero or hits a blank cell | Wrap the calculation in IFERROR, or test the input with IF first | It keeps the sheet readable, but only after you confirm the cause of the error |
| You want a filtered or sorted list that updates with the data | Use FILTER, SORT or UNIQUE (Excel 2021 or a current subscription edition) | They return arrays that spill into neighboring cells |
| You need to combine or reformat text and dates | Use TEXTJOIN, CONCAT, TEXT, EOMONTH or NETWORKDAYS | Each one handles a specific text or date job without manual edits |
What the main formula groups do
Excel functions fall into a few broad families. Knowing the family tells you what arguments to expect before you open the function list.
Lookup and reference formulas
Lookup functions search one range for a value and return a matching value from another range. XLOOKUP takes the lookup value, the range to search, the range to return, and an optional text to show when nothing matches:
=XLOOKUP(F2, A2:A100, C2:C100, "Not found")
This searches column A for the value in F2 and returns the matching amount from column C. In Excel 2019 or earlier, the same job uses INDEX and MATCH:
=INDEX(C2:C100, MATCH(F2, A2:A100, 0))
The 0 in MATCH forces an exact match. Leaving it out can return a near match, which is a common source of wrong results.
Conditional and aggregate formulas
These functions apply a calculation only to rows that meet a condition. SUMIFS takes the sum range first, then pairs of criteria ranges and criteria:
=SUMIFS(C2:C100, A2:A100, "East", B2:B100, "Lee")
This adds the amounts where the region is East and the rep is Lee. COUNTIFS and AVERAGEIFS work the same way. To count orders above 1,000:
=COUNTIFS(C2:C100, ">1000")
For conditions that are hard to express as criteria pairs, SUMPRODUCT multiplies arrays and sums the result. This works in older versions too:
=SUMPRODUCT((A2:A100="East")*(C2:C100))
Text, date and logic formulas
IFS checks several conditions in order and returns the first match, which is easier to read than nested IF statements. Excel 2019 or later supports it:
=IFS(C2>=5000, "Gold", C2>=1000, "Silver", TRUE, "Standard")
TEXTJOIN combines values from a range with a separator and can skip blanks. EOMONTH returns the last day of a month, and NETWORKDAYS counts working days between two dates. These cover reporting tasks that otherwise need manual cleanup.
Common mix-ups to avoid
Relative versus absolute references. When you fill a formula down, Excel shifts relative references. If your tax rate sits in cell H1 and your formula uses it as H1, the reference drifts to H2, H3 and so on. Write it as $H$1 to lock it. A quick check: after filling down, click a few cells and confirm each one points where you expect.
VLOOKUP and XLOOKUP are not interchangeable in every case. VLOOKUP only looks to the right of the key column and needs a column index number, which breaks when someone inserts a column. XLOOKUP reads the return range directly, so it survives those edits. Older files still use VLOOKUP, so keep it in mind when you read other people's sheets.
COUNT, COUNTA and COUNTIF count different things. COUNT counts numbers only. COUNTA counts non-empty cells, including text. COUNTIF counts cells that match one criterion. A column with dates stored as text will look empty to COUNT, which often explains a total that seems too low.
IFERROR hides more than you think. It catches every error, including a #NAME? error caused by a misspelled function or a #REF! error caused by a deleted range. Use it when you know a blank or zero is the expected case, not as a default wrapper.
📊 At a glance
| Formula | Use it to | Example | Version needed |
|---|---|---|---|
| XLOOKUP | Return a value matched by key | =XLOOKUP(F2, A2:A100, C2:C100) | Excel 2021 or a current subscription edition |
| INDEX with MATCH | Same lookup in older versions | =INDEX(C2:C100, MATCH(F2, A2:A100, 0)) | Any version |
| SUMIFS | Total with several conditions | =SUMIFS(C2:C100, A2:A100, "East") | Excel 2007 or later |
| COUNTIFS | Count rows that meet conditions | =COUNTIFS(C2:C100, ">1000") | Excel 2007 or later |
| IFS | Several conditions in order | =IFS(C2>=5000, "Gold", TRUE, "Other") | Excel 2019 or later |
| FILTER | List rows that match a condition | =FILTER(A2:C100, C2:C100>1000) | Excel 2021 or a current subscription edition |
| TEXTJOIN | Join text with a separator | =TEXTJOIN(", ", TRUE, A2:A5) | Excel 2019 or later |
Putting it to use
If your data is already in a workbook, skip to step 3. If it sits in a PDF table, follow these steps.
- Check whether the PDF has selectable text. Try selecting a line of the table with your cursor. If the text highlights, the PDF is digital. If it does not, the file is scanned and needs OCR before export.
- Download the OmniDoc desktop client at download.omnidoc.top. Open the PDF in its PDF editor. If the file is scanned, run OCR first, then export the table to Excel.
- Open the exported workbook and check that each column landed where you expect. Numbers that came in as text will not sum, so fix them before writing formulas. Compare a few rows against the original PDF.
- Write one lookup or SUMIFS formula in a blank cell. Test it against a row you can verify by hand.
- Add $ to any reference that should stay fixed, then fill the formula down the column.
- Read the result against the source once more. OCR and export can misread characters, so treat the sheet as a draft until you have checked the figures that matter.
🎯 Which approach fits you?
If you only need a few lookups and your version supports it, XLOOKUP keeps formulas short and stable when columns move. If you share the file with people on older versions, INDEX with MATCH works everywhere. If you mostly total and count, SUMIFS and COUNTIFS cover the job without helper columns. If you work with lists that change often, FILTER and UNIQUE update automatically but need a newer version.
❓ FAQ
1. Is there a complete list of every Excel formula?
Excel's built-in function library lists every function available in your version, grouped by category. The list depends on the version, so functions added in newer subscription editions will not appear in Excel 2016. Check the library in your own copy before you rely on a function from a guide.
2. Why does my formula show #NAME??
Excel shows #NAME? when it does not recognize the function name. Check for typos, and confirm that the function exists in your version. A function like XLOOKUP or FILTER will show this error in older releases.
3. How can I see which formula a cell uses?
Select the cell and read the formula bar above the grid. Double-clicking a cell or pressing F2 shows the formula inside the cell, with referenced ranges colored so you can trace them.
4. Why does my SUMIFS return 0?
A common cause is a mismatch between the criteria and the data. Text with an extra space, a number stored as text, or a date in a different format will not match. Compare the criteria cell to a value in the column, character by character.
📝 Summary
Most of the formulas you need for real work fall into lookups, conditional totals, and text or date cleanup. Start with the task, pick the matching function, and test it on one row before you fill it down. The advanced Excel formulas list with examples above gives you a starting set you can adapt to your own sheet.
