Excel for Business Intelligence Cheat Sheet

Updated 2026-04-28

Next Topic: Financial Analytics and FP&A Cheat Sheet

Excel has evolved into a powerful business intelligence platform through features like Power Query, Power Pivot, and dynamic arrays. Beyond traditional spreadsheets, Excel now offers enterprise-grade data modeling, ETL capabilities, and DAX calculations comparable to dedicated BI tools. New formula-based aggregation functions (GROUPBY, PIVOTBY) and AI-powered Copilot features bring Excel even closer to purpose-built BI tools. The combination of pivot tables, advanced formulas, and interactive dashboards makes it a go-to solution for analysts who need to quickly transform raw data into actionable insights without leaving the familiar Excel environment.

What This Cheat Sheet Covers

This topic spans 23 focused tables and 186 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Core Lookup and Reference FunctionsTable 2: Dynamic Array FunctionsTable 3: Advanced Aggregation FunctionsTable 4: Statistical Functions for BITable 5: Text Manipulation FunctionsTable 6: Date and Time FunctionsTable 7: Custom Formula FunctionsTable 8: Pivot Table Core OperationsTable 9: Pivot Table Advanced FeaturesTable 10: Power Query (Get & Transform) FundamentalsTable 11: Power Query Advanced TransformationsTable 12: Power Pivot and Data ModelingTable 13: DAX Essential FunctionsTable 14: Conditional Formatting for BITable 15: Data Validation and QualityTable 16: Charts and VisualizationTable 17: What-If Analysis ToolsTable 18: Financial Functions for BITable 19: Excel Tables and Structured ReferencesTable 20: Named Ranges Best PracticesTable 21: Advanced Filtering TechniquesTable 22: Forecast and Trend AnalysisTable 23: AI and Copilot Features

Table 1: Core Lookup and Reference Functions

Function	Example	Description
XLOOKUP	`=XLOOKUP(A2, B:B, C:C, "Not Found")`	• Searches both horizontally and vertically with built-in error handling • modern replacement for VLOOKUP and HLOOKUP with reverse search capability.
INDEX MATCH	`=INDEX(C:C, MATCH(A2, B:B, 0))`	• Two-way lookup that works left or right • more flexible than VLOOKUP; allows lookups in any direction without column number dependencies.
IFERROR	`=IFERROR(VLOOKUP(A2,B:C,2,0),"Not Found")`	• Catches all Excel errors (#N/A, #VALUE!, #REF!, #DIV/0!, etc.) and returns a custom value • essential for clean dashboards.
IFNA	`=IFNA(XLOOKUP(A2,B:B,C:C),"Missing")`	• Catches only #N/A errors, leaving other errors visible • preferred over IFERROR when only lookup misses should be handled.
FILTER	`=FILTER(A:C, B:B>100, "No results")`	• Extracts rows matching criteria into dynamic array • multiple conditions supported using Boolean logic with multiplication or addition operators.

Table 1: Core Lookup and Reference Functions

Function	Example	Description
XLOOKUP	`=XLOOKUP(A2, B:B, C:C, "Not Found")`	• Searches both horizontally and vertically with built-in error handling • modern replacement for VLOOKUP and HLOOKUP with reverse search capability.
INDEX MATCH	`=INDEX(C:C, MATCH(A2, B:B, 0))`	• Two-way lookup that works left or right • more flexible than VLOOKUP; allows lookups in any direction without column number dependencies.
IFERROR	`=IFERROR(VLOOKUP(A2,B:C,2,0),"Not Found")`	• Catches all Excel errors (#N/A, #VALUE!, #REF!, #DIV/0!, etc.) and returns a custom value • essential for clean dashboards.
IFNA	`=IFNA(XLOOKUP(A2,B:B,C:C),"Missing")`	• Catches only #N/A errors, leaving other errors visible • preferred over IFERROR when only lookup misses should be handled.
FILTER	`=FILTER(A:C, B:B>100, "No results")`	• Extracts rows matching criteria into dynamic array • multiple conditions supported using Boolean logic with multiplication or addition operators.