Microsoft Excel: Complete Guide 2026 — Formulas, Functions, and Mastery Tips

Excel Basics: Understanding the Interface

Microsoft Excel organizes data in a grid of cells, each identified by a column letter and row number — for example, cell A1 is in column A, row 1; cell C5 is in column C, row 5. A collection of cells is called a range (e.g., A1:D10 refers to all cells from A1 to D10). Each Excel file is called a workbook, and workbooks can contain multiple worksheets (tabs) that appear along the bottom of the screen.

The ribbon at the top of the Excel window organizes all commands into tabs: Home (formatting, clipboard, alignment), Insert (charts, tables, pivot tables), Page Layout (print settings), Formulas (function library, name manager), Data (import, sort, filter, data validation), Review (comments, track changes), and View (window arrangement, freeze panes).

Key navigation shortcuts every Excel user should know:

• Ctrl + Home — go to cell A1
• Ctrl + End — go to the last used cell
• Ctrl + Arrow keys — jump to the edge of a data range
• Ctrl + Shift + Arrow — select to the edge of a data range
• F2 — enter edit mode in the current cell
• Ctrl + Z — undo the last action
• Ctrl + S — save the workbook

Understanding Excel's cell referencing system is fundamental to building formulas correctly. A regular cell reference like A1 is a relative reference — it adjusts automatically when you copy the formula to another cell. An absolute reference like $A$1 remains fixed regardless of where the formula is copied. A mixed reference like $A1 or A$1 fixes either the column or the row but not both.

Essential Excel Formulas and Functions

Excel formulas always begin with an equals sign (=). Functions are pre-built formulas that perform specific calculations. Mastering the most commonly used functions is the single highest-leverage Excel skill for both workplace productivity and certification exams.

Mathematical functions:

• SUM(range) — adds all values in a range. =SUM(A1:A10) adds cells A1 through A10.
• AVERAGE(range) — calculates the arithmetic mean. =AVERAGE(B2:B20)
• COUNT(range) — counts cells containing numbers. COUNTA counts all non-empty cells. COUNTIF counts cells meeting a specific criterion.
• MAX(range) / MIN(range) — returns the largest or smallest value in a range.
• ROUND(number, digits) — rounds a number to a specified number of decimal places.

Logical functions:

• IF(condition, value_if_true, value_if_false) — the most fundamental logical function. =IF(A1>100, 'Over Budget', 'On Track')
• AND(condition1, condition2...) — returns TRUE if all conditions are true. Often nested inside IF.
• OR(condition1, condition2...) — returns TRUE if any condition is true.
• IFERROR(value, value_if_error) — returns an alternative value if a formula produces an error. Essential for professional dashboards.

Text functions:

• LEFT(text, num_chars) / RIGHT / MID — extract characters from text strings.
• CONCATENATE(text1, text2...) or & — joins text strings. ='First ' & A1 & ' Last'
• LEN(text) — returns the number of characters in a string.
• TRIM(text) — removes leading, trailing, and excess interior spaces.
• UPPER / LOWER / PROPER — change text case.

Date and time functions:

• TODAY() — returns today's date. NOW() returns date and time.
• DATEDIF(start, end, unit) — calculates the difference between two dates in days ('D'), months ('M'), or years ('Y').
• WORKDAY(start, days) — adds business days to a date, excluding weekends.

Pivot Tables and Data Analysis

Pivot tables are Excel's most powerful data analysis feature — they allow you to summarize, group, and analyze large datasets interactively without writing a single formula. A pivot table can transform thousands of rows of raw data into a meaningful summary in seconds.

Creating a pivot table: Select your data range (including headers), go to Insert → PivotTable, choose where to place the table (new worksheet or existing), and click OK. The PivotTable Fields pane on the right lets you drag field names into four areas: Rows, Columns, Values, and Filters.

Key pivot table concepts:

• Rows — the field(s) that form the left side of your table (e.g., Region, Product, Employee)
• Values — the field(s) being summarized (e.g., Sales, Quantity — defaults to Sum but can be changed to Count, Average, Max, etc.)
• Filters — slicers or dropdowns to filter the entire pivot table to a subset (e.g., Year = 2025)
• Calculated fields — custom formulas you create within the pivot table using other value fields

Refreshing pivot tables: Pivot tables do not automatically update when source data changes. Right-click anywhere in the pivot table and choose 'Refresh' to update, or go to PivotTable Analyze → Refresh All. For best practice, convert your source data to an Excel Table (Ctrl+T) before creating the pivot table — Tables expand automatically and make refresh unnecessary for new rows.

