Excel Sheet

Excel Spreadsheet Workflows

Tech Stack Target / Version: Excel desktop or openpyxl-based spreadsheet automation with current OOXML-compatible workflows.

Use this skill when the deliverable is an .xlsx workbook or when spreadsheet structure matters.

• Leverage native parallel subagent dispatch and 200k+ context windows where available.

Current MCP Reality

Excel MCP tooling is host-dependent. In GitHub Copilot, Excel actions may appear as grouped Office tools. In Codex or Claude, those tools may be absent entirely. Treat the included Python script as the reliable fallback.

Activation Conditions

Use symptom -> action triggers: when one matches, apply this skill and verify with the protocol below.

• Creating or updating workbooks
• Converting CSV data into structured Excel output
• Applying formulas, formatting, charts, or pivots
• Producing spreadsheet deliverables when layout matters

Practical Workflow

1. Confirm whether the client exposes spreadsheet MCP tools. 2. If yes, inspect the actual tool names before assuming a wrapper exists. 3. If no, use the local converter or author the workbook with openpyxl. 4. Validate formulas and chart ranges before claiming the workbook is ready.

MCP Fallback – Native Automation

When MCP is unavailable, use native automation: openpyxl for .xlsx, CSV export for flat data, and manual formula inspection for high-risk calculations. Preserve formulas, number formats, sheet names, hidden sheets, and workbook metadata, then reopen or parse the workbook before claiming success.

Anti-Patterns

• Treating source content as already clean: Formatting automation will happily preserve broken or inconsistent input.
• Skipping an open-file verification pass: Documents and spreadsheets often fail in the destination app, not in the script output.
• Automating irreversible edits without checkpoints: A small mapping mistake can affect an entire workbook or document.

Verification Protocol

Before claiming "skill applied successfully":

1. Pass/fail: The Excel Sheet artifact type, target format, and required output fidelity are stated before editing. 2. Pass/fail: MCP availability is checked and the native automation fallback path is named when MCP is absent. 3. Pass/fail: The produced file or formula is opened, parsed, rendered, or otherwise validated locally. 4. Pressure-test scenario: Apply the workflow to a file with formatting, metadata, or conversion edge cases and verify nothing important is lost. 5. Success metric: Zero unverified document claims; the artifact itself is the evidence.

Workbook Checklist

• [ ] Sheets are named clearly
• [ ] Headers are formatted consistently
• [ ] Formulas are used instead of hardcoded derived values
• [ ] Charts reference the correct ranges
• [ ] Frozen panes or filters are applied where useful

References & Resources

Documentation

• Excel Formulas Reference - Formula patterns, lookup guidance, and Power Query notes

Scripts

• CSV to XLSX Converter - Local fallback for generating formatted Excel workbooks from CSV input

Examples

• Excel Workbook Examples - Example workbook structures and automation patterns

<!-- PORTABILITY:START -->

Cross-Client Portability

This skill is written to stay usable across GitHub Copilot, Claude Code, Codex, and Gemini CLI.

• GitHub Copilot: keep the folder in a Copilot-visible skill or plugin path, or wrap the workflow as project instructions if the host does not support portable skill folders directly.
• Claude Code: keep the folder in a local skills directory or a compatible plugin or marketplace source.
• Codex: install or sync the folder into $CODEX_HOME/skills/<skill-name> and restart Codex after major changes.
• Gemini CLI: this repository generates a project command named /skills:excel-sheet from this skill. Rebuild commands with python scripts/export-gemini-skill.py excel-sheet and then run /commands reload inside Gemini CLI.

<!-- PORTABILITY:END -->

<!-- MCP:START -->

MCP Availability And Fallback

Preferred MCP Server: Excel MCP

• Fallback prompt: "Use the Excel Spreadsheet Workflows skill without MCP. Rely on the local SKILL.md, bundled references or scripts, and manual verification. Show the exact commands, evidence, and final checks you used before concluding."
• Use scripts/csv-to-xlsx.py, openpyxl, or desktop Excel when the spreadsheet MCP surface is missing.
• Re-open the generated workbook locally to verify formulas, ranges, and frozen panes.

<!-- MCP:END -->

Related Skills

• documentation-authoring: Use it when the workflow also needs drafting structured technical or product documents.
• notion-docs: Use it when the workflow also needs Notion page and database publishing workflows.
• pdf: Use it when the workflow also needs PDF extraction, generation, and layout-aware review.
• word-document: Use it when the workflow also needs Word document authoring and formatting workflows.

