Article Summary
An Excel formulas cheat sheet is a quick-reference tool listing key functions, syntax, and examples for fast, practical use. This article covers 50 essential formulas across date/time, logic, lookup, math, and financial categories, plus keyboard shortcuts and cell reference types. You'll gain the formula fluency needed to work faster and smarter in Excel.
Looking for a comprehensive Excel cheat sheet? You’re in the right place. This guide covers 50+ essential formulas and shortcuts that will help you work faster and smarter in Microsoft Excel.
All Excel formulas start with an equals sign (=). From there, you can build everything from simple sums to complex financial calculations. This cheat sheet organizes the most important functions into categories:
- Math
- Logic
- Lookup
- Date/time
- Financial
- New Microsoft 365 Functions
Whether you’re a beginner learning the basics or an experienced user looking for a quick reference, bookmark this page. And when you’re ready to go deeper, brush up on your skills with our Advanced Excel article.
Essential Excel keyboard shortcuts
Shortcuts save you time by eliminating the need to search through menus. Instead of manually copying and pasting, just press Ctrl+D. Once you memorize these, you’ll work noticeably faster.
Here are the most useful shortcuts organized by category:
Navigation shortcuts
| Function | Shortcut |
| Go to beginning of worksheet | Ctrl+Home |
| Go to last used cell | Ctrl+End |
| Move to edge of data region | Ctrl+Arrow keys |
| Jump to last used cell in column | Double-click cell border |
| Display active cell | Ctrl+Backspace |
| Move to next worksheet | Ctrl+Page Down |
| Move to previous worksheet | Ctrl+Page Up |
The double-click trick is one most people don’t know: double-click the border of a cell to instantly jump to the last used cell in that column or row.
Data entry and editing shortcuts
| Function | Shortcut |
| Insert current date | Ctrl+; |
| Insert current time | Shift+Ctrl+; |
| Edit cell comment | Shift+F2 |
| Display Active Cell | ctrl+backspace |
| Insert Column | alt+lC |
| Insert Row | alt+lR |
| Fill down | Ctrl+D |
| Fill right | Ctrl+R |
| AutoSum | Alt+= |
| Insert column | Alt+I, C |
| Insert row | Alt+I, R |
| Insert chart | Alt+F1 |
| Save workbook | Ctrl+S |
| Undo | Ctrl+Z |
| Redo | Ctrl+Y |
| Copy | Ctrl+C |
| Paste | Ctrl+V |
| Cut | Ctrl+X |
| Find | Ctrl+F |
| Replace | Ctrl+H |
| Select all | Ctrl+A |
| Bold | Ctrl+B |
| Italic | Ctrl+I |
Table Shortcuts
| Format List as Table | Ctrl + T |
| Toggle Filters | Ctrl + Shift + L |
| Remove Dups | Alt + A + M |
| Open Filter dropdown in header | Alt + Down Arrow |
For more shortcuts, check out our complete article on Excel Keyboard Shortcuts.
Basic Excel formulas everyone should know
These are the formulas you’ll use most often. Master these seven, and you can handle the majority of spreadsheet tasks.
The 7 basic Excel formulas:
- SUM — adds numbers together
- AVERAGE — calculates the mean of a range
- COUNT — counts cells containing numbers
- MIN — finds the smallest value
- MAX — finds the largest value
- IF — returns different values based on a condition
- VLOOKUP — searches for data in a table
| Function | Syntax | Description | Example |
| SUM | SUM(range) | Adds all numbers in a range | =SUM(A1:A10) |
| AVERAGE | AVERAGE(range) | Returns the mean of a range | =AVERAGE(B1:B20) |
| COUNT | COUNT(range) | Counts cells with numbers | =COUNT(C1:C50) |
| MIN | MIN(range) | Returns the smallest value | =MIN(D1:D100) |
| MAX | MAX(range) | Returns the largest value | =MAX(D1:D100) |
| IF | IF(condition, true, false) | Returns one value if true, another if false | =IF(A1>10,”Yes”,”No”) |
| COUNTIF | COUNTIF(range, criteria) | Counts cells meeting a condition | =COUNTIF(A1:A10,”>5″) |
| SUMIF | SUMIF(range, criteria) | Sums cells meeting a condition | =SUMIF(A1:A10,”>5″) |
| NOT | NOT(condition 1) | The “NOT” function will run a statement and return true or false. It can also be used as a nested function within an IF function. | |
| IFERROR | IFERROR(argument, error_note) | The “IFERROR” function is a way to debug your logic. You send an argument and what should be displayed if that argument produces an error. |
There’s also the SUMIFS function article for summing with multiple criteria.
If you’re looking to master Microsoft Excel formulas and functions, check out this Udemy course. It includes an introduction to using ChatGPT with Excel.
Understanding cell references
Most formulas require a cell reference. How you define that reference affects how the formula behaves when copied.
| Reference Type | Example | Behavior |
| Relative | =A2+B2 | Changes when copied (A3+B3, A4+B4, etc.) |
| Absolute | =$A$1 | Stays fixed when copied |
Relative cell reference: Refers to a cell address that automatically adjusts when you copy a formula. If you copy =A1+B1 down one row, it becomes =A2+B2.
Absolute cell reference: Use the “$” symbol to lock a reference. The formula =$A$1+B1 will always reference A1, even when copied to other cells.
Math and statistical functions
| Function | Syntax | Description |
| ABS | ABS(value) | Returns the absolute value (both 1 and -1 return 1) |
| MEDIAN | MEDIAN(range) | Returns the middle value in a range |
| PI | PI() | Returns the value of pi |
Excel lookup functions
Need to find data in a large spreadsheet? Lookup functions search through your data and return matching values. VLOOKUP is among the most searched Excel functions for good reason.
| Function | 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(lookup_value, lookup_location, column_return, approx) | Searches vertically for a value and returns data from a specified column. Learn more about VLOOKUP. |
| HLOOKUP | HLOOKUP(lookup_value, lookup_location, column_return, approx) | Works like VLOOKUP but searches horizontally. Learn more about HLOOKUP. |
| INDEX | INDEX(array, row, column) | Returns a value at a specific position in a table. |
| MATCH | MATCH(value, array, match_type) | Returns the position of a value in a range. Learn more about the MATCH function. |
INDEX and MATCH combination
Advanced users often prefer INDEX and MATCH over VLOOKUP. Why? VLOOKUP can only search leftward and return values to the right. INDEX and MATCH work in any direction.
For example, to find a product price where the price column is to the left of the product name:
=INDEX(A2:A100, MATCH(“Widget”, C2:C100, 0))
This searches column C for “Widget” and returns the corresponding value from column A.
Excel logic functions
Logic functions are where Excel gets really powerful. Most programming languages have similar logic functions. Understanding these will help you build dynamic workbooks.
If you’re having trouble with logic functions, try mapping them out on an index card first.
| Function | Syntax | Description |
| IF | IF(statement, true, false) | Tests a condition and returns one value if true, another if false. |
| OR | OR(condition 1, condition 2) | Returns TRUE if any condition is met. Can be nested in IF. |
| AND | AND(condition 1, condition 2) | Returns TRUE only if all conditions are met. |
| NOT | NOT(condition 1) | Reverses the logic of its argument. |
| IFERROR | IFERROR(argument, error_note) | Returns a custom value if a formula produces an error. |
Nested IF example:
=IF(AND(A1>10, B1<5), “Yes”, “No”)
This returns “Yes” only if A1 is greater than 10 AND B1 is less than 5.
Date, time, and financial functions
Date and time functions
Your computer already knows what day it is. Excel’s date and time functions let you use that information in your calculations.
Note: Excel stores dates as “serial numbers” internally. These functions often return serial numbers, which you can format as readable dates.
| Function | Syntax | Description |
| DATE | DATE(year, month, day) | Creates a date from year, month, and day values. |
| DATEDIF | DATEDIF(start_date, end_date, unit) | Calculates the time between two dates. |
| DAY | DAY(serial number) | Returns the day of a date (1-31). |
| TODAY | TODAY() | Returns today’s date. |
| NOW | NOW() | Returns the current date and time. |
| YEAR | YEAR(date) | Extracts the year from a date. |
| EDATE | EDATE(start_date, months) | Adds months to a date. |
| SUM | SUM(range) | One of the most commonly used functions, the SUM function will combine multiple cells in a range into a single sum. |
| EOMONTH | EOMONTH(start_date, months) | Returns the last day of a month, X months from the start date. |
Financial functions
Excel is widely used in finance, so it includes powerful built-in financial functions. These are commonly used for loan calculations, investment analysis, and depreciation.
| Function | Syntax | Description |
| NPV | NPV(rate, range_of_cashflows) | Calculates net present value of an investment. |
| PMT | PMT(rate, number_of_payments, present_value, [future_value], [eob]) | Returns the periodic payment for a loan. |
| 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_of_return, number_of_payments, payments_made, [future_payments], [eob]) | Calculates the future value of an investment. |
| PV | PV(rate, number_of_payments, payment, [future_value], [eob]) | Returns the present value of an investment. |
| IRR | IRR(values, [estimate]) | Calculates the internal rate of return for cash flows. |
| IPMT | IPMT(interest_rate, payment_period, number_of_payments, present_value, [desired_cash_balance], [eob]) | Returns the interest portion of a loan payment. |
| IRR | IRR(values, [estimate]) | IRR is a function that returns the Internal Rate of Return for a series of cash flows. |
| 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. |
| NPV | NPV(rate, range_of_cashflows) | NPV is one of the most commonly used financial functions. It calculates the net present value of a loan or an investment. |
| PMT | PMT(rate, number_of_payments, present_value, [future_value], [eob]) | This function will return the amount of periodic repayment for a loan; how much needs to be paid back regularly for a given loan. |
| PV | PV(rate, number_of_payments, payment, [future_value], [eob]) | This function returns the current value of an investment, given parameters about that investment. |
| 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(settlement_date, maturity_date, rate, price, redemption, frequency, basis) | The YIELD function returns the amount that has been yielded on a security with periodic interest. |
New Excel Functions (Microsoft 365)
| Function | Syntax | Description |
| TEXTSPLIT | TEXTSPLIT(text, col_delimeter, [row_delimeter], [ignore_empty[, [match_mode], [pad_width]) | Splits text into rows or columns using delimiters, for example, splitting a value such as; 123-ABC, into two columns based on the “-“ delimiter. |
| TEXTJOIN | TEXTJOIN(delimiter, ignore_empty, text_1, [text2],…) | Concatenates a list or range of text strings using a delimiter. |
| VSTACK | VSTACK(array1, [array2],…) | Vertically stacks arrays into one array. |
| XLOOKUP | XLOOKUP(lookup_value, look_array, return_array, [if_not_found], [match_mode]) | Searches a range of an array for a match and returns the corresponding item from the second range or array. Use the XLOOKUP to replace VLOOKUP or INDEX/MATCH functions. |
| UNIQUE | UNIQUE(array, [by_col],[exactly_once]) | Returns the unique values from a range or array. |
| GROUPBY | GROUPBY(row_fields, values, function, [field_headers], [total_depth]) | Aggregate values by row fields. This function allows you to group data and perform a summary calculation within those groups. |
| PIVOTBY | PIVOTBY(row_fields, col_fileds, values, function, [field_headers]) | Aggregate values by rows and columns. Similar to the GROUPBY function, but the PIVOTBY accepts COLUMN FIELDS as well in the summary. |
| FILTER | FILTER(array, include, [if_empty]) | Filter a range or array. |
| SORT | SORT(array, [sort_index], [sort_order], [by_col]) | Sorts an array or range. |
Take your Excel skills further
This cheat sheet covers the essentials, but Excel can do much more. Here’s how to keep learning based on your skill level:
- Beginners: Start with a structured course to build a solid foundation, like Udemy’s Excel from Beginner to Advanced course.
- Intermediate users: Explore charting functions and pivot tables to analyze data visually.
- Advanced users: Learn SQL in Excel to work with larger datasets. Start with: SQL for Excel users: A Beginner-Friendly Guide.
- Career-focused: Consider Excel certification to validate your skills for employers.
Ready to level up? Browse Excel courses on Udemy to find the right fit for your goals.
