Advanced Excel & Google Sheets Cheat Sheet: Data Analysis Power Functions (Free PDF)

by

Advanced Excel & Google Sheets Cheat Sheet: Data Analysis Power Functions

Moving from a basic spreadsheet user to a data power user requires mastering logic, advanced lookups, and dynamic arrays. This advanced cheat sheet compiles the most powerful functions in modern Excel (Office 365) and Google Sheets. Master these tools to build automated dashboards, clean messy data, and analyze thousands of rows in seconds.

[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.

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.

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 TRUE acts 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 press Ctrl + Alt + V (Windows) or Cmd + Option + V (Mac) and choose Values to remove the heavy formulas while keeping the data.

5. Frequently Asked Questions (FAQ)

What causes a #SPILL! error?

A #SPILL! error occurs when a dynamic array formula (like FILTER or UNIQUE) tries to output multiple rows of data, but there is already text or data sitting in one of the cells below it. Clear out the cells underneath the formula, and the data will instantly spill down.

How do I join text from multiple cells together safely?

Stop using &. Use =TEXTJOIN(", ", TRUE, A2:A10) instead. It lets you choose a delimiter (like a comma) and has a built-in setting (TRUE) to automatically skip empty cells.

How do I handle multiple criteria inside an XLOOKUP?

You can use the boolean & operator inside XLOOKUP.
Syntax: =XLOOKUP(Value1 & Value2, Range1 & Range2, Return_Range). This removes the need to create clunky helper columns in your sheets.

Leave a Comment