Changelog

[2026-04-25] - Version 1.2 Verification Protocol Refresh

Added

• Added a Verification Protocol section with skill-specific pass/fail checks, one pressure-test scenario, and a measurable success metric.
• Added guidance to leverage native parallel subagent dispatch and 200k+ context windows where available.
• Added the shared MCP Fallback ? Native Automation section for Office-style workflows.

Changed

• Updated SKILL.md frontmatter to version: "1.2" and last_updated: 2026-04-25.
• Reframed activation guidance toward symptom -> action triggers and standardized two-stage review wording where applicable.

[2026-04-24] - Version 1.1 Refresh

Changed

• Updated the SKILL frontmatter version to 1.1 for the 2026-04-24 catalog refresh.

All notable changes to this skill will be documented in this file.

[2026-04-24] - Skill Refresh

Changed

• Standardized the SKILL frontmatter with version metadata, last-updated date, tags, and a concise catalog description.
• Reformatted the portability and MCP guidance with a preferred server line, a copy-paste fallback prompt, and consistent bullet lists.
• Added a catalog-standard Anti-Patterns section and refreshed the Related Skills links at the end of the skill.

[2026-04-04] - Cross-Client Portability Refresh

Changed

• Added a standard portability note covering GitHub Copilot, Claude Code, Codex, and Gemini CLI.
• Documented the preferred MCP server surface for this skill and a local no-MCP fallback workflow.

Tested

• Validated SKILL.md frontmatter, portability sections, and Gemini export readiness with python scripts/validate-skills.py.

[2026-03-09] - Workspace Modernization

Changed

• Rewrote the skill to describe Excel MCP as host-specific rather than assuming universal wrapper commands
• Repositioned scripts/csv-to-xlsx.py as the local fallback when spreadsheet MCP tools are unavailable

[2026-02-28] - Description Rewrite and Cross-References

Changed

• Rewrote the skill description to concise activation-focused wording
• Improved keyword specificity to reduce overlap with related skills

Added

• Added the related-skills cross-reference table

[2026-02-19] - Excel MCP Extraction

Changed

• Split Excel workflow guidance out of the former shared office-documents skill into an Excel-specific skill
• Replaced library-first guidance with MCP-oriented spreadsheet workflows
• Updated activation triggers around workbook, worksheet, spreadsheet, and Excel MCP use cases

Added

• Added Excel-specific references and local CSV-to-XLSX fallback automation
• Added workbook, worksheet, cell, chart, and pivot-table workflow coverage

Fixed

• Reduced ambiguity between generic document handling and Excel-specific spreadsheet tasks

Excel Workbook Examples

Example 1: Creating a Quarterly Report

// Activate required tools
activate_worksheet_management_tools();
activate_cell_management_tools();
activate_column_management_tools();

// Create workbook
mcp_excel_create_workbook({ filename: "q3_report.xlsx" });

// Rename and create sheets
mcp_excel_rename_worksheet({ filename: "q3_report.xlsx", old_name: "Sheet1", new_name: "Summary" });
mcp_excel_create_worksheet({ filename: "q3_report.xlsx", sheet_name: "Raw Data" });
mcp_excel_create_worksheet({ filename: "q3_report.xlsx", sheet_name: "Charts" });

// Write headers to Summary sheet
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "A1", value: "Region" });
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "B1", value: "Q1" });
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "C1", value: "Q2" });
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "D1", value: "Q3" });
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "E1", value: "Total" });
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "F1", value: "Change %" });

// Format header row
mcp_excel_format_range({
  filename: "q3_report.xlsx",
  sheet_name: "Summary",
  range: "A1:F1",
  bold: true,
  font_color: "FFFFFF",
  bg_color: "1B3A5C",
  alignment: "center"
});

// Write data rows
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "A2", value: "Americas" });
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "B2", value: 1200000 });
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "C2", value: 1350000 });
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "D2", value: 1380000 });
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "E2", value: "=SUM(B2:D2)" });
mcp_excel_write_cell({ filename: "q3_report.xlsx", sheet_name: "Summary", cell: "F2", value: "=(D2-B2)/B2" });

// Format data row
mcp_excel_format_range({
  filename: "q3_report.xlsx",
  sheet_name: "Summary",
  range: "A2:F2",
  bg_color: "D6EAF8"
});

