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. |
No lessons are available yet.