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:

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