Welcome to Phase 2 of your data journey! In this module, we explore two of the most powerful and accessible tools in a data analyst’s toolkit: Microsoft Excel and Google Sheets. Whether you’re prepping raw data or creating dynamic dashboards, this guide will help you master it all.
1️⃣ Fundamental Functions & Formulas
Familiarize yourself with the Excel/Sheets interface and master foundational functions:
- 🔢 Basic Functions:
SUM,AVERAGE,COUNT,COUNTA,IF,TEXT,CONCATENATE - 🔍 Lookup & Reference:
VLOOKUP,HLOOKUP,INDEX-MATCH,XLOOKUP - 🎯 Conditional Logic:
IF,IFERROR,AND,OR - 📊 Aggregation:
SUMIFS,COUNTIFS,AVERAGEIFS
💡 Pro Tip: Use INDEX-MATCH instead of VLOOKUP for more flexibility and performance.
2️⃣ Pivot Tables & Data Analysis
Pivot Tables are your best friend when it comes to summarizing large datasets.
- Create dynamic pivot tables in Excel/Sheets
- Group, filter, and sort data efficiently
- Add calculated fields for extra insights
- Build pivot charts for visual storytelling
⚙️ Use pivot tables to identify trends, patterns, and outliers quickly.
3️⃣ Data Cleaning & Formatting Best Practices
Dirty data? Not anymore! Learn how to clean and prep your dataset like a pro:
- ❌ Remove duplicates and blanks
- 🔤 Standardize text with
TRIM,CLEAN,PROPER,LEFT,RIGHT,MID - ✂️ Use Text to Columns and Flash Fill for quick splitting/merging
- ✅ Apply Data Validation for consistent inputs
- 🧹 Leverage Power Query for large-scale data transformations
4️⃣ Conditional Formatting & Data Validation
Make your data more visual and reliable:
- 🎨 Apply color scales, data bars, and icon sets
- 🧠 Build custom rules with formulas
- 🔽 Create drop-down lists and dependent lists
- 🧯 Use error checking to prevent bad data entries
🎯 Great formatting makes your data easier to read and analyze at a glance.
5️⃣ Advanced Excel for Analytics
📈 Dashboards & Reports
Take your reporting skills to the next level:
- Build interactive dashboards with slicers & timelines
- Use a variety of charts: line, bar, scatter, waterfall
- Connect to live data sources for real-time reporting
🧰 Power Query for Data Transformation
Master Excel’s built-in ETL tool:
- Import & merge multiple datasets
- Clean & reshape data with Power Query Editor
- Automate refreshes for up-to-date reports
🤖 Automate Tasks with Macros
Streamline your workflow with automation:
- Record and edit Macros to reduce repetitive work
- Get started with VBA (Visual Basic for Applications)
- Automate routine data cleaning or formatting tasks
🎯 What’s Next?
By the end of this phase, you’ll be equipped to clean, analyze, and present data confidently using spreadsheets. In Phase 3, we’ll dive into SQL for data extraction and querying.
Consistency is key — the more you practice, the more fluent you’ll become in spreadsheets.