Excel Functions Cheat Sheet 2026: Formulas, Examples & Practice Quiz
Excel functions help you calculate totals, analyse data, apply conditions, find information and automate repetitive spreadsheet tasks. Instead of memorising hundreds of formulas, learn the most useful functions by category and keep this Excel functions cheat sheet available whenever you need to check the syntax.
- Seven essential Excel functions for everyday work.
- Math, text, logical, lookup, date, statistical and financial functions.
- Formula examples that you can try in Excel.
- Common Excel errors and ways to troubleshoot them.
- Useful Excel 365 dynamic array functions.
- 10 interactive MCQs with automatic scoring and explanations.
1. Seven Essential Excel Functions
Start with these seven functions. They cover many common tasks, including calculations, summaries, condition checking and retrieving information.
| Function | Example Formula | Purpose |
|---|---|---|
| SUM | =SUM(A1:A10) | Adds values in a range. |
| AVERAGE | =AVERAGE(B1:B10) | Calculates the arithmetic mean. |
| COUNT | =COUNT(A1:A20) | Counts cells containing numbers. |
| MIN | =MIN(C1:C15) | Returns the smallest value. |
| MAX | =MAX(C1:C15) | Returns the largest value. |
| IF | =IF(A1>100,"High","Low") | Returns a result based on a condition. |
| XLOOKUP | =XLOOKUP(A2,D2:D100,E2:E100,"Not found") | Searches for a value and returns its corresponding result. |
2. Math and Arithmetic Functions
Math functions help you calculate totals, round numbers, find remainders and perform conditional sums.
| Function | Syntax | What it does |
|---|---|---|
| SUM | =SUM(A1:A10) | Adds all numeric values in a range. |
| SUMIF | =SUMIF(A2:A20,"East",B2:B20) | Adds values that meet one condition. |
| SUMIFS | =SUMIFS(C2:C20,A2:A20,"East",B2:B20,"Sales") | Adds values that meet multiple conditions. |
| ROUND | =ROUND(A1,2) | Rounds a number to two decimal places. |
| INT | =INT(A1) | Rounds a number down to an integer. |
| MOD | =MOD(A1,B1) | Returns the remainder after division. |
| ABS | =ABS(A1) | Returns the absolute value. |
Example: SUMIFS with ROUND
Suppose column A contains department names and column C contains sales amounts. To calculate the total sales for the Sales department and round the result to two decimal places, use:
=ROUND(SUMIFS(C2:C100,A2:A100,"Sales"),2)
SUMIFS calculates the total for matching rows, and ROUND formats the numerical result to two decimal places.
3. Text Functions
Text functions help clean imported data, extract parts of text, combine values and standardise names or codes.
| Function | Example | Purpose |
|---|---|---|
| LEFT | =LEFT(A1,3) | Returns the first three characters. |
| RIGHT | =RIGHT(A1,4) | Returns the last four characters. |
| MID | =MID(A1,3,6) | Extracts six characters starting at position three. |
| LEN | =LEN(A1) | Counts characters, including spaces. |
| TRIM | =TRIM(A1) | Removes excess ordinary spaces between words and at the ends. |
| UPPER | =UPPER(A1) | Converts text to uppercase. |
| LOWER | =LOWER(A1) | Converts text to lowercase. |
| CONCAT | =CONCAT(A1," ",B1) | Combines text values. |
| TEXTJOIN | =TEXTJOIN(", ",TRUE,A1:A5) | Combines text using a delimiter and can ignore empty cells. |
4. Logical Functions
Logical functions let you test conditions and make decisions automatically. They are useful for marksheets, eligibility checks, reports and status columns.
| Function | Example | Purpose |
|---|---|---|
| IF | =IF(A1>50,"Pass","Fail") | Returns one result when true and another when false. |
| AND | =AND(A1>0,B1<100) | Returns TRUE when all conditions are true. |
| OR | =OR(A1="Yes",B1="Yes") | Returns TRUE when at least one condition is true. |
| IFS | =IFS(A1>=90,"A",A1>=80,"B",A1>=70,"C",TRUE,"D") | Checks several conditions in order. |
| IFERROR | =IFERROR(A1/B1,"Check values") | Returns an alternative result when the formula produces an error. |
| NOT | =NOT(A1="Closed") | Reverses TRUE to FALSE and FALSE to TRUE. |
Example: Check eligibility
=IF(AND(A2>=18,B2="Yes"),"Eligible","Not eligible")
This formula returns Eligible only when the age in A2 is at least 18 and B2 contains Yes.
5. Lookup and Reference Functions
Lookup functions retrieve information from a table. They are useful for finding employee details, product prices, student marks and records by ID.
| Function | Example | Purpose |
|---|---|---|
| XLOOKUP | =XLOOKUP(A2,D2:D100,E2:E100,"Not found") | Finds a match and returns a corresponding value. |
| VLOOKUP | =VLOOKUP(A2,D2:F100,2,FALSE) | Searches the first column of a table and returns a value from a column to its right. |
| HLOOKUP | =HLOOKUP(A1,B1:G3,2,FALSE) | Searches the first row and returns a value from a specified row. |
| INDEX | =INDEX(B2:B100,5) | Returns a value at a specified position. |
| MATCH | =MATCH(A2,D2:D100,0) | Returns the position of an exact match. |
| CHOOSE | =CHOOSE(2,"Jan","Feb","Mar") | Returns the second item, Feb, from the list. |
XLOOKUP vs VLOOKUP
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Default matching | Approximate if the last argument is omitted | Exact match by default |
| Lookup direction | Returns values from columns to the right of the first table column | Can return values from either side |
| Not-found result | Usually handled with IFERROR | Can be specified in the fourth argument |
| Older Excel compatibility | Available in many older versions | Not available in Excel 2019 or Excel 2021 |
For older Excel versions, INDEX and MATCH can provide a flexible alternative:
=INDEX(B2:B100,MATCH(A2,D2:D100,0))
6. Date and Time Functions
Date functions help calculate deadlines, working days, dates, elapsed time and reporting periods.
| Function | Example | Purpose |
|---|---|---|
| TODAY | =TODAY() | Returns the current date. |
| NOW | =NOW() | Returns the current date and time. |
| DATE | =DATE(2026,3,15) | Creates a date from year, month and day. |
| DATEDIF | =DATEDIF(A1,B1,"D") | Calculates the difference in days; other units include months and years. |
| EOMONTH | =EOMONTH(A1,0) | Returns the last day of the same month as A1. |
| WEEKDAY | =WEEKDAY(A1,2) | Returns a weekday number, with Monday as 1. |
| NETWORKDAYS | =NETWORKDAYS(A1,B1) | Counts working days, excluding weekends. |
To exclude a list of holidays as well, use the third argument of NETWORKDAYS:
=NETWORKDAYS(A1,B1,H1:H10)
Here, H1:H10 contains holiday dates. The start date and end date are included when they fall on working days.
7. Statistical Functions
Statistical functions summarise data, identify typical values, count matching records and rank results.
| Function | Example | Purpose |
|---|---|---|
| AVERAGE | =AVERAGE(A1:A50) | Calculates the mean. |
| MEDIAN | =MEDIAN(A1:A50) | Returns the middle value. |
| MODE.SNGL | =MODE.SNGL(A1:A50) | Returns the most frequently occurring value. |
| STDEV.S | =STDEV.S(A1:A50) | Estimates standard deviation for a sample. |
| COUNTIF | =COUNTIF(A1:A50,">100") | Counts cells meeting one condition. |
| COUNTIFS | =COUNTIFS(A1:A50,">100",B1:B50,"Active") | Counts rows meeting multiple conditions. |
| RANK.EQ | =RANK.EQ(A2,$A$2:$A$50,0) | Ranks a number; 0 ranks highest first. |
| LARGE | =LARGE(A1:A50,2) | Returns the second-largest value. |
| SMALL | =SMALL(A1:A50,2) | Returns the second-smallest value. |
AVERAGE vs MEDIAN: AVERAGE can be pulled upward or downward by extreme values. MEDIAN identifies the middle value and may better represent a typical result when a dataset contains outliers.
8. Financial Functions
Financial functions are useful for loan payments, investment analysis, interest rates and depreciation calculations.
| Function | Example | Purpose |
|---|---|---|
| PMT | =PMT(0.06/12,60,-20000) | Calculates a periodic loan payment. |
| PV | =PV(rate,nper,pmt) | Calculates present value. |
| FV | =FV(rate,nper,pmt,pv) | Calculates future value. |
| NPV | =NPV(rate,value1,value2,...) | Calculates net present value of periodic cash flows. |
| IRR | =IRR(A1:A10) | Estimates the internal rate of return. |
| RATE | =RATE(nper,pmt,pv) | Calculates the interest rate per period. |
| SLN | =SLN(cost,salvage,life) | Calculates straight-line depreciation per period. |
9. Useful Nested Formula Patterns
Combining functions can solve practical problems without creating long chains of manual calculations.
| Task | Formula |
|---|---|
| Lookup with fallback text | =IFERROR(XLOOKUP(A2,D:D,E:E),"Missing") |
| Check multiple conditions | =IF(AND(A1>0,B1="Active"),"Eligible","Not eligible") |
| Sum values in a date range | =SUMIFS(C:C,A:A,">="&DATE(2026,1,1),A:A,"<="&DATE(2026,3,31)) |
| Create a formatted code | =CONCAT(LEFT(A1,3),"-",TEXT(B1,"000")) |
| Lookup using two conditions in modern Excel | =XLOOKUP(1,(A2:A100=D1)*(C2:C100=E1),B2:B100,"Not found") |
These formulas are examples. Change the cell references and criteria to match your actual data. Avoid using entire columns in complex array calculations if a reasonably sized range will work.
10. Common Excel Errors and Their Fixes
| Error | Common Cause | Possible Fix |
|---|---|---|
| #N/A | A lookup cannot find the requested value. | Check spelling, spaces, data types and lookup ranges. |
| #VALUE! | The formula receives an unexpected data type or argument. | Check whether numbers are stored as text and review the arguments. |
| #REF! | A cell reference is invalid, often after deleting a referenced row or column. | Repair the formula reference. |
| #DIV/0! | A formula divides by zero or an empty denominator. | Check the denominator before dividing. |
| #NAME? | A function or named range is misspelled or unavailable. | Check spelling, Name Manager and Excel version compatibility. |
| #NUM! | A numeric calculation or argument is invalid. | Check input values and mathematical restrictions. |
Examples of error prevention
Handle a missing lookup result:
=IFERROR(XLOOKUP(A2,D:D,E:E),"Not found")
Prevent division by zero:
=IF(B1=0,"N/A",A1/B1)
Convert a number stored as text when appropriate:
=VALUE(A1)
IFERROR can make a report cleaner, but it should not be used to hide every possible error without checking the underlying cause.
Use Formula Auditing tools
- F2: enter edit mode for the selected formula cell.
- Trace Precedents: identify cells used by the formula.
- Trace Dependents: identify formulas that use the selected cell.
- Evaluate Formula: inspect how Excel evaluates parts of a formula.
These tools are available under the Formulas tab in supported desktop versions of Excel. They are particularly helpful when a formula returns a result but the result is not what you expected.
11. Excel 365 Functions to Learn in 2026
Modern Excel includes dynamic array functions that can return multiple results from a single formula. In supported versions, results can spill into neighbouring cells automatically.
| Function | Example | Purpose |
|---|---|---|
| FILTER | =FILTER(A2:C100,B2:B100="West","No results") | Returns rows meeting a condition. |
| UNIQUE | =UNIQUE(A2:A100) | Returns distinct values. |
| SORT | =SORT(A2:B50,2,-1) | Sorts a range by its second column, descending. |
| SEQUENCE | =SEQUENCE(10,1,1,1) | Generates a sequence from 1 to 10. |
| XLOOKUP | =XLOOKUP(A2,D2:D100,E2:E100,"Not found",0) | Looks up a value using exact matching. |
You can combine dynamic array functions. For example, this returns a sorted list of unique values:
=SORT(UNIQUE(B2:B500))
Dynamic arrays can reduce repetitive formulas and helper columns. Make sure the cells where results need to spill are empty, or Excel may return a #SPILL! error.
12. Excel Keyboard Shortcuts
| Shortcut | Action |
|---|---|
| = | Begins entering a formula. |
| Tab | Accepts a highlighted autocomplete suggestion while entering a formula. |
| Shift + F3 | Opens the Insert Function dialog in supported desktop versions. |
| Ctrl + ` | Toggles Show Formulas in supported Windows desktop versions. |
| Ctrl + Shift + Enter | Enters certain legacy array formulas in older Excel versions; many modern dynamic array formulas do not require it. |
| F2 | Edits the selected cell's formula. |
13. How to Learn Excel Functions Faster
- Start with the basics: practise SUM, AVERAGE, COUNT, MIN and MAX.
- Learn conditions: use IF, AND, OR, COUNTIF and SUMIF with sample data.
- Practise lookups: try XLOOKUP, then learn VLOOKUP and INDEX plus MATCH for compatibility.
- Use real datasets: practise on sales, attendance, marksheets or inventory tables.
- Debug your own mistakes: check ranges, criteria, argument order and data types.
- Use this cheat sheet as a reference: look up syntax when needed instead of trying to memorise every function.
=A1*B1 instead of =A1*1.08 when the rate can change. Keep the rate in a separate cell so it can be updated in one place.
14. Frequently Asked Questions
What are the seven basic Excel functions?
A useful starting set is SUM, AVERAGE, COUNT, MIN, MAX, IF and XLOOKUP. These cover arithmetic, summaries, conditional logic and lookup tasks. The most useful functions can vary by job and Excel version.
What are ten useful Excel functions?
A practical set includes SUM, IF, SUMIFS, XLOOKUP, COUNTIFS, COUNT, LET, FILTER, UNIQUE and TEXTBEFORE. Some of these require newer Excel versions, so check compatibility before using them in shared workbooks.
What are twenty useful Excel functions?
A broad starter list is SUM, SUMIFS, AVERAGE, COUNT, COUNTIFS, MIN, MAX, IF, IFS, IFERROR, XLOOKUP, INDEX, MATCH, FILTER, UNIQUE, SORT, TODAY, NETWORKDAYS, TRIM and CONCAT. Learn them according to the tasks you need to perform.
What is the golden rule of Excel formulas?
Use cell references for values that may change. This makes formulas easier to update, maintain and audit. Hardcoded constants can still be appropriate when they are genuinely fixed and clearly understood.
How should I use an Excel functions cheat sheet?
Use it as a quick syntax reference. First identify the task, choose the appropriate function, check the argument order, then test the formula on sample data. Practice is what builds speed and accuracy.
Are all Excel functions available in every version?
No. Some functions, including XLOOKUP and dynamic array functions such as FILTER and UNIQUE, are unavailable in certain older versions. Confirm the target Excel version before sharing a workbook.
15. ExamKosh Practice Quiz: 10 MCQs
Test what you have learned. Select one answer for each question and click Check My Score. The quiz will calculate your score and show explanations for all answers.
Q1. Which function adds values in a range?
Q2. Which function calculates the arithmetic mean?
Q3. What does =IF(A1>50,"Pass","Fail") do?
Q4. Which function counts cells containing numbers?
Q5. Which function is useful for removing extra spaces from text?
Q6. Which function supports lookup in either direction and defaults to exact matching?
Q7. Which function counts working days between two dates?
Q8. Which function counts cells that meet one condition?
Q9. Which function returns rows that meet a specified condition in supported Excel versions?
Q10. What does the #DIV/0! error usually indicate?
Quiz Answer Key
Answers: 1-B, 2-C, 3-A, 4-D, 5-B, 6-C, 7-A, 8-D, 9-B, 10-C.
Try answering all questions before checking the answer key. Read the explanations for any mistakes and attempt the quiz again.
Conclusion
Excel functions become easier to use when you understand their purpose and learn them by category. Begin with SUM, AVERAGE, COUNT, MIN, MAX, IF and XLOOKUP, then practise text, logical, date, statistical and financial functions as needed.
Keep this Excel Functions Cheat Sheet 2026 as a quick reference, test formulas on real data and learn how to fix errors instead of simply hiding them. Regular practice will help you build faster, clearer and more reliable spreadsheets.
ExamKosh Practice Tip: Save this guide and practise at least five formulas in Excel today. Complete the quiz to check your understanding.
Comments
Post a Comment