// Add chart
mcp_excel_create_chart({
  filename: "q3_report.xlsx",
  sheet_name: "Charts",
  chart_type: "column",
  data_sheet: "Summary",
  data_range: "A1:E3",
  title: "Revenue by Region & Quarter",
  position: { left: 1, top: 1, width: 8, height: 5 }
});

Example 2: Data Analysis Workbook

// Create data analysis workbook
activate_worksheet_management_tools();
activate_cell_management_tools();

mcp_excel_create_workbook({ filename: "data_analysis.xlsx" });
mcp_excel_rename_worksheet({ filename: "data_analysis.xlsx", old_name: "Sheet1", new_name: "Data" });
mcp_excel_create_worksheet({ filename: "data_analysis.xlsx", sheet_name: "Analysis" });
mcp_excel_create_worksheet({ filename: "data_analysis.xlsx", sheet_name: "Pivot" });

// Write data headers
const headers = ["Date", "Product", "Category", "Region", "Sales", "Quantity"];
headers.forEach((h, i) => {
  mcp_excel_write_cell({
    filename: "data_analysis.xlsx",
    sheet_name: "Data",
    cell: String.fromCharCode(65 + i) + "1",
    value: h
  });
});

// Format headers
mcp_excel_format_range({
  filename: "data_analysis.xlsx",
  sheet_name: "Data",
  range: "A1:F1",
  bold: true,
  bg_color: "4472C4",
  font_color: "FFFFFF"
});

// Write sample data
const data = [
  ["2025-01-15", "Widget A", "Electronics", "Americas", 15000, 100],
  ["2025-01-16", "Widget B", "Electronics", "EMEA", 12000, 80],
  ["2025-01-17", "Gadget C", "Accessories", "APAC", 8500, 120]
];

data.forEach((row, rowIndex) => {
  row.forEach((value, colIndex) => {
    mcp_excel_write_cell({
      filename: "data_analysis.xlsx",
      sheet_name: "Data",
      cell: String.fromCharCode(65 + colIndex) + (rowIndex + 2),
      value: value
    });
  });
});

// Create pivot table
mcp_excel_create_pivot_table({
  filename: "data_analysis.xlsx",
  source_sheet: "Data",
  source_range: "A1:F100",
  destination_sheet: "Pivot",
  destination_cell: "A3",
  rows: ["Region", "Category"],
  columns: [],
  values: [{ field: "Sales", aggregation: "sum" }]
});

Example 3: Budget Tracker

// Create personal budget tracker
activate_worksheet_management_tools();
activate_cell_management_tools();

mcp_excel_create_workbook({ filename: "budget_tracker.xlsx" });
mcp_excel_rename_worksheet({ filename: "budget_tracker.xlsx", old_name: "Sheet1", new_name: "Budget" });

// Budget template headers
mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: "A1", value: "Category" });
mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: "B1", value: "Budgeted" });
mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: "C1", value: "Actual" });
mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: "D1", value: "Difference" });
mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: "E1", value: "Status" });

// Format headers
mcp_excel_format_range({
  filename: "budget_tracker.xlsx",
  sheet_name: "Budget",
  range: "A1:E1",
  bold: true,
  bg_color: "203864",
  font_color: "FFFFFF"
});

// Budget categories
const categories = [
  ["Housing", 2000, "=C2"],
  ["Food", 600, "=C3"],
  ["Transportation", 400, "=C4"],
  ["Utilities", 300, "=C5"],
  ["Entertainment", 200, "=C6"],
  ["Savings", 1000, "=C7"]
];

categories.forEach((cat, i) => {
  const row = i + 2;
  mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: `A${row}`, value: cat[0] });
  mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: `B${row}`, value: cat[1] });
  mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: `D${row}`, value: `=C${row}-B${row}` });

  // Status formula
  mcp_excel_write_cell({
    filename: "budget_tracker.xlsx",
    sheet_name: "Budget",
    cell: `E${row}`,
    value: `=IF(D${row}>0,"Under Budget",IF(D${row}<0,"Over Budget","On Target"))`
  });

  // Alternate row shading
  const bgColor = i % 2 === 0 ? "FFFFFF" : "E7E6E6";
  mcp_excel_format_range({
    filename: "budget_tracker.xlsx",
    sheet_name: "Budget",
    range: `A${row}:E${row}`,
    bg_color: bgColor
  });
});