Power Pivot and Power Query: For very large datasets (millions of rows) or data from multiple sources, Excel 365 and Excel 2021 include Power Query (for importing and transforming data) and Power Pivot (for building data models with relationships between tables). These tools extend Excel's analytical capabilities well beyond what traditional pivot tables support.

Excel Charts and Data Visualization

Excel offers a comprehensive chart library for visualizing data. Selecting the right chart type for your data and your audience's needs is a fundamental skill for professional reporting.

Bar and Column charts: Best for comparing discrete categories. Column charts (vertical bars) are ideal for showing rankings or category comparisons. Bar charts (horizontal bars) work better when category labels are long.

Line charts: Best for showing trends over time. Use a line chart when you have a continuous time axis (months, quarters, years) and want to show the direction and rate of change.

Pie and donut charts: Show part-to-whole relationships. Best used with 4–6 segments maximum — too many slices make pie charts unreadable. Donut charts are a modern alternative that allow a label in the center ring.

Scatter plots and bubble charts: Show correlations between two variables (scatter) or three variables (bubble — x, y, and bubble size). Essential for statistical analysis and data science presentations.

Sparklines: Tiny inline charts that fit inside a single cell, useful for showing trends alongside data in a table without taking up chart space. Available as Line, Column, and Win/Loss sparkline types.

Recommended Charts: Excel's 'Recommended Charts' option (Insert → Recommended Charts) analyzes your selected data and suggests the most appropriate chart types — a useful starting point when you are unsure which chart best represents your data.

Advanced Excel Features

Beyond core functions and pivot tables, several advanced Excel features separate intermediate users from true power users in professional settings.

Data Validation: Restricts what can be entered in a cell — dropdown lists, number ranges, date limits, custom formula-based rules. Data validation is essential for building robust spreadsheets that prevent data entry errors. Access via Data → Data Validation.

Conditional Formatting: Automatically applies formatting (color fills, icons, data bars) to cells based on their values. Useful for highlighting outliers, heat maps, and dashboard-style reporting. Common rules include color scales (green-yellow-red), top/bottom 10%, and custom formula-based rules.

Named Ranges: Assigning a descriptive name to a cell or range (e.g., 'TaxRate' for cell F2) makes formulas more readable and maintainable. Named ranges also make absolute references unnecessary since named ranges behave as absolute by default.

Power Query (Get & Transform): Import data from external sources (databases, web pages, CSV files, SharePoint) and transform it automatically each time the query is refreshed. Power Query eliminates repetitive manual data cleaning tasks and is one of the most high-value productivity skills for data analysts.

Macros and VBA: Record repetitive sequences of actions as macros (no coding required) or write Visual Basic for Applications (VBA) code for full automation. VBA can automate complex multi-step processes, generate reports, interact with other Office applications, and build custom functions. For a deeper dive into Excel formulas and automation, see our guide on Excel formulas for mastery.

Microsoft Excel Certification (MOS Exam)

The Microsoft Office Specialist (MOS) Excel certification is a globally recognized credential that validates your Excel proficiency for employers. The MOS certification program is administered through Certiport at authorized testing centers and is available at two levels:

MOS Excel Associate (MO-200): Tests core Excel skills including spreadsheet creation, formatting, formulas, charts, and data management. This certification is appropriate for candidates with 1–3 years of Excel use in professional settings. The exam contains 35–40 project-based tasks completed within 50 minutes.

MOS Excel Expert (MO-201): Tests advanced Excel features including Power Query, advanced formulas, custom number formats, complex charts, and data analysis tools. This level is appropriate for experienced users who work with Excel daily in analytical roles. Earning both MO-200 and MO-201 qualifies you for the MOS Expert recognition.

Both exams use a performance-based testing format — you complete real tasks in a live Excel environment rather than answering multiple-choice questions. This approach tests applied knowledge rather than theoretical recall. For a complete MOS exam guide and preparation strategy, see our Microsoft Excel certification guide, which covers the exam blueprint, recommended study resources, and scoring methodology.

For job seekers, the MOS certification signals Excel competency to hiring managers who frequently test candidates with a skills assessment before making a hire. Preparing specifically for job interview Excel tests requires different practice than MOS certification prep — see our guide on Excel skills test preparation for interviews for targeted preparation.