Data Analyst Skills Guide: SQL, Excel, Tableau, Python

Table of Content

Table of Content

Table of Content

Data Analyst Skills Guide: SQL, Excel, Tableau, Python

✅ SQL - Must-Know Topics 🗄️

Category

Key Concepts & Commands


SQL Basics

SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING, DISTINCT, LIMIT, OFFSET


Data Aggregation

COUNT(), SUM(), AVG(), MIN(), MAX(), GROUP BY with aggregate functions


Joins

INNER JOIN, LEFT JOIN, FULL OUTER JOIN


Data Filtering

Logical operators (AND, OR, NOT), Wildcards (LIKE), IN, BETWEEN


Window Functions

ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), NTILE(), OVER(PARTITION BY...)


Views & CTEs

WITH clause (Common Table Expressions), Creating/Managing Views


Data Cleaning

IS NULL, IS NOT NULL, COALESCE(), NULLIF()


Reporting

PIVOT, UNPIVOT, Creating Summary Tables



!TIP!

💡 Nice-to-Have Topics for SQL

  • Data Manipulation: INSERT, UPDATE, DELETE, Triggers

  • Advanced Joins: Self Joins and Cross Joins

  • Optimization: Query Optimization Techniques, Star & Snowflake Schemas

  • Subqueries: Single-row and Multi-row subqueries


✅ Excel - Must-Know Topics 📊

🛠️ Data Preparation & Cleaning

  • Tools: Remove Duplicates, Text to Columns, Flash Fill, and Data Validation.

  • References: Understanding Absolute vs. Relative References ($A$1, A$1, $A1).

  • Tables: Creating and working with Structured References.

🧪 Essential Formulas & Functions

  • Text: LEFT, RIGHT, MID, TRIM, PROPER

  • Logical: IF, AND, OR, IFERROR

  • Date/Time: TODAY, NOW, DATEDIF, EOMONTH

  • Lookup: VLOOKUP, INDEX, MATCH, XLOOKUP

  • Math/Stats: SUMIF, COUNTIF, AVERAGEIF

📈 Visualization & Analysis

  • Pivot Tables: Creating/Customizing, Calculated Fields, and Items.

  • Charts: Line, Bar, Pie, etc.

  • Conditional Formatting: Custom Rules, Color Scales, and Icon Sets.

[!TIP]

💡 Nice-to-Have Topics for Excel

  • Power Tools: Power Query and Power Pivot.

  • Automation: Recording Macros (no VBA required) and Basic VBA.

  • Advanced Analysis: Data Tables, Goal Seek, and Scenario Manager.



✅ Tableau - Must-Know Topics 🎨

🏗️ Dashboard Design & Prep

  • Storytelling: Creating Dashboards and Stories with Interactive Features (Filters, Actions, Tooltips).

  • Connectivity: Linking to Excel, SQL, Google Sheets, and Cloud Sources.

  • Preparation: Data Blending, Joins, Extracts, and Source Filters.

📊 Visualizations & Calculations

  • Chart Types: Heatmaps, Tree Maps, Dual-Axis, and Combined Charts.

  • Calculated Fields: Custom logic and Aggregations.

  • Table Calcs: Running Total, Moving Average.

  • Filtering: Dimension, Measure, Context, and Cascading Filters.


!TIP!

💡 Nice-to-Have Topics for Tableau

  • LOD Expressions: FIXED, INCLUDE, EXCLUDE.

  • Advanced Analytics: Pareto Charts, Waterfall Charts, Gantt Charts.

  • Organization: Hierarchies, Groups, Sets, and Bins.

  • Prep: Using Tableau Prep for heavy data cleaning.



✅ Python - Must-Know Topics 🐍

🐍 The Fundamentals

Python
# Variables, Loops, and Logic
for item in list:
    if condition:
        print(item)

# Data Structures
my_list = [1, 2, 3]
my_dict = {"key": "value"}

🐼 Libraries for Data Analysis

  • Pandas: DataFrames, Series, merge(), concat(), groupby(), pivot_table().

  • NumPy: Arrays, Mathematical Functions, Reshaping Data.

  • Visualization: * Matplotlib: Histograms, Scatter Plots.

    • Seaborn: Heatmaps, Categorical Plots.

🧹 Data Handling & Scripting

  • Data Cleaning: dropna(), fillna(), replace(), split().

  • Dates: Datetime module, Parsing, and Formatting.

  • Scripting: def functions, return, lambda, and Error Handling (try/except).

  • I/O: Reading/Writing CSV, Excel, and JSON; APIs (Requests) and Web Scraping (BeautifulSoup).


!TIP!

💡 Nice-to-Have Topics for Python

  • Automation: OpenPyXL or XlsxWriter for Excel automation.

  • Advanced Viz: Interactive plots with Plotly.

  • Statistics: SciPy for probability distributions and hypothesis testing.

Data Roles Breakdown

Data Roles Breakdown

Data Roles Breakdown

Excel 30 Days Roadmap

Excel 30 Days Roadmap

Excel 30 Days Roadmap