playbooks

home / skills / pluginagentmarketplace / custom-plugin-data-analyst / excel

excel skill

/skills/excel

This skill helps you master advanced Excel analytics techniques, enabling dynamic pivoting, lookups, and automated reports for data driven insights.

npx playbooks add skill pluginagentmarketplace/custom-plugin-data-analyst --skill excel

Review the files below or copy the command above to add this skill to your agents.

Files (6)
SKILL.md
1.6 KB
---
name: excel
description: Advanced Excel and spreadsheet analytics techniques
version: "2.0.0"
sasmp_version: "2.0.0"
bonded_agent: 01-data-analytics-foundations
bond_type: SECONDARY_BOND

# Skill Configuration
config:
  atomic: true
  retry_enabled: true
  max_retries: 3
  backoff_strategy: exponential

# Parameter Validation
parameters:
  skill_level:
    type: string
    required: true
    enum: [beginner, intermediate, advanced]
    default: beginner
  focus_area:
    type: string
    required: false
    enum: [formulas, pivots, power_query, charts, all]
    default: all

# Observability
observability:
  logging_level: info
  metrics: [formula_complexity, pivot_usage, automation_level]
---

# Excel Analytics Skill

## Overview
Master advanced Excel techniques for professional data analysis and reporting.

## Topics Covered
- Pivot tables and advanced filtering
- VLOOKUP, XLOOKUP, INDEX-MATCH
- Power Query and data transformation
- Excel formulas for analysis
- Charts and conditional formatting

## Learning Outcomes
- Master advanced Excel functions
- Build dynamic pivot tables
- Create automated reports
- Transform data with Power Query

## Error Handling

| Error Type | Cause | Recovery |
|------------|-------|----------|
| #REF! error | Broken cell reference | Check and update references |
| #N/A error | Lookup not found | Use IFERROR wrapper |
| Slow calculation | Too many formulas | Convert to values, optimize |
| File corruption | Crash during save | Enable AutoRecover, use cloud backup |

## Related Skills
- foundations (for core concepts)
- visualization (for chart design)
- reporting (for automated delivery)

Overview

This skill teaches advanced Excel and spreadsheet analytics techniques for professional data analysis and reporting. It focuses on practical workflows—pivot tables, advanced lookups, Power Query, formulas, and visualization—to speed analysis and create automated reports. The content helps turn messy data into reliable, repeatable outputs.

How this skill works

The skill inspects common analysis tasks and provides step-by-step techniques: building dynamic pivot tables, writing efficient lookup formulas (VLOOKUP/XLOOKUP/INDEX-MATCH), and transforming data with Power Query. It also covers charting, conditional formatting, and strategies to diagnose and recover from common Excel errors. Emphasis is on reproducible, performance-minded solutions and error-handling patterns.

When to use it

  • Preparing monthly or quarterly reports from transactional data
  • Cleaning and reshaping messy tables before analysis
  • Creating dashboards and interactive summaries for stakeholders
  • Automating repetitive data transformations with Power Query
  • Resolving lookup or reference errors in complex workbooks

Best practices

  • Design a clear raw/data layer separate from analysis and reporting sheets
  • Prefer structured tables and named ranges to reduce reference errors
  • Use Power Query for repeatable ETL and avoid overly complex worksheet formulas
  • Wrap fragile formulas with error handlers like IFERROR or ISERROR strategically
  • Optimize heavy workbooks by turning calculated ranges to values and limiting volatile functions

Example use cases

  • Build a sales dashboard with slicers, dynamic pivot tables, and trend charts
  • Reconcile two datasets using XLOOKUP or INDEX-MATCH and flag mismatches
  • Automate monthly ETL: import, transform, aggregate, and export using Power Query
  • Create conditional formatted scorecards to highlight outliers and KPIs
  • Diagnose and fix common errors (#REF!, #N/A) and recover from slow calculation issues

FAQ

Can this skill help with very large datasets?

Yes. It recommends Power Query, efficient lookups, and workbook optimization techniques to handle larger datasets without moving to a database.

When should I use XLOOKUP vs INDEX-MATCH?

Use XLOOKUP for straightforward, readable lookups with built-in error handling; use INDEX-MATCH when you need more flexibility or compatibility with older Excel versions.

How do I prevent file corruption and slow performance?

Enable AutoRecover and cloud backups for safety, convert heavy formula regions to values when final, and remove unnecessary volatile formulas to improve performance.