// Total row
const totalRow = categories.length + 2;
mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: `A${totalRow}`, value: "TOTAL" });
mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: `B${totalRow}`, value: `=SUM(B2:B${totalRow-1})` });
mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: `C${totalRow}`, value: `=SUM(C2:C${totalRow-1})` });
mcp_excel_write_cell({ filename: "budget_tracker.xlsx", sheet_name: "Budget", cell: `D${totalRow}`, value: `=C${totalRow}-B${totalRow}` });

// Format total row
mcp_excel_format_range({
  filename: "budget_tracker.xlsx",
  sheet_name: "Budget",
  range: `A${totalRow}:E${totalRow}`,
  bold: true,
  bg_color: "203864",
  font_color: "FFFFFF"
});

Example 4: Project Timeline Tracker

// Create project timeline workbook
activate_worksheet_management_tools();
activate_cell_management_tools();

mcp_excel_create_workbook({ filename: "project_timeline.xlsx" });
mcp_excel_rename_worksheet({ filename: "project_timeline.xlsx", old_name: "Sheet1", new_name: "Timeline" });

// Timeline headers
mcp_excel_write_cell({ filename: "project_timeline.xlsx", sheet_name: "Timeline", cell: "A1", value: "Task" });
mcp_excel_write_cell({ filename: "project_timeline.xlsx", sheet_name: "Timeline", cell: "B1", value: "Start Date" });
mcp_excel_write_cell({ filename: "project_timeline.xlsx", sheet_name: "Timeline", cell: "C1", value: "End Date" });
mcp_excel_write_cell({ filename: "project_timeline.xlsx", sheet_name: "Timeline", cell: "D1", value: "Duration (Days)" });
mcp_excel_write_cell({ filename: "project_timeline.xlsx", sheet_name: "Timeline", cell: "E1", value: "Status" });
mcp_excel_write_cell({ filename: "project_timeline.xlsx", sheet_name: "Timeline", cell: "F1", value: "Assigned To" });

// Format headers
mcp_excel_format_range({
  filename: "project_timeline.xlsx",
  sheet_name: "Timeline",
  range: "A1:F1",
  bold: true,
  bg_color: "2E5090",
  font_color: "FFFFFF"
});

// Project tasks
const tasks = [
  ["Requirements Gathering", "2025-01-01", "2025-01-14", "=C2-B2", "Completed", "Alice"],
  ["Design Phase", "2025-01-15", "2025-01-31", "=C3-B3", "Completed", "Bob"],
  ["Development", "2025-02-01", "2025-02-28", "=C4-B4", "In Progress", "Charlie"],
  ["Testing", "2025-03-01", "2025-03-15", "=C5-B5", "Not Started", "Diana"],
  ["Deployment", "2025-03-16", "2025-03-20", "=C6-B6", "Not Started", "Alice"]
];

tasks.forEach((task, i) => {
  const row = i + 2;
  task.forEach((value, colIndex) => {
    mcp_excel_write_cell({
      filename: "project_timeline.xlsx",
      sheet_name: "Timeline",
      cell: String.fromCharCode(65 + colIndex) + row,
      value: value
    });
  });

  // Format data row
  mcp_excel_format_range({
    filename: "project_timeline.xlsx",
    sheet_name: "Timeline",
    range: `A${row}:F${row}`,
    bg_color: i % 2 === 0 ? "FFFFFF" : "D9E1F2"
  });
});

// Merge cells for project title
mcp_excel_merge_cells({
  filename: "project_timeline.xlsx",
  sheet_name: "Timeline",
  range: "A1:F1"
});

Excel Formulas and Data Manipulation Reference

Comprehensive reference for Excel formulas, data patterns, and programmatic spreadsheet manipulation.

---

Lookup Formulas

VLOOKUP

Searches the first column of a range and returns a value from a specified column.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Examples:

=VLOOKUP("SKU-100", A2:D50, 3, FALSE)
  → Find SKU-100 in column A, return value from column C

=VLOOKUP(B2, Products!A:E, 4, FALSE)
  → Cross-sheet lookup; find B2's value in Products sheet column A, return column D

Limitations: Only searches left-to-right. Use INDEX/MATCH or XLOOKUP for reverse lookups.

XLOOKUP

Modern replacement for VLOOKUP — searches any direction, supports multiple match modes.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Examples:

