Excel is powerful for analyzing data. You can summarize, visualize, and find patterns in your data. I use Excel for data analysis all the time, and I'm going to show you the techniques that actually work.

This course will teach you how to analyze data in Excel. By the end, you'll be able to create pivot tables, build charts, and extract insights from your data.

Preparing Your Data

Before analyzing, make sure your data is clean:

• Headers in first row: Each column should have a clear header
• No blank rows: Remove empty rows in your data
• Consistent formatting: Dates should be dates, numbers should be numbers
• No merged cells: Merged cells cause problems with analysis
• One row per record: Each row should be one complete record

Clean data makes analysis much easier. Spend time cleaning before analyzing.

Pivot Tables - Your Best Friend

Pivot tables are Excel's most powerful analysis tool. They summarize data quickly:

How to create one:

1. Select your data (including headers)
2. Go to Insert → PivotTable
3. Choose where to put it (new sheet is usually best)
4. Drag fields into areas:
   • Rows: What to group by
   • Columns: Additional grouping
   • Values: What to calculate (sum, count, average, etc.)
   • Filters: Filter the entire table

Example: Sales data with Date, Product, and Amount columns. Drag Product to Rows, Amount to Values. You'll see total sales per product.

Pro tip: Right-click values in the pivot table to change how they're calculated (sum, average, count, etc.).

Creating Charts

Charts visualize your data. Here's how to create them:

1. Select your data (including headers)
2. Go to Insert → Charts
3. Choose a chart type:
   • Column/Bar: Compare values
   • Line: Show trends over time
   • Pie: Show proportions
   • Scatter: Show relationships
4. Customize the chart (titles, colors, etc.)

Chart best practices:

• Keep it simple - don't overcrowd
• Use clear titles and labels
• Choose appropriate chart types
• Use colors meaningfully

Using SUMIFS for Analysis

SUMIFS lets you sum data based on multiple conditions:

Example: Sum sales where Region is "North" AND Month is "January":

This is powerful for analyzing data by multiple dimensions.

Using COUNTIFS

COUNTIFS counts records that meet multiple conditions:

Example: Count orders where Status is "Completed" AND Amount is greater than 100:

Data Tables (What-If Analysis)

Data tables let you see how changing inputs affects outputs:

1. Set up your calculation
2. Go to Data → What-If Analysis → Data Table
3. Enter input values and see results

Great for scenarios - "What if sales increase by 10%?" or "What if costs decrease by 5%?"

Conditional Formatting

Highlight data based on conditions:

1. Select your data
2. Go to Home → Conditional Formatting
3. Choose a rule:
   • Highlight cells greater than/less than a value
   • Color scales (heat maps)
   • Data bars
   • Icon sets

Example: Highlight all sales over $1000 in green, under $500 in red.

Using Filters for Analysis

Filters let you focus on specific data:

1. Select your data
2. Go to Data → Filter
3. Click arrows in headers to filter
4. Use multiple filters to narrow down

Advanced filters: Use Data → Advanced Filter for complex criteria. You can filter based on formulas and multiple conditions.

Creating Summary Tables

Summarize data with formulas:

Combine with other formulas to create comprehensive summaries.

Analyzing Trends

Moving averages: Smooth out fluctuations to see trends

Growth rates: Calculate percentage change

Common Analysis Tasks

Find top/bottom values:

• Use LARGE and SMALL functions
• Or sort your data
• Or use conditional formatting to highlight

Find duplicates:

• Use conditional formatting → Highlight Duplicates
• Or use COUNTIF to find duplicates

Compare two lists:

• Use VLOOKUP or INDEX/MATCH
• Or use conditional formatting to highlight matches/differences

Best Practices

• Start with pivot tables: They're the fastest way to summarize data
• Visualize your findings: Charts make insights easier to understand
• Document your analysis: Add notes explaining what you found
• Keep raw data separate: Don't modify your original data - work with copies
• Use named ranges: Makes formulas easier to read and maintain