Organize tabular data and explain results using formulas, tables and charts.

Before starting: Basic computer use and tabular data.

Practice project: Build a study-expense workbook with known-total checks and clear labels.

1. Excel Essentials & Grid Anatomy

  1. Excel Introduction: Workbooks, Worksheets, The Ribbon & Quick Access Toolbar
  2. Grid Coordinate System: Cells, Columns, Rows, Ranges & The Name Box
  3. Excel Data Types: Text, Numbers, Dates, Booleans & Empty Cells
  4. AutoFill Series, Custom Lists & AI Flash Fill (Ctrl + E)
  5. Range Selection, Keyboard Navigation & Productivity Shortcuts

2. Cell Formatting, Numbers & Custom Masks

  1. Number Formatting: General, Number, Currency vs Accounting Formats
  2. Custom Number Formatting: Four-Section Format Masks (#,##0;(#,##0);'-')
  3. Date & Time Formatting: Excel Serial Number Engine & Custom Date Codes
  4. Cell Styling: Fills, Borders, Alignment, Text Wrapping & Merging Pitfalls
  5. Format Painter, Clear Formats & Managing Sheet Cell Styles

3. Formula Syntax, Referencing & Basic Math

  1. Formula Architecture: The Equals Sign (=), Operator Hierarchy & PEMDAS
  2. Cell Referencing: Relative (A1), Absolute ($A$1) & Mixed ($A1 vs A$1) Referencing
  3. Basic Math Functions: SUM, AVERAGE, MIN, MAX & AutoSum (Alt + =)
  4. Counting Functions: COUNT, COUNTA, COUNTBLANK & Their Critical Differences
  5. Mathematical Utilities: ROUND, ROUNDUP, ROUNDDOWN, INT & MOD

4. Logical Decisions & Boolean Logic

  1. The IF Function: Single Condition Branching & Dynamic Return Values
  2. Nested IF Statements & The Modern IFS Function (Excel 2019/365)
  3. Compound Logical Conditions: AND, OR, NOT & Combining Multiple Criteria
  4. Error Trapping: IFERROR, IFNA & Designing Bulletproof User Spreadsheets
  5. Multi-Case Branching: SWITCH vs CHOOSE for Clean Category Mapping

5. Conditional Aggregations & Statistical Analysis

  1. Single-Criterion Math: COUNTIF & SUMIF Syntax, Criteria Quotes & Logic
  2. Multi-Criteria Aggregations: COUNTIFS, SUMIFS & AVERAGEIFS Mastery
  3. Wildcard Criteria: Asterisk (*), Question Mark (?) & Exact Search with Tilde (~)
  4. Ranking & Extremes: LARGE, SMALL, RANK.EQ & Robust Central Tendency with MEDIAN
  5. Frequency & Distribution: MODE.SNGL, PERCENTILE.INC & Statistical Summaries

6. Lookup & Reference Engines: VLOOKUP to Modern XLOOKUP

  1. Classic VLOOKUP: Exact (FALSE) vs Approximate (TRUE) Match Mechanics
  2. Limitations of VLOOKUP: Left-Lookup Traps & Column Index Fragility
  3. Two-Way Matrix Lookup: Dynamic INDEX & MATCH Architecture
  4. The Modern Super-Lookup: XLOOKUP Mastery (Excel 2021 & Microsoft 365)
  5. Reverse & Matrix Power: XMATCH, OFFSET & Dynamic INDIRECT References

7. Text Manipulation & Modern String Parsing

  1. String Slicing: LEFT, RIGHT, MID & Measuring Length with LEN
  2. Data Cleansing: TRIM, CLEAN, UPPER, LOWER & Title Casing with PROPER
  3. Substring Locators: FIND (Case-Sensitive) vs SEARCH (Case-Insensitive)
  4. Modern Concatenation: Ampersand (&), CONCAT & Delimited TEXTJOIN
  5. Microsoft 365 Dynamic Text Splitters: TEXTBEFORE, TEXTAFTER & TEXTSPLIT

8. Date, Time & Business Schedule Calculations

  1. Dynamic Timestamps: TODAY(), NOW() & Volatile Recalculation Mechanics
  2. Component Assembly: Constructing Dates with DATE, YEAR, MONTH & DAY
  3. Milestone Dates: EDATE (Month Offsets) & EOMONTH (End of Month Schedules)
  4. Working Days & Holidays: NETWORKDAYS, NETWORKDAYS.INTL & WORKDAY
  5. Age & Duration Calculation: The Secret DATEDIF Function & Unit Tokens ("Y", "M", "D")

9. Dynamic Array Formulas, Sorting & Filtering

  1. The Dynamic Array Revolution: #SPILL! Behavior & Spilled Range Operator (#)
  2. Deduplication & Distinct Lists: The UNIQUE Function (Excel 365 & 2021)
  3. Automatic Dynamic Sorting: The SORT & SORTBY Functions
  4. Conditional Live Slicing: The FILTER Dynamic Array Function
  5. Synthetic Arrays & Matrix Reshaping: SEQUENCE, RANDARRAY, CHOOSECOLS & TOCOL

10. Structured Tables, Data Validation, Pivot Tables & Power Query

  1. Excel Structured Tables (Ctrl + T): Structured Referencing, Auto-Expansion & Total Row
  2. Data Validation: Dropdown Lists, Numeric Constraints & Dependent Dropdowns (INDIRECT)
  3. Conditional Formatting: Rule Hierarchies, Color Scales & Custom Formula Highlighting
  4. Pivot Tables & Pivot Charts: Drag-and-Drop Aggregations, Slicers & Calculated Fields
  5. Modern Excel Workflows: Power Query ETL, Formula Auditing & Excel 2026 Roadmap

Avoid data leakage when preparing a dataset

A model evaluation is useful only when the held-out rows have not influenced training. Split the rows before learning a mean, scaler, vocabulary, or feature-selection rule. Fit those rules using training data, then reuse them unchanged on validation and test data. Time-ordered data often needs a chronological split instead of a random split.

Python 3; example uses no external packages

training = [10, None, 30]
testing = [None, 90]

observed = [x for x in training if x is not None]
mean = sum(observed) / len(observed)

def fill(values):
    return [mean if x is None else x for x in values]

print(fill(training))
print(fill(testing))
Check the output
[10, 20.0, 30]
[20.0, 90]

Explain why including 90 when computing the replacement mean leaks information. Decide what to do if every training value is missing.

Check a spreadsheet or dashboard against known values

A polished chart can conceal a wrong total. First make a three-row fixture whose result you can calculate without software. Decide how blanks and duplicates should be handled. In a dashboard, check the unfiltered result and then a filtered result. Keep numeric values numeric rather than storing formatted numbers as text.

Excel or Power BI; arithmetic fixture is independent of product version

Category    Amount
Books       10
Books       20
Travel      30

All rows: 10 + 20 + 30 = 60
Books only: 10 + 20 = 30
Check the output
All rows total 60. Books total 30. A report that produces different values needs investigation before sharing.

Add a blank amount, a zero, and a duplicate row. Document the intended behavior before adjusting formulas or measures.

Open the Excel learning path on DevStudyHub · Play coding games on this blog

logoblog

No comments:

Post a Comment

Please do not enter any spam link in the comment box.