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
- Excel Introduction: Workbooks, Worksheets, The Ribbon & Quick Access Toolbar
- Grid Coordinate System: Cells, Columns, Rows, Ranges & The Name Box
- Excel Data Types: Text, Numbers, Dates, Booleans & Empty Cells
- AutoFill Series, Custom Lists & AI Flash Fill (Ctrl + E)
- Range Selection, Keyboard Navigation & Productivity Shortcuts
2. Cell Formatting, Numbers & Custom Masks
- Number Formatting: General, Number, Currency vs Accounting Formats
- Custom Number Formatting: Four-Section Format Masks (#,##0;(#,##0);'-')
- Date & Time Formatting: Excel Serial Number Engine & Custom Date Codes
- Cell Styling: Fills, Borders, Alignment, Text Wrapping & Merging Pitfalls
- Format Painter, Clear Formats & Managing Sheet Cell Styles
3. Formula Syntax, Referencing & Basic Math
- Formula Architecture: The Equals Sign (=), Operator Hierarchy & PEMDAS
- Cell Referencing: Relative (A1), Absolute ($A$1) & Mixed ($A1 vs A$1) Referencing
- Basic Math Functions: SUM, AVERAGE, MIN, MAX & AutoSum (Alt + =)
- Counting Functions: COUNT, COUNTA, COUNTBLANK & Their Critical Differences
- Mathematical Utilities: ROUND, ROUNDUP, ROUNDDOWN, INT & MOD
4. Logical Decisions & Boolean Logic
- The IF Function: Single Condition Branching & Dynamic Return Values
- Nested IF Statements & The Modern IFS Function (Excel 2019/365)
- Compound Logical Conditions: AND, OR, NOT & Combining Multiple Criteria
- Error Trapping: IFERROR, IFNA & Designing Bulletproof User Spreadsheets
- Multi-Case Branching: SWITCH vs CHOOSE for Clean Category Mapping
5. Conditional Aggregations & Statistical Analysis
- Single-Criterion Math: COUNTIF & SUMIF Syntax, Criteria Quotes & Logic
- Multi-Criteria Aggregations: COUNTIFS, SUMIFS & AVERAGEIFS Mastery
- Wildcard Criteria: Asterisk (*), Question Mark (?) & Exact Search with Tilde (~)
- Ranking & Extremes: LARGE, SMALL, RANK.EQ & Robust Central Tendency with MEDIAN
- Frequency & Distribution: MODE.SNGL, PERCENTILE.INC & Statistical Summaries
6. Lookup & Reference Engines: VLOOKUP to Modern XLOOKUP
- Classic VLOOKUP: Exact (FALSE) vs Approximate (TRUE) Match Mechanics
- Limitations of VLOOKUP: Left-Lookup Traps & Column Index Fragility
- Two-Way Matrix Lookup: Dynamic INDEX & MATCH Architecture
- The Modern Super-Lookup: XLOOKUP Mastery (Excel 2021 & Microsoft 365)
- Reverse & Matrix Power: XMATCH, OFFSET & Dynamic INDIRECT References
7. Text Manipulation & Modern String Parsing
- String Slicing: LEFT, RIGHT, MID & Measuring Length with LEN
- Data Cleansing: TRIM, CLEAN, UPPER, LOWER & Title Casing with PROPER
- Substring Locators: FIND (Case-Sensitive) vs SEARCH (Case-Insensitive)
- Modern Concatenation: Ampersand (&), CONCAT & Delimited TEXTJOIN
- Microsoft 365 Dynamic Text Splitters: TEXTBEFORE, TEXTAFTER & TEXTSPLIT
8. Date, Time & Business Schedule Calculations
- Dynamic Timestamps: TODAY(), NOW() & Volatile Recalculation Mechanics
- Component Assembly: Constructing Dates with DATE, YEAR, MONTH & DAY
- Milestone Dates: EDATE (Month Offsets) & EOMONTH (End of Month Schedules)
- Working Days & Holidays: NETWORKDAYS, NETWORKDAYS.INTL & WORKDAY
- Age & Duration Calculation: The Secret DATEDIF Function & Unit Tokens ("Y", "M", "D")
9. Dynamic Array Formulas, Sorting & Filtering
- The Dynamic Array Revolution: #SPILL! Behavior & Spilled Range Operator (#)
- Deduplication & Distinct Lists: The UNIQUE Function (Excel 365 & 2021)
- Automatic Dynamic Sorting: The SORT & SORTBY Functions
- Conditional Live Slicing: The FILTER Dynamic Array Function
- Synthetic Arrays & Matrix Reshaping: SEQUENCE, RANDARRAY, CHOOSECOLS & TOCOL
10. Structured Tables, Data Validation, Pivot Tables & Power Query
- Excel Structured Tables (Ctrl + T): Structured Referencing, Auto-Expansion & Total Row
- Data Validation: Dropdown Lists, Numeric Constraints & Dependent Dropdowns (INDIRECT)
- Conditional Formatting: Rule Hierarchies, Color Scales & Custom Formula Highlighting
- Pivot Tables & Pivot Charts: Drag-and-Drop Aggregations, Slicers & Calculated Fields
- 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 = 30Check 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
No comments:
Post a Comment
Please do not enter any spam link in the comment box.