Excel formulas really are the backbone of efficient data handling. Learn them, and you can save hours of manual work every week, turning a basic spreadsheet into a powerful decision-making tool.
Keep this cheat sheet within reach and bookmark it now. When you’re ready for more, join our Advanced Excel Course and master everything from formulas to advanced data analysis.
Understanding Excel Formulas Basics
Formula vs. Function
A formula is an expression that begins with an equals sign (=) and performs a calculation. A function is a built-in named operation, like SUM() or IF(), that you use inside a formula, kinda like a prebuilt tool.
How to Enter a Formula
- Click a cell and type = to start.
- Type the function name and open parenthesis, e.g., =SUM(
- Select your range or type values, then close with )
- Press Enter to confirm
Common Formula Errors
| Error | Meaning | Fix |
| #DIV/0! | Division by zero | Wrap with IFERROR or check denominator |
| #VALUE! | Wrong data type | Ensure numbers are not stored as text |
| #REF! | Invalid cell reference | Check if referenced cells were deleted |
| #NAME? | Unrecognized formula name | Check spelling of the function name |
| #N/A | Value not found | Use IFERROR or IFNA to handle missing values |
Essential Excel keyboard shortcuts
Shortcuts help you save time by removing the necessity to navigate through menus. Rather than copying and pasting manually, simply press Ctrl+D. Once you commit these to memory, you’ll definitely notice an increase in your speed. Below is a list of the most helpful shortcuts categorized by type:
Navigation shortcuts
| Shortcut | Action |
| Ctrl + Home | Go to cell A1 |
| Ctrl + End | Go to last used cell |
| Ctrl + Arrow Key | Jump to last cell in direction |
| Double-click cell border | Jump to last used cell in column |
| Ctrl + BackSpace | Display active cell |
| Ctrl + Page Down | Move to next worksheet |
| Ctrl + Page Up | Move to next worksheet |
Here’s a hidden gem: double-click a cell’s border to instantly jump to the last used cell in that column or row.
Data entry and editing shortcuts
| Shortcut | Action |
| Ctrl+; | Insert current date |
| Shift+Ctrl+; | Insert current time |
| Shift+F2 | Edit cell comment |
| alt+lC | Insert Column |
| alt+lR | Insert Row |
| Ctrl+D | Copy formula down |
| Ctrl+R | Copy formula to the right |
| Alt+= | AutoSum |
| Alt+I, C | Insert column |
| Alt+I, R | Insert row |
| Alt+F1 | Insert chart |
| Ctrl+S | Save workbook |
| Ctrl+Z | Undo |
| Ctrl+Y | Redo |
| Ctrl+C | Copy |
| Ctrl+V | Paste |
| Ctrl+X | Cut |
| Ctrl+F | Find |
| Ctrl+H | Replace |
| Ctrl+A | Select all |
| Ctrl+B | Bold |
| Ctrl+I | Italic |
Table Shortcuts
| Ctrl + T | Format List as Table |
| Ctrl + Shift + L | Toggle Filters |
| Alt + A + M | Remove Dups |
| Alt + Down Arrow | Open Filter dropdown in header |
Essential Excel Formulas Every User Should Know
The 7 basic Excel formulas:
- SUM: adds up a range of numbers
- AVERAGE: finds the mean value of a range
- COUNT: counts how many cells contain numbers
- MIN: returns the lowest value in a range
- MAX: returns the highest value in a range
- IF: outputs different results based on a condition
- VLOOKUP: searches and retrieves data from a table
| Formula | Syntax with Example | Description |
| SUM | =SUM(A1:A10) | Adds all values in a range |
| AVERAGE | =AVERAGE(A1:A10) | Returns the mean of a range |
| MIN | =MIN(A1:A10) | Returns the smallest value |
| MAX | =MAX(A1:A10) | Returns the largest value |
| COUNT | =COUNT(A1:A10) | Counts cells with numbers |
| COUNTA | =COUNTA(A1:A10) | Counts non-empty cells |
| ROUND | =ROUND(A1,2) | Rounds to specified decimal places |
| ROUNDUP | =ROUNDUP(A1,0) | Always rounds up |
| ROUNDDOWN | =ROUNDDOWN(A1,0) | Always rounds down |
| SUMIF | =SUMIF(range,criteria,sum_range) | Sum cells that meet a condition |
| SUMIFS | =SUMIFS(sum_range,range1,crit1,…) | Sum with multiple conditions |
| AVERAGEIF | =AVERAGEIF(range,criteria,avg_range) | Average cells meeting a condition |
| MOD | =MOD(10,3) | Returns remainder after division |
| ABS | =ABS(-5) | Returns absolute value |
| POWER | =POWER(2,3) | Raises number to a power |
| NOT | NOT(condition 1) | The NOT function evaluates a statement and returns either TRUE or FALSE. It’s also commonly used as a nested function inside an IF formula for more complex logic. |
Understanding Cell References
Most formulas rely on cell references. The way you define them directly affects how your formula behaves when copied to other cells.
| Reference Type | Example | Behavior |
| Relative | =X2+Y2 | Changes when copied (X3+Y3, X4+Y4, etc.) |
| Absolute | =$X$1 | Stays fixed when copied |
Relative Cell Reference: A reference that automatically updates when you copy a formula. For example, copying =X1+Y1 down one row changes it to =X2+Y2.
Absolute Cell Reference: Use the $ symbol to fix a reference so it never changes. For example, =$X$1+Y1 always refers to X1, even when copied to different cells.
Math and statistical functions
| Function | Syntax | Description |
| MEDIAN | MEDIAN(range) | Returns the middle value in a range |
| PI | PI() | Returns the value of pi |
Excel lookup functions
When you need to find specific data across a large spreadsheet, lookup functions are your best friend. They search through your data and return matching values instantly, which is why VLOOKUP remains one of the most popular Excel functions around.
| Formula | Syntax | Description |
| LOOKUP | LOOKUP(lookup_value, lookup_vector, [result_vector]) | Searches for a value in a range. Most users prefer VLOOKUP or HLOOKUP. |
| VLOOKUP | =VLOOKUP(val,range,col,0) | Looks up value vertically in a table |
| HLOOKUP | =HLOOKUP(val,range,row,0) | Looks up value horizontally |
| INDEX | =INDEX(range,row,col) | Returns value at given row/column |
| MATCH | =MATCH(val,range,0) | Returns position of a value in a range |
| INDEX+MATCH | =INDEX(B1:B10,MATCH(val,A1:A10,0)) | Smarter alternative to VLOOKUP |
| XLOOKUP | =XLOOKUP(val,lookup,return) | Excel 365 replaces VLOOKUP/HLOOKUP |
| OFFSET | =OFFSET(A1,2,3) | Returns a cell offset from a reference |
| INDIRECT | =INDIRECT(“A”&B1) | Returns reference from a text string |
| CHOOSE | =CHOOSE(2,”a”,”b”,”c”) | Returns value from a list by index |
Date, time, and financial functions
Date and time functions
Need to work with dates and times in your spreadsheet? Excel has a full set of built-in functions to help you calculate durations, automate schedules, and manage time-based data effortlessly.
| Formula | Syntax | Description |
| TODAY | =TODAY() | Returns today’s date |
| NOW | =NOW() | Returns current date and time |
| DATE | =DATE(2026,6,17) | Creates a date from year, month, day |
| YEAR | =YEAR(A1) | Extracts the year from a date |
| MONTH | =MONTH(A1) | Extracts the month from a date |
| DAY | =DAY(A1) | Extracts the day from a date |
| DATEIF | =DATEIF(A1, B1,”D”) | Calculates difference between dates |
| NETWORKDAYS | -NETWORKDAYS(A1,B1) | Counts working days between dates |
| WORKDAY | =WORKDAY(A1, 10) | Adds working days to a date |
| EDATE | =EDATE(A1, 3) | Adds months to a date |
| EMONTH | =EMONTH(A1,0) | Returns last day of a month |
Financial functions
When it comes to finance, Excel is in a league of its own. Its powerful built-in financial functions are designed for real-world use, covering loan calculations, investment analysis, and depreciation with ease.
| Formula | Syntax | Description |
| NPV | =NPV(rate,value1,value2,…) | Net present value of cash flows. |
| PMT | =PMT(rate,nper,pv) | Calculates loan payment amount. |
| DDB | =DDB(cost_basis, salvage_cost, life, period, [factor]) | The DDB function calculates the depreciation of an asset with a double-declining balance. |
| FV | =FV(rate,nper,pmt,pv) | Future value of an investment. |
| PV | =PV(rate,nper,pmt) | Present value of an investment. |
| IRR | =IRR(values) | Internal rate of return. |
| IPMT | =IPMT(interest_rate, payment_period, number_of_payments, present_value, [desired_cash_balance], [eob]) | Returns the interest portion of a loan payment. |
| INTRATE | =INTRATE(settlement_date, maturity_date, invested, redemption, [basis]) | INTRATE returns the interest rate for a security that’s been invested in; in other words, the rate of return. |
| SLN | =SLN(cost, salvage_value, life) | The SLN function will return depreciation on an asset for a single period, calculated on a straight line. |
| XNPV | =XNPV(rate, value_range, dates) | This function is similar to the NPV function, but it calculates it based on irregular values rather than regular values. |
| XIRR | =XIRR(values, dates, [guess]) | This function is similar to the IRR function, but it calculates it based on irregular values rather than regular values. |
| YIELD | =YIELD | The YIELD function returns the amount that has been yielded on a security with periodic interest. |
| RATE | =RATE(nper,pmt,pv) | Calculates interest rate per period. |
| NPER | =NPER(rate,pmt,pv) | Number of periods for an investment. |
Text Formulas
Working with text in Excel is easier than you think. These formulas let you split, join, clean, and transform text data in just a few clicks.
| Formula | Syntax | Description |
|
CONCATENATE |
=CONCATENATE(A1,” “,B1) |
Joins text strings together |
|
TEXTJOIN |
=TEXTJOIN(“, “,TRUE,A1:A5) |
Joins with delimiter, ignores blanks |
| LEFT |
=LEFT(A1,5) |
Extracts characters from the left |
| RIGHT |
=RIGHT(A1,4) |
Extracts characters from the right |
| MID |
=MID(A1,3,5) |
Extracts from middle of a string |
| LEN |
=LEN(A1) |
Returns length of a string |
| TRIM |
=TRIM(A1) |
Removes extra spaces |
| UPPER |
=UPPER(A1) |
Converts text to uppercase |
| LOWER |
=LOWER(A1) |
Converts text to lowercase |
| PROPER |
=PROPER(A1) |
Capitalizes first letter of each word |
| FIND |
=FIND(“x”,A1) |
Finds position of text (case-sensitive) |
| SEARCH |
=SEARCH(“x”,A1) |
Finds position (not case-sensitive) |
| SUBSTITUTE |
=SUBSTITUTE(A1,”old”,”new”) |
Replaces specific text |
| REPLACE |
=REPLACE(A1,1,3,”new”) |
Replaces by position |
Logical Formulas
Logical formulas let Excel make decisions for you. They evaluate conditions and return different results based on whether something is true or false.
| Formula | Syntax | Description |
| IF |
=IF(A1>10,”Yes”,”No”) |
Returns value based on a condition |
| IFS |
=IFS(A1>90,”A”,A1>80,”B”) |
Tests multiple conditions in sequence |
| AND |
=AND(A1>5,B1<10) |
Returns TRUE if all conditions are true |
| OR |
=OR(A1>5,B1<10) |
Returns TRUE if any condition is true |
| NOT |
=NOT(A1=0) |
Reverses a logical value |
| IFERROR |
=IFERROR(formula,”Error”) |
Returns fallback if formula errors |
| IFNA |
=IFNA(VLOOKUP(…), ‘N/A’) |
Returns fallback only for #N/A errors |
| Nested IF |
=IF(A1>90,”A”,IF(A1>80,”B”,”C”)) |
Multiple conditions in one formula |
Remaining Excel Formulas & Formatting Shortcuts
Speed up your workflow even further with these additional shortcuts. From formatting cells to navigating formulas, these are the ones worth adding to your daily routine.
| Shortcut | Action |
| Ctrl + G | Go To dialog box |
| F5 | Open Go To dialog |
| Ctrl + Tab | Switch between open workbooks |
| Ctrl + U | Underline selected text |
| Ctrl + 1 | Open Format Cells dialog |
| Ctrl + Shift + $ | Apply Currency format |
| Ctrl + Shift + % | Apply Percentage format |
| Ctrl + Shift + # | Apply Date format |
| Alt + H + H | Fill cell with color |
| Alt + H + B | Apply border |
| = (Equals) | Start a formula |
| F2 | Edit the active cell |
| F4 | Toggle absolute/relative reference ($A$1) |
| Ctrl + ` | Show all formulas in worksheet |
| Ctrl + Shift + Enter | Enter as Array formula |
| Alt + = | Auto-sum selected range |
| F9 | Recalculate all formulas |
| Shift + F3 | Insert Function dialog |
Pro Tips to Use Excel Formulas Faster
1. Use Named Ranges
Instead of typing something like =SUM( B2:B50 ) , you can name that whole range “Sales” and then just use =SUM( Sales ). When you do this, your formulas look way clearer right away, and they’re also simpler to handle, update, and maintain over time.
2. Combine Formulas for Powerful Results
Example: =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),”Not Found”) – this looks up a value and handles errors gracefully in one step.
3. Absolute vs. Relative Refere
- Relative reference: A1: changes when copied
- Absolute reference: $A$1: stays fixed when copied
- Mixed reference: $A1 or A$1: fixes column or row only
- Press F4 while editing to toggle between types
4. Use Formula Auditing Tools
- Formulas tab > Trace Precedents: see which cells feed into a formula
- Formulas tab > Trace Dependents: see which cells depend on this one
- Formulas tab > Evaluate Formula: step through a formula calculation.
Conclusion: Master Excel Formulas One Function at a Time
Learning Excel formulas is one of the best investments you can make in your professional skills. Every Excel formulas in this guide saves you real time every single day.
Start applying what you’ve learned and share this guide with a colleague. Ready to go further? Send us an inquiry, and let’s take your Excel skills to the next level together.