=XLOOKUP("Widget", B2:B100, E2:E100, "Not found")
  → Search B column for "Widget", return corresponding E value

=XLOOKUP(TODAY(), A2:A100, B2:B100, , -1)
  → Find today's date or nearest earlier date, return column B

=XLOOKUP(D2, Products[SKU], Products[Name]&" - "&Products[Category])
  → Return concatenated result from structured table

INDEX / MATCH

The classic flexible lookup combination — works in any direction.

=INDEX(return_range, MATCH(lookup_value, lookup_range, match_type))

MATCH match_type: 0 = exact, 1 = largest value ≤ lookup (sorted asc), -1 = smallest value ≥ lookup (sorted desc).

Examples:

=INDEX(C2:C100, MATCH("Target", A2:A100, 0))
  → Find "Target" in column A, return corresponding column C value

=INDEX(A2:A100, MATCH(MAX(D2:D100), D2:D100, 0))
  → Return the name (column A) for the row with the highest value in column D

=INDEX(B2:D100, MATCH("ID-55", A2:A100, 0), 3)
  → Two-dimensional: find row by ID in A, return 3rd column of B:D range

---

Math Formulas

SUM

=SUM(A1:A100)                    → Sum a range
=SUM(A1:A100, C1:C100)           → Sum multiple ranges
=SUM(Sheet1:Sheet3!B5)           → 3D sum across sheets

SUMIF / SUMIFS

=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

Examples:

=SUMIF(B2:B100, "Electronics", D2:D100)
  → Sum column D where column B = "Electronics"

=SUMIF(A2:A100, ">1000")
  → Sum values in A that are greater than 1000

=SUMIFS(E2:E100, B2:B100, "West", C2:C100, ">=2025-01-01")
  → Sum E where region is "West" AND date ≥ Jan 1 2025

SUMPRODUCT

Array-aware multiplication and summation without Ctrl+Shift+Enter.

=SUMPRODUCT(array1, [array2], ...)

Examples:

=SUMPRODUCT(B2:B100, C2:C100)
  → Multiply each B×C pair, then sum all products (weighted total)

=SUMPRODUCT((A2:A100="East")*(C2:C100>50)*D2:D100)
  → Conditional sum: total D where region="East" AND quantity>50

=SUMPRODUCT((MONTH(A2:A100)=3)*B2:B100)
  → Sum B for all March entries

ROUND / ROUNDUP / ROUNDDOWN

=ROUND(3.14159, 2)     → 3.14
=ROUNDUP(3.141, 2)     → 3.15
=ROUNDDOWN(3.149, 2)   → 3.14
=ROUND(1234, -2)       → 1200    (round to nearest hundred)

---

Text Formulas

CONCATENATE / CONCAT / TEXTJOIN

=CONCATENATE(A1, " ", B1)          → "John Smith" (legacy)
=CONCAT(A1, " ", B1)               → Same, modern
=A1 & " " & B1                     → Operator shorthand

=TEXTJOIN(", ", TRUE, A1:A10)
  → Join non-empty cells with comma+space: "Alpha, Beta, Gamma"

LEFT / RIGHT / MID

=LEFT(A1, 3)       → First 3 characters
=RIGHT(A1, 4)      → Last 4 characters
=MID(A1, 5, 3)     → 3 characters starting at position 5

Practical example — extract area code:

=MID(A1, 2, 3)     → From "(555) 123-4567" extracts "555"

TRIM / CLEAN / SUBSTITUTE

=TRIM(A1)                            → Remove leading/trailing/extra spaces
=CLEAN(A1)                           → Remove non-printable characters
=SUBSTITUTE(A1, "old", "new")        → Replace all occurrences
=SUBSTITUTE(A1, " ", "", 1)          → Remove only the first space

TEXT (Number Formatting)

=TEXT(A1, "0.00")             → "1234.50"
=TEXT(A1, "$#,##0.00")        → "$1,234.50"
=TEXT(A1, "yyyy-mm-dd")       → "2025-03-15"
=TEXT(A1, "dddd, mmmm d")    → "Saturday, March 15"
=TEXT(A1, "0.0%")             → "85.3%"

LEN / FIND / SEARCH

=LEN(A1)                     → Character count
=FIND("@", A1)               → Position of @ (case-sensitive, error if missing)
=SEARCH("word", A1)          → Position (case-insensitive, supports wildcards)
=IFERROR(FIND("x", A1), 0)   → Return 0 if not found

