Master basic to advanced Excel, Google Sheets, & SQL
1. Foundations and Interface
Understand spreadsheet environments and basic operations.
| Topic | Description |
|---|---|
| Getting Started with Spreadsheets | Overview of interface, ribbons, toolbars, workbooks, worksheets, navigation, and basic settings. |
| Data Entry and Editing | Entering/editing numbers, text, and formulas; autofill; Flash Fill; undo/redo; keyboard shortcuts. |
| Cell Referencing and Ranges | Relative vs. absolute references; named ranges; dynamic ranges; efficient range selection techniques. |
2. Formatting and Layout
Make data readable and visually appealing.
| Topic | Description |
|---|---|
| Cell Formatting | Number/date formats; fonts; alignment; borders; fill colors; custom formats. |
| Conditional Formatting | Highlight rules; data bars; icon sets; color scales; formula-based format rules. |
| Tables and Styles | Converting ranges to tables; table styles; banded rows; built-in and custom formats. |
3. Core Formulas and Functions
Build fundamental analytical skills.
| 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 (and 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; formatting and customization. |
| PivotTables & PivotCharts | Creating, configuring fields; grouping; slicers; calculated fields; pivot charts. |
| Data Validation | Input rules; drop-down lists; error alerts; circle invalid data. |
5. Advanced Functions and Tools
Develop power-user capabilities.
| Topic | Description |
|---|---|
| Advanced Formulas | SUMIFS, COUNTIFS, AVERAGEIFS; nested IFs; array formulas; |
| Dynamic Array Functions (Excel 365) | FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP variants. |
| Power Query and Data Import (Excel) | Importing data from files or web; shaping and transforming data. |
6. Collaboration and Sharing
Enable teamwork and version control.
| Topic | Description |
|---|---|
| Sharing and Permissions (Sheets) | Granting view/comment/edit access; protected ranges; version history. |
| Comments, Notes, and Chat (Sheets) | Adding comments; assigning tasks; threaded discussions. |
| Co-authoring and Real-Time Editing | Simultaneous editing; presence indicators; version snapshots. |
| Workbook Protection (Excel) | Password protection; sheet/workbook locking; restricting edits. |
7. Specialized Analytics Techniques
Expand into analytical workflows.
| Topic | Description |
|---|---|
| What-If Analysis (Excel) | Data Tables; Goal Seek; Scenario Manager. |
| QUERY Function (Sheets) | SQL-like queries over ranges for filtering, aggregation, and summary. |
| Integration with BI Tools | Connecting Excel/Sheets to Power BI, Data Studio, or other visualization platforms. |
8. SQL for Spreadsheet Users
Introduce foundational database concepts and querying.
| Topic | Description |
|---|---|
| Introduction to SQL | What SQL is; relational database concepts (tables, rows, columns). |
| CRUD Operations | SELECT, INSERT, UPDATE, DELETE syntax; WHERE filters; ORDER BY sorting. |
| Aggregation & Grouping | GROUP BY; HAVING; SUM, COUNT, AVG, MIN, MAX functions. |
| Joining Tables | INNER JOIN, LEFT JOIN basics; combining data across tables. |
| Practical Exercises | Exporting spreadsheet data to a simple database; writing and running basic queries. |
9. Capstone Projects and Case Studies
Reinforce learning with real-world applications.
| Project | Objectives |
|---|---|
| Interactive Dashboard | Build a dynamic dashboard combining charts, pivot tables, and QUERY-function insights. |
| Financial Analysis Model | Use advanced formulas and what-if analysis to project revenues, costs, and profitability. |
| End-to-End Data Pipeline | Import data into SQL, query and clean it, then analyze and visualize results in Sheets/Excel. |
Outcome:
Graduates will confidently navigate Excel and Google Sheets, perform complex analyses, automate tasks, and execute mid level SQL queries—fully prepared for data-driven roles.
No lessons are available yet.