Foundations

Excel & Spreadsheet Analytics

Build reliable spreadsheet analysis with structured tables, explicit references, formulas, PivotTables, charts, Power Query and auditing practices.

How to use this topic

Learn the mechanism one decision at a time

Work through the lessons in order if the topic is new. If you already know the basics, open the specific leaf lesson that matches the operation, diagnostic or failure mode you need.

1Definition→

2Mechanism→

3Example→

4Diagnostic→

5Decision

Workbook, worksheet and table anatomyWorkbook, worksheet and table anatomy is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

Cell references: relative, absolute and mixedCell references: relative, absolute and mixed is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

Core arithmetic and aggregation formulasCore arithmetic and aggregation formulas is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

Logical formulas with IF, IFS, AND and ORLogical formulas with IF, IFS, AND and OR is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

Conditional aggregation with SUMIFS, COUNTIFS and AVERAGEIFSConditional aggregation with SUMIFS, COUNTIFS and AVERAGEIFS is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

Lookup analysis with XLOOKUPLookup analysis with XLOOKUP is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

INDEX and MATCH for flexible lookupsINDEX and MATCH for flexible lookups is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

Text cleaning with TRIM, CLEAN, TEXTSPLIT and SUBSTITUTEText cleaning with TRIM, CLEAN, TEXTSPLIT and SUBSTITUTE is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

Date and time analysis in ExcelDate and time analysis in Excel is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

Excel Tables and structured referencesExcel Tables and structured references is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

PivotTables for grouped analysisPivotTables for grouped analysis is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

Charts in Excel: choose, label and auditCharts in Excel: choose, label and audit is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

Power Query fundamentalsPower Query fundamentals is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.

Spreadsheet error handling and auditingSpreadsheet error handling and auditing is part of reliable spreadsheet analytics. The aim is not only to make a formula or feature work, but to keep the workbook structured, auditable and understandable when data or users change.