Excel Commands, Functions & Shortcuts Reference Guide
A comprehensive, categorized reference of Excel functions, formulas, and keyboard shortcuts for efficient data analysis and automation
Basic Functions
| Function | Description | Example Usage |
|---|---|---|
| IF | Performs a logical test and returns one value if TRUE, another if FALSE. | =IF(E1>10,"Yes","No") |
| AND | Returns TRUE if all supplied conditions are TRUE. | =AND(A1>0, B1<10) |
| OR | Returns TRUE if any condition is TRUE. | =OR(A1>10, B1>10) |
| NOT | Reverses the logical value of its argument. | =NOT(A1>5) |
| IFERROR | Returns a custom result when a formula generates an error. | =IFERROR(A1/B1, "Error") |
Text Functions
| Function | Description | Example Usage |
|---|---|---|
| CONCAT | Joins multiple text strings into one. | =CONCAT(A1,B1) |
| TEXTJOIN | Combines text from multiple ranges with a delimiter. | =TEXTJOIN(", ",TRUE,A1:A5) |
| LEFT | Returns the first N characters of a text string. | =LEFT(A1,5) |
| RIGHT | Returns the last N characters of a text string. | =RIGHT(A1,3) |
| MID | Extracts a substring from a text string. | =MID(A1,2,4) |
| LEN | Counts characters in a text string. | =LEN(A1) |
| TRIM | Removes extra spaces from text. | =TRIM(A1) |
| UPPER | Converts text to uppercase. | =UPPER(A1) |
| LOWER | Converts text to lowercase. | =LOWER(A1) |
| PROPER | Capitalizes the first letter of each word. | =PROPER(A1) |
Lookup & Reference Functions
| Function | Description | Example Usage |
|---|---|---|
| VLOOKUP | Searches for a value in the first column and returns a corresponding value from another column. | =VLOOKUP(101,A1:C10,2,FALSE) |
| HLOOKUP | Looks for a value in the first row and returns data from a specified row. | =HLOOKUP("Name",A1:F3,2,FALSE) |
| XLOOKUP | Replaces VLOOKUP with more flexibility and direction options. | =XLOOKUP("Apple",A2:A10,B2:B10,"Not Found") |
| INDEX | Returns a value based on row and column position in a range. | =INDEX(B2:D10,3,2) |
| MATCH | Returns the relative position of an item in a range. | =MATCH("Item",A1:A10,0) |
| OFFSET | Returns a reference offset from a starting cell or range. | =OFFSET(A1,2,3) |
Date & Time Functions
| Function | Description | Example Usage |
|---|---|---|
| TODAY | Returns the current date. | =TODAY() |
| NOW | Returns the current date and time. | =NOW() |
| DATE | Creates a date from year, month, and day values. | =DATE(2025,8,31) |
| DAY | Returns the day from a date value. | =DAY(A1) |
| MONTH | Returns the month from a date value. | =MONTH(A1) |
| YEAR | Returns the year from a date value. | =YEAR(A1) |
| NETWORKDAYS | Returns the number of working days between two dates. | =NETWORKDAYS(A1,B1) |
Math & Statistical Functions
| Function | Description | Example Usage |
|---|---|---|
| SUM | Adds all numbers in a range of cells. | =SUM(A1:A5) |
| AVERAGE | Calculates the arithmetic mean of a set of numbers. | =AVERAGE(B1:B10) |
| COUNT | Counts numeric values in a range. | =COUNT(C1:C10) |
| COUNTA | Counts all non-empty cells in a range. | =COUNTA(D1:D10) |
| ROUND | Rounds a number to a specified number of digits. | =ROUND(A1,2) |
| ROUNDUP | Rounds a number up, away from zero. | =ROUNDUP(A1,0) |
| ROUNDDOWN | Rounds a number down, toward zero. | =ROUNDDOWN(A1,0) |
| INT | Rounds a number down to the nearest integer. | =INT(A1) |
| MOD | Returns the remainder after division. | =MOD(A1,3) |
| POWER | Raises a number to a power. | =POWER(A1,3) |
| SQRT | Returns the square root of a number. | =SQRT(A1) |
| SUMIF | Adds cells that meet specific criteria. | =SUMIF(A1:A10,">100") |
| AVERAGEIF | Returns the average of cells that meet criteria. | =AVERAGEIF(B1:B10,">50") |
| PRODUCT | Multiplies all numbers in a range. | =PRODUCT(A1:A3) |
| ABS | Returns the absolute value of a number. | =ABS(A1) |
Common Excel Keyboard Shortcuts
| Shortcut | Action |
|---|---|
| Ctrl + C | Copy selected cells. |
| Ctrl + V | Paste copied content. |
| Ctrl + Z | Undo last action. |
| Ctrl + Y | Redo last undone action. |
| Ctrl + Shift + L | Toggle filters on or off. |
| Ctrl + Arrow Keys | Navigate to edge of data region. |
| Ctrl + ; | Insert current date. |
| Ctrl + Shift + : | Insert current time. |
| Alt + = | Automatically insert SUM formula. |
| F4 | Repeat last action or toggle absolute references. |
About Excel Functions and Formulas
This Excel functions reference provides a complete, categorized list of formulas used for calculations, data analysis, and automation in Microsoft Excel. It’s designed for engineers, analysts, students, and professionals who want to work faster and more efficiently with spreadsheets.
Applications
- Basic Functions – Perform fundamental operations such as summing, averaging, and counting cells.
- Text Functions – Clean, extract, and combine text strings for data formatting and reporting.
- Lookup & Reference Functions – Search and retrieve values dynamically using tools like
VLOOKUP,INDEX, andMATCH. - Date & Time Functions – Calculate and format dates, times, and durations for scheduling or analysis.
- Math & Statistical Functions – Handle complex numeric computations, rounding, and statistical evaluation.
By mastering these Excel functions, you can:
- Enhance accuracy and efficiency in data-driven projects
- Automate calculations and reduce manual work
- Build dynamic dashboards and analytical models
- Improve decision-making through reliable data analysis
Microsoft Excel remains one of the most powerful tools for data analysis, engineering, and business applications. This reference serves as a quick guide for understanding and applying essential Excel functions in real-world scenarios.