Reach new heights
Start your online course today!
Skill up and have an impact! Your business career starts here.
Time to start a course.
Leaderboard
No leaderboard currently :(
Master Google Sheets/MS Excel from Basic to Advanced
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; auto-fill; 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; LET, LAMBDA (Excel 365). |
| Dynamic Array Functions (Excel 365) | FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP variants. |
| Power Query and Data Import (Excel) | Importing data from files, databases, 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 Collaboration (Sheets) | 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. |
| Solver Add-In (Excel) | Optimization modeling with constraints. |
| Integration with BI Tools | Connecting Excel/Sheets to Power BI, Data Studio, or other visualization platforms. |
8. Capstone Projects and Case Studies
Reinforce learning with real-world applications.
| Project | Objectives |
|---|---|
| Dashboard Creation | Build an interactive dashboard combining charts, slicers, and pivot tables. |
| Financial Analysis Model | Use advanced formulas and what-if analysis to project revenues, costs, and profitability. |
| Data Cleaning and Reporting (Sheets) | Import external data, clean using functions, and deliver automated reports. |
Basic
Intermediate
0 steps
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.
Intermediate
Advanced
0 steps
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. |
Advanced
Basic
Intermediate
0 steps
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. |
Advanced
Basic
Intermediate
0 steps
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.
Intermediate
Advanced
0 steps
Master Google Sheets/MS Excel from Basic to Advanced
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; auto-fill; 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; LET, LAMBDA (Excel 365). |
| Dynamic Array Functions (Excel 365) | FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP variants. |
| Power Query and Data Import (Excel) | Importing data from files, databases, 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 Collaboration (Sheets) | 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. |
| Solver Add-In (Excel) | Optimization modeling with constraints. |
| Integration with BI Tools | Connecting Excel/Sheets to Power BI, Data Studio, or other visualization platforms. |
8. Capstone Projects and Case Studies
Reinforce learning with real-world applications.
| Project | Objectives |
|---|---|
| Dashboard Creation | Build an interactive dashboard combining charts, slicers, and pivot tables. |
| Financial Analysis Model | Use advanced formulas and what-if analysis to project revenues, costs, and profitability. |
| Data Cleaning and Reporting (Sheets) | Import external data, clean using functions, and deliver automated reports. |
Basic
Intermediate
0 steps