The Library · Insights
Deep dives across data, automation & AI
978 in-depth articles — from Excel foundations to production machine learning. Filter by topic or difficulty to find exactly what you need.
SQLPractitioner
Ranking and Filtering Groups with HAVING: Writing Conditional Aggregates That Go Beyond WHERE
Most SQL practitioners use HAVING only for the simplest cases. This lesson goes deeper — teaching conditional aggregates with CASE WHEN, dynamic thresholds with subqueries, and real-world churn analysis patterns that WHERE simply cannot express.
20 min read
Microsoft ExcelPractitioner
Mastering Excel's Paste Special: Transpose, Values, Formats, and Operations for Efficient Data Workflows
Paste Special is one of Excel's most powerful and underused features. This lesson gives you complete, practical command over every major option — Transpose, Values, Formats, Operations, Skip Blanks, and more — with real-world workflows that eliminate helper columns and manual reformatting.
23 min read
SQLFoundation
Combining Results with SQL Set Operations: UNION, UNION ALL, INTERSECT, and EXCEPT
SQL set operations let you stack and compare results from multiple queries — without a single JOIN. Learn how UNION, UNION ALL, INTERSECT, and EXCEPT work, when to use each one, and how to avoid the mistakes that trip up beginners.
15 min read
Microsoft ExcelFoundation
Formatting Numbers, Dates, and Cells in Excel: Custom Number Formats for Clear, Professional Spreadsheets
Raw data is only as useful as it is readable. This lesson teaches you how to use Excel's custom number format system to display numbers, dates, and currencies exactly the way you need — without changing a single underlying value. Learn the four-section syntax, date/time codes, and professional cell formatting techniques from the ground up.
17 min read
SQLExpert
Mastering SQL EXISTS and NOT EXISTS: Correlated Subquery Patterns for Filtering with Related Data
EXISTS and NOT EXISTS are among the most powerful and misunderstood filtering tools in SQL. Learn how correlated subqueries work under the hood, why NOT EXISTS beats NOT IN every time, and how to compose multi-criteria existence checks that would tie a JOIN in knots.
28 min read
Microsoft ExcelExpert
Mastering Excel Formula Auditing: Trace Precedents, Dependents, and Evaluate Formulas to Build Error-Free Workbooks
Most Excel users write formulas and hope they're right. This lesson teaches you to systematically verify them — using Trace Precedents, Trace Dependents, Evaluate Formula, Watch Window, and Error Checking to audit any workbook with professional rigor. Whether you're inheriting a financial model or building your own, these techniques will give you genuine confidence in your numbers.
30 min read
SQLPractitioner
Writing SQL Self-Joins: Query the Same Table Twice to Compare Rows and Find Relationships
Self-joins are one of SQL's most versatile — and most misunderstood — patterns. Learn how to query a table against itself to navigate hierarchies, compare rows, detect duplicates, and find sequential relationships, with realistic examples and clear guidance on when to use this technique versus window functions.
21 min read
Microsoft ExcelPractitioner
Importing and Cleaning External Data in Excel: Text to Columns, Flash Fill, and Data Transformation Techniques
Raw external data is almost never analysis-ready. This lesson teaches you a complete, repeatable workflow for splitting, cleaning, and normalizing imported data using Text to Columns, Flash Fill, and Excel's full suite of text transformation functions — with real-world scenarios and a hands-on exercise.
21 min read
SQLFoundation
Selecting Distinct Values and Eliminating Duplicates in SQL: DISTINCT, GROUP BY, and COUNT Explained
Duplicate rows in SQL results are one of the most common — and most fixable — problems in data work. This lesson teaches you exactly how DISTINCT, GROUP BY, and COUNT work, when to use each, and how to audit your data for duplicates like a pro.
15 min read
SQLExpert
Writing SQL FROM Scratch: Structuring Multi-Step Analytical Queries with Derived Tables and Inline Views
Most complex analytical questions can't be answered in a single flat query — they require computing something, and then computing something on top of that. This lesson teaches you how to structure multi-step SQL using derived tables and inline views, with realistic examples, optimizer internals, and a complete hands-on exercise.
26 min read
SQLPractitioner
Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice
Learn how to write production-quality SQL that combines GROUP BY, HAVING, and CASE WHEN in a single query. This lesson goes beyond the basics to cover conditional counts, rate calculations, performance tier classification, and the subtle bugs that cause silently wrong results.
21 min read
SQLFoundation
Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements
Learn how to write SQL subqueries that dynamically filter rows, look up calculated values, and check for related data — all inside a single query. This hands-on lesson builds from scalar subqueries to correlated subqueries with realistic examples and exercises.
15 min read