This advanced Excel cheat sheet helps you analyze data, build reliable formulas, automate repetitive work, and create faster spreadsheet workflows. Use it to reference XLOOKUP, PivotTables, dynamic arrays, conditional formulas, data cleaning, charts, and other advanced Excel tools.
The examples are designed for analysts, business users, students, and spreadsheet professionals who want practical solutions they can apply directly in Microsoft Excel and Google Sheets.
How to Use This Advanced Excel Cheat Sheet
Start with the formulas and data-analysis tools that match your current task. Use lookup functions to connect datasets, dynamic arrays to create flexible results, PivotTables to summarize information, and conditional formulas to test multiple business rules.
Test formulas on a copy of important data before applying them to an entire workbook. Check cell references, data types, regional separators, and compatibility because some functions behave differently across Excel versions and Google Sheets.
[BUTTON: Download Advanced PDF Cheat Sheet]
1. Advanced Lookup & Reference Functions
Forget old-school limitations. These functions allow you to look up data in any direction, across multiple criteria, without breaking your sheets.
Figure: How XLOOKUP works in 4 simple steps: (1) Search value, (2) Formula syntax, (3) Lookup table, and (4) Returned result.
| Function & Syntax | What it Does | Real-World Example |
|---|---|---|
XLOOKUP=XLOOKUP(value, lookup_range, return_range, [if_not_found]) |
Searches a range for a match and returns data from another column. Can look to the left and defaults to exact match. | =XLOOKUP("Emp_102", A:A, C:C, "Missing")Finds employee ID in column A and returns their salary from column C. |
INDEX & MATCH=INDEX(return_range, MATCH(value, lookup_range, 0)) |
The bulletproof alternative to VLOOKUP for older Excel versions. INDEX specifies the target, MATCH finds the row number. | =INDEX(D:D, MATCH("Product_X", B:B, 0)) |
IMPORTRANGE (Sheets)=IMPORTRANGE(spreadsheet_url, range) |
Dynamically pulls data from an entirely separate Google Sheets file into your current workbook via the cloud. | =IMPORTRANGE("https://google.com...", "Sales!A1:G50") |
2. Dynamic Arrays & Filtering (Modern Excel & Sheets)
Dynamic array formulas allow you to write a single formula in one cell, and the results automatically “spill” into the surrounding cells.
Figure: How the FILTER function handles dynamic arrays, automatically spilling the extracted matching data into a defined Spill Range (blue border).
| Function & Syntax | What it Does | Why it is a Game Changer |
|---|---|---|
FILTER=FILTER(array, include, [if_empty]) |
Extracts all rows from a dataset that meet specific conditions instantly, without using manual filters. | =FILTER(A2:D100, C2:C100="Europe")Creates a live list of all data belonging to the Europe region. |
UNIQUE=UNIQUE(range) |
Scans a messy column and returns a clean list of every unique value, removing duplicates automatically. | =UNIQUE(B2:B500)Instantly extracts a list of all unique client names from a database. |
SORT=SORT(range, column, ascending) |
Sorts a matrix or range automatically based on the values in a specific column. | =SORT(FILTER(A2:C10, C2:C10>50), 3, FALSE)Filters data and sorts it by the 3rd column in descending order. |
3. Power Logic & Nesting Formulas
Real data analysis requires combining multiple conditions. These formulas let you build advanced IF/THEN business rules.
1. IFS (Multi-Condition Logic)
Replaces messy, deeply nested IF statements. Evaluates multiple conditions in order.
- Syntax:
=IFS(condition1, value1, condition2, value2, ...) - Example:
=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE, "F") - What it does: Assigns grades based on a score. The final
TRUEacts as an “Else” statement for anything below 70.
2. SUMPRODUCT (Array Multiplier)
Multiplies corresponding components in given arrays and returns the sum of those products. Excellent for calculating weighted averages.
- Syntax:
=SUMPRODUCT(array1, array2) - Example:
=SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10) - What it does: Multiplies item price (B) by quantity sold (C), then divides by total quantity to find the weighted average price.
4. Pro-Tips for Advanced Spreadsheet Optimization
- Kill the Formula Drag: Instead of dragging a formula down 10,000 rows, use
=ARRAYFORMULA(A2:A100 * B2:B100)in Google Sheets to calculate the entire column inside one single cell. - The Secret QUERY Function (Sheets Only): Use
=QUERY(A2:E100, "SELECT A, C WHERE E > 500 ORDER BY C DESC")to run actual SQL-style databases inside your spreadsheet. - Hardcode to Save Memory: If your workbook is lagging due to thousands of advanced formulas, select the data, press
Ctrl + C, then pressCtrl + Alt + V(Windows) orCmd + Option + V(Mac) and choose Values to flatten the calculations into static data.
Advanced Excel Cheat Sheet Resources
Use these official resources alongside the advanced Excel cheat sheet to verify functions, compare spreadsheet features, and continue learning: