excel-analysis

spjoshis's avatarfrom spjoshis

Master Excel for data analysis with pivot tables, formulas, Power Query, and advanced Excel techniques.

⭐1stars🔀0forks📁View on GitHub🕐Updated Dec 30, 2025
[Spreadsheets]

When & Why to Use This Skill

This Claude skill empowers users to master advanced Excel data analysis through expert guidance on pivot tables, complex formulas, and Power Query. It streamlines business intelligence workflows by providing structured techniques for data transformation, financial modeling, and professional reporting, ensuring high data integrity and actionable insights.

Use Cases

  • Financial Modeling: Build robust and scalable financial models using advanced formulas like INDEX/MATCH and structured table references for accurate forecasting.
  • Automated ETL with Power Query: Streamline data preparation by automating the cleaning, merging, and transformation of large datasets from multiple sources.
  • Business Intelligence Dashboards: Create dynamic pivot tables and calculated fields to visualize sales performance, regional trends, and key performance indicators (KPIs).
  • Data Cleaning and Validation: Implement best practices for data validation and use Power Query to remove duplicates and standardize formats for error-free analysis.
  • Ad-hoc Reporting: Rapidly generate stakeholder-ready reports using complex logical functions and conditional formatting to highlight critical business data.
nameexcel-analysis
descriptionMaster Excel for data analysis with pivot tables, formulas, Power Query, and advanced Excel techniques.

Excel Analysis

Master Excel for data analysis using pivot tables, formulas, Power Query, and advanced features for business analytics.

When to Use This Skill

  • Ad-hoc analysis
  • Quick reporting
  • Data cleaning
  • Financial modeling
  • Business calculations
  • Data transformation
  • Stakeholder reports
  • Self-service analytics

Core Concepts

1. Pivot Tables

## Sales Analysis Pivot Table

**Source Data**: Sales transactions (Date, Region, Product, Sales Rep, Amount)

**Pivot Table Setup**:
- Rows: Region, Sales Rep
- Columns: Month (Date grouped by month)
- Values: Sum of Amount
- Filters: Product, Date Range

**Calculated Fields**:
- Average Sale = Sum of Amount / Count of Transactions
- Target vs Actual = Sum of Amount - Target
- % of Total = Sum of Amount / Grand Total

**Formatting**:
- Currency format for amounts
- Percentage for % of Total
- Conditional formatting: Above/below average

2. Useful Formulas

# VLOOKUP - Lookup customer name from ID
=VLOOKUP(A2, Customers!A:B, 2, FALSE)

# SUMIFS - Sum sales for specific region and product
=SUMIFS(Sales, Region, "West", Product, "Widget")

# INDEX/MATCH - More flexible than VLOOKUP
=INDEX(Customers!B:B, MATCH(A2, Customers!A:A, 0))

# Array Formula - Count unique values
=SUM(1/COUNTIF(A2:A100, A2:A100))

# TEXTJOIN - Combine values with delimiter
=TEXTJOIN(", ", TRUE, A2:A10)

# IFS - Multiple conditions
=IFS(A2>90, "A", A2>80, "B", A2>70, "C", TRUE, "F")

# Date calculations
=EDATE(A2, 3)  # Add 3 months
=NETWORKDAYS(A2, B2)  # Business days between dates

3. Power Query

## ETL with Power Query

**Transform Sales Data**:
1. Load data from CSV
2. Remove duplicates
3. Filter out cancelled orders
4. Split full name into first/last
5. Change date format
6. Group by customer, sum amounts
7. Add calculated column: Tier (based on total)
8. Load to Excel table

**M Code Example**:
```m
let
    Source = Csv.Document(File.Contents("sales.csv")),
    RemoveDuplicates = Table.Distinct(Source),
    FilterCancelled = Table.SelectRows(RemoveDuplicates, each [Status] <> "Cancelled"),
    SplitName = Table.SplitColumn(FilterCancelled, "Name", Splitter.SplitTextByDelimiter(" "), {"FirstName", "LastName"}),
    GroupedRows = Table.Group(SplitName, {"CustomerID"}, {{"TotalSales", each List.Sum([Amount]), type number}}),
    AddTier = Table.AddColumn(GroupedRows, "Tier", each if [TotalSales] > 10000 then "Gold" else if [TotalSales] > 5000 then "Silver" else "Bronze")
in
    AddTier


## Best Practices

1. **Use tables** - Structured references, auto-expansion
2. **Name ranges** - Formulas more readable
3. **Avoid merged cells** - Breaks sorting, filtering
4. **Document assumptions** - Comments, separate tab
5. **Validate data** - Data validation rules
6. **Use shortcuts** - Ctrl+T (table), Alt+D+P (pivot)
7. **Power Query for ETL** - Repeatable transformations
8. **Version control** - Save versions, track changes

## Resources

- **Excel Jet**: Formula reference and tips
- **Mr. Excel**: Advanced Excel techniques