---

Date Formulas

TODAY / NOW

=TODAY()          → Current date (no time)
=NOW()            → Current date and time
=TODAY() + 30     → 30 days from today

DATEDIF

Calculates the difference between two dates. Not shown in autocomplete but works.

=DATEDIF(start_date, end_date, unit)

Examples:

=DATEDIF(A1, TODAY(), "Y")         → Years since date in A1
=DATEDIF(A1, A2, "M")             → Months between two dates
=DATEDIF(B1, B2, "Y") & " years, " & DATEDIF(B1, B2, "YM") & " months"
  → "3 years, 7 months"

EDATE / EOMONTH

=EDATE(A1, 3)        → Date 3 months after A1
=EDATE(A1, -6)       → Date 6 months before A1
=EOMONTH(A1, 0)      → Last day of A1's month
=EOMONTH(A1, 1)      → Last day of next month

DATE / YEAR / MONTH / DAY

=DATE(2025, 6, 15)       → June 15, 2025
=YEAR(A1)                → Extract year
=MONTH(A1)               → Extract month (1-12)
=DAY(A1)                 → Extract day (1-31)
=WEEKDAY(A1, 2)          → Day of week (Monday=1 with type 2)

WORKDAY / NETWORKDAYS

=WORKDAY(A1, 10)                → 10 business days after A1
=WORKDAY(A1, 10, holidays)      → Excluding listed holidays
=NETWORKDAYS(A1, B1)            → Business days between two dates

---

Statistical Formulas

AVERAGE / MEDIAN

=AVERAGE(A1:A100)                → Arithmetic mean
=AVERAGEIF(B1:B100, ">0")       → Average of positive values only
=AVERAGEIFS(D1:D100, B1:B100, "East", C1:C100, ">100")
=MEDIAN(A1:A100)                 → Middle value

STDEV / VAR

=STDEV(A1:A100)       → Sample standard deviation (STDEV.S)
=STDEVP(A1:A100)      → Population standard deviation (STDEV.P)
=VAR(A1:A100)         → Sample variance

COUNT / COUNTA / COUNTBLANK / COUNTIF / COUNTIFS

=COUNT(A1:A100)                                → Count numeric cells
=COUNTA(A1:A100)                               → Count non-empty cells
=COUNTBLANK(A1:A100)                           → Count empty cells
=COUNTIF(B1:B100, "Complete")                  → Count cells matching criteria
=COUNTIF(C1:C100, ">500")                      → Count cells > 500
=COUNTIFS(B1:B100, "East", C1:C100, ">1000")   → Multiple criteria

PERCENTILE / QUARTILE

=PERCENTILE.INC(A1:A100, 0.9)    → 90th percentile (inclusive)
=QUARTILE.INC(A1:A100, 1)        → First quartile (25th percentile)
=QUARTILE.INC(A1:A100, 3)        → Third quartile (75th percentile)

MIN / MAX / LARGE / SMALL

=MIN(A1:A100)              → Smallest value
=MAX(A1:A100)              → Largest value
=LARGE(A1:A100, 3)         → 3rd largest value
=SMALL(A1:A100, 2)         → 2nd smallest value

---

Conditional Formulas

IF

=IF(condition, value_if_true, value_if_false)

Examples:

=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F")))
  → Nested grade assignment

=IF(AND(B1>0, C1>0), B1*C1, 0)
  → Multiply only if both positive

IFS (Excel 2019+)

Evaluates multiple conditions in order — first TRUE wins.

=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")
  → Cleaner than nested IF; TRUE acts as default/else

SWITCH

Match a value against a list of cases.

=SWITCH(A1,
  "N", "North",
  "S", "South",
  "E", "East",
  "W", "West",
  "Unknown"
)

AND / OR / NOT / XOR

=AND(A1>0, B1>0)       → TRUE if both conditions met
=OR(A1="Yes", B1="Yes") → TRUE if either condition met
=NOT(A1="Error")        → Invert boolean
=XOR(A1>0, B1>0)        → TRUE if exactly one condition met

IFERROR / IFNA

=IFERROR(A1/B1, 0)              → Return 0 instead of #DIV/0!
=IFNA(VLOOKUP(...), "Missing")  → Handle #N/A specifically

---

Pivot Table Patterns

When to Use Pivot Tables

