Skip to main content
FreeAcademy

Tools and Workflows

Spreadsheets, AI, and When to Use What

Different tools suit different tasks. This chapter helps you choose and combine tools effectively.

The Tool Landscape

Spreadsheets (Excel, Google Sheets)

Best for:

  • Smaller datasets (under ~100,000 rows)
  • Ad-hoc analysis
  • Familiar, accessible
  • Calculations with formulas
  • Basic charts
  • Manual data manipulation

Limitations:

  • Performance with large data
  • Complex analysis
  • Version control
  • Collaboration challenges (Excel)

AI Assistants (Claude, ChatGPT, etc.)

Best for:

  • Natural language questions
  • Explaining concepts
  • Suggesting approaches
  • Pattern recognition
  • Data cleaning suggestions
  • Generating code
  • Interpreting results

Limitations:

  • Context window limits
  • Can make mistakes
  • Need to verify outputs
  • Not connected to live data

Business Intelligence Tools (Tableau, Power BI, Looker)

Best for:

  • Dashboards and visualization
  • Large datasets
  • Shared reporting
  • Regular monitoring
  • Interactive exploration

Limitations:

  • Learning curve
  • Cost
  • Setup required
  • Less flexible for ad-hoc

Programming (Python, R, SQL)

Best for:

  • Complex analysis
  • Large datasets
  • Reproducibility
  • Custom modeling
  • Automation

Limitations:

  • Learning curve
  • Setup overhead
  • Slower for simple tasks

When to Use What

Quick, One-Time Analysis

Start with: Spreadsheet or AI

Small dataset, simple question, don't need to repeat? Open Excel or paste into AI.

Regular Reporting

Use: Spreadsheet templates or BI tools

Build once, refresh regularly. Templates save time.

Exploring New Questions

Use: AI first, then spreadsheet or code

AI helps you figure out the approach. Then execute with appropriate tool.

Large Dataset

Use: BI tool or programming

Spreadsheets slow down with large data. Use tools designed for scale.

Learning New Techniques

Use: AI for explanation, then practice in tools

AI explains concepts clearly. Then apply in real tools.

Combining Tools Effectively

The AI + Spreadsheet Workflow

  1. Plan in AI: Ask AI how to approach the analysis
  2. Prepare in spreadsheet: Clean and structure data
  3. Analyze with both: Use AI for complex questions, spreadsheet for calculations
  4. Verify: Check AI outputs against spreadsheet calculations
  5. Communicate: Use AI to help draft summaries

Using AI with Spreadsheets

Getting help:

  • "Write an Excel formula to [calculation]"
  • "How do I create a pivot table for [task]"
  • "What's wrong with this formula: [formula]"

Analysis:

  • Paste data into AI
  • Ask questions
  • Request calculations
  • Get interpretation

Practical Spreadsheet Tips

Essential Functions

SUMIF/SUMIFS: Sum based on criteria COUNTIF/COUNTIFS: Count based on criteria AVERAGEIF: Average based on criteria VLOOKUP/INDEX-MATCH: Look up values IF: Conditional logic TEXT/VALUE: Convert between text and numbers CONCATENATE/&: Combine text

Pivot Tables

The most powerful spreadsheet feature. Learn it well.

Use for:

  • Summarizing by categories
  • Cross-tabulations
  • Quick calculations
  • Data exploration

Data Validation

Prevent errors by restricting inputs:

  • Drop-down lists
  • Number ranges
  • Date restrictions

Named Ranges

Name your data ranges. Makes formulas readable.

Workflow Examples

Example 1: Monthly Sales Report

  1. Export sales data from system (CSV)
  2. Open in spreadsheet
  3. Clean: remove duplicates, fix formats
  4. Create pivot table: sales by product/region
  5. Add month-over-month comparison
  6. Create charts
  7. Use AI to help write summary
  8. Share report

Example 2: Customer Segmentation

  1. Export customer data
  2. Paste sample into AI: "How should I segment these customers?"
  3. AI suggests RFM approach
  4. Calculate RFM scores in spreadsheet
  5. Ask AI: "How do I categorize these scores?"
  6. Apply categorization in spreadsheet
  7. Analyze segment differences
  8. Create recommendations

Example 3: Ad-Hoc Question

  1. Business question arises
  2. Identify needed data
  3. Paste into AI: "What's the relationship between X and Y?"
  4. AI provides analysis and interpretation
  5. Verify key numbers in spreadsheet
  6. Communicate findings

AI Prompt: Tool Selection

Help me choose the right approach for this analysis.

What I need to do: [Analysis task]
Data size: [Approximate rows/complexity]
My tools: [What you have access to]
My skills: [What you're comfortable with]
Timeline: [How quickly needed]
Repeatability: [One-time or ongoing]

Recommend:
1. Best tool/approach for this task
2. Step-by-step workflow
3. What to watch out for
4. Alternatives if primary approach doesn't work

What's Next

Templates for common analyses.

Next chapter: Analysis templates.

Common Analysis ScenariosAnalysis Templates