Microsoft Excel – Complete Commands &
Formulas Guide
📁 File Menu (Green Zone)
• Info: Protect workbook, check hidden data
• New: Blank spreadsheet or templates (budgets, calendars)
• Open: Find existing Excel files
• Save/Save As: Keep work safe, create copies, export formats
• Print: Preview sheets, choose print area
• Share: Email or OneDrive collaboration
• Export: Convert to PDF quickly
🏠 Home Tab: Daily Tools
• Copy/Cut/Paste: Move data, paste special options
• Font: Arial, Bold, Size, Colors, Borders
• Alignment: Left/Center/Right, Merge cells, Wrap text
• Number: Currency, Percentage, Date formats
• Conditional Formatting: Color cells based on rules
• Format as Table: Convert range to Excel Table
• AutoSum: Quick SUM, AVERAGE, COUNT
• Sort & Filter: Arrange and hide data
➕ Insert Tab: Add Elements
• Tables: Excel Tables, PivotTables
• Charts: Bar, Line, Pie charts from data
• Pictures: Images, Icons, Screenshots
• Shapes: Arrows, circles, text boxes
• SmartArt: Process diagrams, org charts
• Sparklines: Mini charts in cells
• Slicers: Interactive filter buttons
• Links: Hyperlinks to websites/files
📐 Page Layout Tab: Print Setup
• Themes: Professional color/font sets
• Margins: Normal, Narrow, Wide spacing
• Orientation: Portrait vs Landscape
• Print Area: Define what to print
• Page Breaks: Control page splits
• Scale to Fit: Shrink to fit pages
🔢 Formulas Tab: Calculations
• Function Library: All formulas organized by category
• AutoSum: SUM, AVERAGE, MAX, MIN
• Insert Function: Step-by-step formula builder
• Name Manager: Create named ranges
• Trace Precedents: See formula connections
• Show Formulas: View formulas instead of results
📊 Data Tab: Organize & Clean
• Get Data: Import from files, web, databases
• Sort: Ascending/Descending, multiple columns
• Filter: Hide unwanted rows
• Text to Columns: Split data into separate columns
• Remove Duplicates: Clean duplicate entries
• Data Validation: Control what can be entered
• PivotTable: Summarize large datasets
• What-If Analysis: Goal Seek, Scenarios
✅ Review Tab: Check & Protect
• Spelling: Check for typos
• Comments: Discussion bubbles on cells
• Protect Sheet: Lock cells from editing
• Track Changes: See who edited what
• Translate: Convert to other languages
👁️ View Tab: Display Options
• Normal: Standard spreadsheet view
• Page Layout: Print preview mode
• Page Break Preview: See page divisions
• Freeze Panes: Keep rows/columns visible while scrolling
• Zoom: 50%, 100%, 200% magnification
• Split: Divide window into sections
🔢 Complete Excel Formulas A-Z (All Functions)
📊 Mathematical & Statistical
• ABS: Positive value of number
• AGGREGATE: Calculation with options to ignore errors/hidden rows
• AVERAGE/AVERAGEA: Calculate average (A includes text/logical)
• AVERAGEIF/AVERAGEIFS: Average with conditions
• COUNT/COUNTA/COUNTBLANK: Count numbers/non-empty/blank cells
• COUNTIF/COUNTIFS: Count with conditions
• MAX/MAXA/MIN/MINA: Largest/smallest values
• MAXIFS/MINIFS: Max/Min with conditions
• MEDIAN: Middle number in dataset
• MODE/[Link]/[Link]: Most frequent value(s)
• PRODUCT: Multiply all numbers
• RAND/RANDARRAY/RANDBETWEEN: Generate random numbers
• RANK/[Link]/[Link]: Position in sorted list
• ROUND/ROUNDUP/ROUNDDOWN: Round numbers
• SUM/SUMIF/SUMIFS: Add numbers with/without conditions
• SUMPRODUCT: Multiply arrays and sum products
• SUMSQ: Sum of squares
• SUBTOTAL: Summary with filtering options
📅 Date & Time Functions
• DATE/TIME: Create date/time from components
• DATEVALUE/TIMEVALUE: Convert text to date/time
• DAY/MONTH/YEAR/HOUR/MINUTE/SECOND: Extract components
• DAYS/DAYS360: Calculate days between dates
• EDATE/EOMONTH: Add months to date/end of month
• NETWORKDAYS/[Link]: Workdays between dates
• NOW/TODAY: Current date/time
• WEEKDAY/WEEKNUM/ISOWEEKNUM: Week information
• YEARFRAC: Fraction of year between dates
📝 Text Functions
• CHAR/CODE: Character from/to number code
• CLEAN: Remove non-printable characters
• CONCAT/CONCATENATE: Join text strings
• EXACT: Check if text identical (case-sensitive)
• FIND/SEARCH: Locate text (FIND case-sensitive)
• LEFT/RIGHT/MID: Extract text from position
• LEN/LENB: Count characters/bytes
• LOWER/UPPER/PROPER: Change text case
• REPLACE/SUBSTITUTE: Replace text
• REPT: Repeat text specified times
• TEXT: Format number as text
• TEXTAFTER/TEXTBEFORE: Extract text around delimiter
• TEXTJOIN: Join with delimiter
• TEXTSPLIT: Split text into array
• TRIM: Remove extra spaces
• VALUE: Convert text to number
🔍 Lookup & Reference
• ADDRESS: Create cell address as text
• AREAS: Count areas in reference
• CHOOSE: Pick value from list by index
• COLUMN/COLUMNS/ROW/ROWS: Get position/count
• HLOOKUP: Horizontal lookup in table
• HYPERLINK: Create clickable link
• INDEX: Get value from table position
• INDIRECT: Reference from text string
• LOOKUP: Simple lookup function
• MATCH/XMATCH: Find position in array
• OFFSET: Reference offset from starting point
• TRANSPOSE: Switch rows/columns
• VLOOKUP: Vertical lookup in table
• XLOOKUP: Advanced flexible lookup
❓ Logical Functions
• AND/OR/NOT: Logical operators
• FALSE/TRUE: Logical values
• IF/IFS: Conditional logic
• IFERROR/IFNA: Handle errors
• SWITCH: Multiple condition matching
• XOR: Exclusive OR logic
ℹ️ Information Functions
• CELL: Cell information
• [Link]: Identify error type
• INFO: System environment info
• ISBLANK/ISERROR/ISNA: Check for blank/error/NA
• ISEVEN/ISODD: Check if even/odd
• ISFORMULA/ISLOGICAL/ISNUMBER/ISTEXT: Check data type
• TYPE: Return data type number
💰 Financial Functions
• ACCRINT/ACCRINTM: Accrued interest calculations
• CUMIPMT/CUMPRINC: Cumulative interest/principal
• DB/DDB/SLN/SYD: Depreciation methods
• DISC: Discount rate calculation
• DOLLAR/DOLLARDE/DOLLARFR: Currency formatting/conversion
• DURATION/MDURATION: Bond duration
• EFFECT/NOMINAL: Interest rate conversions
• FV/PV: Future/present value
• FVSCHEDULE: Future value with varying rates
• INTRATE: Interest rate calculation
• IPMT/PPMT: Interest/principal payment portion
• IRR/MIRR/XIRR: Internal rate of return
• NPER: Number of payment periods
• NPV/XNPV: Net present value
• PDURATION: Periods to reach target value
• PMT: Payment calculation
• PRICE/PRICEDISC/PRICEMAT: Security pricing
• RATE: Interest rate calculation
• RECEIVED: Amount at maturity
• RRI: Equivalent interest rate
• TBILLEQ/TBILLPRICE/TBILLYIELD: Treasury bill functions
• VDB: Variable declining balance depreciation
• YIELD/YIELDDISC/YIELDMAT: Yield calculations
🔢 Excel 365 Dynamic Array Functions
• FILTER: Filter array based on criteria
• RANDARRAY: Array of random numbers
• SEQUENCE: Sequential number array
• SORT/SORTBY: Sort arrays
• UNIQUE: Extract unique values
• XLOOKUP/XMATCH: Advanced lookup functions
🧮 Advanced Mathematical
• CEILING/FLOOR: Round to multiple
• [Link]/[Link]: Advanced rounding
• COMBIN/COMBINA: Combinations
• CONVERT: Unit conversions
• DEGREES/RADIANS: Angle conversions
• EXP/LN/LOG/LOG10: Exponential/logarithm
• FACT/FACTDOUBLE: Factorial functions
• GCD/LCM: Greatest common divisor/least common multiple
• MOD: Remainder after division
• MROUND: Round to nearest multiple
• MULTINOMIAL: Multinomial coefficient
• PERMUT/PERMUTATIONA: Permutations
• PI: Pi constant
• POWER: Number raised to power
• QUOTIENT: Integer division
• SIGN: Sign of number (+1, 0, -1)
• SQRT/SQRTPI: Square root functions
📈 Statistical Functions
• AVEDEV: Average deviation
• [Link]/[Link]: Beta distribution
• [Link]/[Link]: Binomial distribution
• [Link]/[Link]/[Link]: Chi-square functions
• [Link]/CONFIDENCE.T: Confidence intervals
• CORREL/PEARSON: Correlation coefficient
• COVARIANCE.P/COVARIANCE.S: Covariance
• DEVSQ: Sum of squared deviations
• [Link]: Exponential distribution
• [Link]/[Link]/[Link]: F-distribution
• FORECAST/[Link]: Linear forecast
• [Link]: Exponential smoothing forecast
• GAMMA/[Link]/[Link]: Gamma functions
• GAUSS: Standard normal distribution
• GEOMEAN/HARMEAN: Geometric/harmonic mean
• GROWTH/TREND: Growth/trend calculations
• [Link]: Hypergeometric distribution
• INTERCEPT/SLOPE: Linear regression
• KURT: Kurtosis
• LARGE/SMALL: Kth largest/smallest
• LINEST/LOGEST: Regression statistics
• [Link]/[Link]: Log-normal distribution
• [Link]: Negative binomial distribution
• [Link]/[Link]: Normal distribution
• [Link]/[Link]: Standard normal distribution
• [Link]/[Link]: Percentiles
• [Link]/[Link]: Percent rank
• PHI: Standard normal density
• [Link]: Poisson distribution
• PROB: Probability calculation
• [Link]/[Link]: Quartiles
• SKEW/SKEW.P: Skewness
• STANDARDIZE: Z-score calculation
• STDEV.P/STDEV.S: Standard deviation
• STDEVA/STDEVPA: Standard deviation including text
• STEYX: Standard error
• [Link]/[Link]/[Link]: T-distribution
• TRIMMEAN: Mean excluding extremes
• VAR.P/VAR.S: Variance
• VARA/VARPA: Variance including text
• [Link]: Weibull distribution
• [Link]: Z-test
🔧 Engineering Functions
• BESSELI/BESSELJ/BESSELK/BESSELY: Bessel functions
• BITAND/BITOR/BITXOR: Bitwise operations
• BITLSHIFT/BITRSHIFT: Bit shifting
• COMPLEX: Create complex number
• CONVERT: Engineering unit conversions
• DEC2BIN/DEC2HEX/DEC2OCT: Decimal conversions
• DELTA: Test equality
• ERF/ERFC: Error functions
• GESTEP: Test threshold
• HEX2BIN/HEX2DEC/HEX2OCT: Hexadecimal conversions
• IMABS/IMAGINARY/IMREAL: Complex number functions
• OCT2BIN/OCT2DEC/OCT2HEX: Octal conversions
🔢 Trigonometric Functions
• ACOS/ACOSH/ACOT/ACOTH: Inverse trigonometric
• ASIN/ASINH/ATAN/ATANH: Inverse trigonometric
• ATAN2: Two-argument arctangent
• COS/COSH/COT/COTH: Cosine functions
• SEC/SECH: Secant functions
• SIN/SINH: Sine functions
• TAN/TANH: Tangent functions
🎲 Database Functions
• DAVERAGE: Database average
• DCOUNT/DCOUNTA: Database count
• DGET: Extract single record
• DMAX/DMIN: Database max/min
• DPRODUCT: Database product
• DSTDEV/DSTDEVP: Database standard deviation
• DSUM: Database sum
• DVAR/DVARP: Database variance
🧊 Cube Functions (For OLAP)
• CUBEKPIMEMBER: KPI member
• CUBEMEMBER: Cube member
• CUBEMEMBERPROPERTY: Member property
• CUBERANKEDMEMBER: Ranked member
• CUBESET: Define calculated set
• CUBESETCOUNT: Set count
• CUBEVALUE: Aggregated value
🔄 Power Tools (Advanced)
• Power Query: Import & clean external data
• Power Pivot: Complex data models with DAX formulas
• VBA Macros: Automate tasks with programming
• PivotTables: Summarize massive datasets
⌨️ Essential Shortcuts
• Ctrl+C/V: Copy/Paste
• Ctrl+Z: Undo
• Ctrl+S: Save
• F2: Edit cell
• Ctrl+Shift+L: Add filters
• Alt+=: AutoSum
• Ctrl+T: Create table
🚨 Common Formula Errors
• #VALUE! → Check data types (text vs numbers)
• #REF! → Fix broken cell references
• #N/A → VLOOKUP/XLOOKUP couldn't find match
• #DIV/0! → Division by zero
• #NAME? → Excel doesn't recognize formula name
• #NULL! → Space between ranges instead of comma