• Summarizing large datasets by category
• Cross-tabulating two dimensions
• Calculating subtotals, averages, counts per group
• Drilling down into data hierarchies

Common Pivot Configurations

Calculated Fields

Revenue per Unit = Revenue / Quantity
Profit Margin = (Revenue - Cost) / Revenue

Pivot Table Best Practices

• Source data: one row per record, no merged cells, consistent headers
• Refresh after source data changes
• Group dates by Month/Quarter/Year for time analysis
• Use Slicers for interactive filtering
• Name ranges or use structured tables as source

---

Data Validation Rules

Dropdown List

Source: "Option A,Option B,Option C"
  or
Source: =NamedRange

Numeric Constraints

Date Constraints

Allow: Date, Between, =TODAY(), =TODAY()+365
  → Only accept dates within the next year

Custom Formula Validation

=AND(LEN(A1)=10, LEFT(A1,3)="PRJ")
  → Must be 10 chars starting with "PRJ"

=COUNTIF($A:$A, A1)<=1
  → Prevent duplicate entries in column A

---

Conditional Formatting Patterns

Color Scales

Apply 2-color or 3-color gradient based on cell value — useful for heat maps.

Data Bars

In-cell bar chart proportional to value — quick visual comparison.

Icon Sets

Arrows, traffic lights, stars, flags based on value thresholds.

Formula-Based Rules

=AND($D2>1000, $E2="Open")
  → Highlight row where amount > 1000 AND status = Open

=MOD(ROW(), 2)=0
  → Alternating row shading

=$B2=MAX($B$2:$B$100)
  → Highlight the row with maximum value

=TODAY()-$C2>30
  → Highlight dates older than 30 days

---

Chart Types and Use Cases

Chart Best Practices

• Title every chart clearly
• Label axes with units
• Limit pie charts to ≤ 6 slices; use bar chart otherwise
• Start Y-axis at 0 for bar/column charts (avoid misleading scales)
• Use consistent colors across related charts
• Remove chart junk: unnecessary gridlines, 3D effects, borders

---

Power Query M Basics

Power Query (Get & Transform) uses the M language for data transformation.

Common M Patterns

// Load CSV
let
    Source = Csv.Document(File.Contents("C:\data\sales.csv"), [Delimiter=",", Encoding=65001]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangedTypes = Table.TransformColumnTypes(PromotedHeaders, {
        {"Date", type date}, {"Amount", type number}, {"Region", type text}
    })
in
    ChangedTypes

Filtering Rows

Table.SelectRows(Source, each [Region] = "East" and [Amount] > 1000)

Adding Calculated Columns

Table.AddColumn(Source, "Profit", each [Revenue] - [Cost], type number)
Table.AddColumn(Source, "Year", each Date.Year([OrderDate]), Int64.Type)
Table.AddColumn(Source, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([OrderDate])))

Grouping and Aggregation

Table.Group(Source, {"Region"}, {
    {"TotalRevenue", each List.Sum([Revenue]), type number},
    {"OrderCount", each Table.RowCount(_), Int64.Type},
    {"AvgOrder", each List.Average([Revenue]), type number}
})

Unpivoting Columns

Table.UnpivotOtherColumns(Source, {"Product", "Region"}, "Month", "Sales")
  // Convert wide format (Jan, Feb, Mar columns) to long format

Merging Tables (JOIN)

Table.NestedJoin(Orders, {"CustomerID"}, Customers, {"ID"}, "CustomerData", JoinKind.LeftOuter)
// Then expand: Table.ExpandTableColumn(Merged, "CustomerData", {"Name", "Email"})

Practical Transform Pipeline

let
    Source = Excel.Workbook(File.Contents("C:\data\raw.xlsx"), null, true),
    Sheet1 = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
    Headers = Table.PromoteHeaders(Sheet1),
    Typed = Table.TransformColumnTypes(Headers, {
        {"Date", type date}, {"Revenue", type number}, {"Cost", type number}
    }),
    Cleaned = Table.SelectRows(Typed, each [Revenue] <> null and [Revenue] > 0),
    WithProfit = Table.AddColumn(Cleaned, "Profit", each [Revenue] - [Cost], type number),
    WithMargin = Table.AddColumn(WithProfit, "Margin", each [Profit] / [Revenue], type number),
    Sorted = Table.Sort(WithMargin, {{"Date", Order.Ascending}})
in
    Sorted