Master basic to advanced Excel, Google Sheets, and SQL with Gen AI
1. Foundations and Interface
Establish comfort with spreadsheet environments and basic operations.
| Topic | Description |
|---|---|
| Getting Started with Spreadsheets | Overview of Excel & Google Sheets interfaces, ribbons, menus, creating/saving workbooks, navigation. |
| Data Entry and Editing | Entering/editing numbers, text, dates; autofill; Flash Fill; undo/redo; keyboard shortcuts. |
| Cell Referencing and Ranges | Relative vs. absolute references; named and dynamic ranges; efficient selection and navigation techniques. |
2. Formatting and Layout
Enhance readability and visual appeal of data.
| Topic | Description |
|---|---|
| Cell Formatting | Number/date formats; fonts; alignment; borders; fill colors; custom cell styles. |
| Conditional Formatting | Highlight rules; data bars; icon sets; color scales; custom formula-based rules. |
| Tables and Styles | Converting ranges to tables; table styles; banded rows; table customization (Excel & Sheets). |
3. Core Formulas and Functions
Build essential analytical capabilities.
| Topic | Description |
|---|---|
| Arithmetic & Statistical Functions | SUM, AVERAGE, COUNT, MIN, MAX, ROUND, MEDIAN, MODE. |
| Logical Functions | IF, AND, OR, NOT, IFERROR. |
| Text Functions | CONCAT, LEFT, RIGHT, MID, LEN, TRIM, SUBSTITUTE, UPPER/LOWER/PROPER. |
| Lookup & Reference Functions | VLOOKUP/HLOOKUP, INDEX & MATCH, XLOOKUP (Excel 365). |
4. Data Analysis and Visualization
Transform raw data into actionable insights.
| Topic | Description |
|---|---|
| Sorting, Filtering, Subtotals | AutoFilter; custom filters; Sort & Filter menus; Subtotal feature. |
| Charts and Graphs | Column, bar, line, pie, scatter, combo charts; customization; sparklines (Sheets). |
| PivotTables & PivotCharts | Creating/configuring fields; grouping; slicers; calculated fields; pivot charts. |
| Data Validation | Input rules; drop-down lists; error alerts; highlighting invalid data. |
5. Advanced Functions and Tools
Develop power-user techniques.
| Topic | Description |
|---|---|
| Advanced Formulas | SUMIFS, COUNTIFS, AVERAGEIFS; nested IFs; array formulas; LET/LAMBDA (Excel 365). |
| Dynamic Array Functions (Excel 365) | FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP variants. |
| Power Query/Data Import (Excel) | Importing and shaping data from files, web, and other sources. |
6. Collaboration and Sharing
Leverage cloud and teamwork features.
| Topic | Description |
|---|---|
| Sharing & Permissions (Sheets) | Granting view/comment/edit access; protected ranges; version history. |
| Comments, Notes & Chat (Sheets) | Adding comments; assigning tasks; threaded discussions. |
| Co-authoring & Real-Time Editing | Simultaneous editing; presence indicators; version snapshots. |
| Workbook Protection (Excel) | Password protection; sheet/workbook locking; restricting edits. |
7. SQL Basics for Data Analytics
Introduce core database querying skills.
| Topic | Description |
|---|---|
| Introduction to SQL | What SQL is; relational database concepts (tables, rows, columns). |
| SELECT & Filtering | Basic SELECT syntax; WHERE filters; ORDER BY sorting; LIMIT/OFFSET. |
| Aggregation & Grouping | GROUP BY; HAVING; SUM, COUNT, AVG, MIN, MAX functions. |
| Joins & Relationships | INNER JOIN, LEFT JOIN fundamentals; combining data across tables. |
| Practical Exercises | Export/import Sheets data to a simple database; writing and running basic queries. |
8. Generative AI for Data Analytics
Harness AI—such as Gemini and ChatGPT—for automating tasks, crafting advanced formulas, and generating SQL.
| Topic | Description |
|---|---|
| Overview of Generative AI | Difference between AI and generative AI; use cases in data cleaning, summarization, and insight generation. |
| Prompt Engineering Fundamentals | Crafting precise prompts to generate spreadsheet formulas, data summaries, and chart narratives. |
| AI-Assisted Formula Generation | Using Gemini/ChatGPT to write and optimize advanced Excel & Sheets formulas (e.g., nested functions, array formulas, LAMBDA). |
| AI-Generated SQL Queries | Teaching how to prompt Gemini/ChatGPT to produce correct, efficient SQL statements for SELECT, JOINs, aggregations, and more. |
| AI-Powered Spreadsheet Automation | Leveraging AI to auto-generate data transformations, validation rules, and dashboard components directly in Sheets/Excel. |
| Natural-Language Data Insights | Generating clear, narrative summaries of tables, charts, and pivot results via AI prompts. |
| Ethical & Responsible AI Integration | Addressing bias, privacy, and best practices when incorporating AI into analytics workflows. |
9. Capstone Projects and Case Studies
Apply learned skills in realistic scenarios.
| Project | Objectives |
|---|---|
| Interactive Analytics Dashboard | Build a dynamic dashboard in Excel/Sheets using charts, pivot tables, QUERY function, and AI-generated summaries. |
| Advanced Reporting Workflow | Combine spreadsheet analysis with SQL queries and AI prompts to produce end-to-end analytic reports. |
| AI-Enhanced Data Pipeline | Import data into a database, query and clean it, analyze in Sheets/Excel, and generate AI-driven insights. |
No lessons are available yet.