Master Excel: Advanced Formulas Cheat Sheet PDF
Mastering advanced Excel formulas is like having a secret superpower - you can perform complex calculations, automate tasks, and unlock insightful data analysis with just a few clicks. Whether you're aiming to become an Excel ninja or simply want to streamline your work, having a trusty advanced Excel formulas cheat sheet at hand can be priceless. In this comprehensive guide, we'll dive into some of the most powerful Excel formulas, categorized for easy reference. Download your PDF cheat sheet at the end to keep these formulas handy.
Before we jump in, ensure you're using Excel 2016 or later, or Excel Online. Most of these formulas are compatible with these versions. Now, let's explore these advanced Excel formulas, grouped into manageable sections.
Data Analysis & Lookup Formulas
Data analysis is the heart of many Excel workbooks. Let's start with some powerful lookup formulas that help extract and manipulate data.
INDEX & MATCH
The dynamic duo, INDEX and MATCH, together replace the need for VLOOKUP or XLOOKUP in most scenarios. INDEX returns a value from a table, and MATCH returns the relative position of a specified item in a range.
Syntax: =INDEX(array, [match_mode], [match_type])
- array: The range of cells from which you want to return a value.
- match_mode: (Optional) The rows or columns to include in the array. Defaults to 1 (rows).
- match_type: (Optional) The matching criteria. Defaults to 0 (exact match).
For example, suppose you want to retrieve the quantity of Product A from the table below (array):
| Product | Category | Quantity |
|---|---|---|
| A | Fruits | 100 |
| B | Vegetables | 200 |
The formula =INDEX(A2:C4, MATCH("A", A2:A4, 0), 3) would return 100.
XLOOKUP
Introduced in Excel 365 and Excel 2021, XLOOKUP is a versatile function that replaces multiple legacy functions like VLOOKUP, HLOOKUP, and INDEX + MATCH.
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
It's more flexible and easier to use than its predecessors. For example, XLOOKUP can look up vertically (Historia: XLOOKUP("Colleagues", A2:A30, B2:B30)) or horizontally (Years: XLOOKUP("2021", B2:G1, A2:G1, "Not Found: Revenue is hidden!")) while returning a different range.
Conditional & Text Formulas
Next, let's explore some conditional and text formulas that enable you to clean, manipulate, and analyze text data.
IFS & TEXTSPLIT
IFS is a catch-all formula that simplifies consecutive IF statements. Combine it with TEXTSPLIT to handle multiple conditions within a single cell.
Syntax: =IFS(condition_1, value_if_true_1, [condition_2, value_if_true_2]...
For example, Suppose you have a list of countries. You want to categorize them into continents based on the following rules:
- North and South America: "Americas"
- Europe: "Europe"
- Asian countries: "Asia"
- Other countries: "Other continents"
The formula =IFS(COUNTIF({"USA", "Canada"}, A2, "Americas"), COUNTIF({"Germany", "United Kingdom"}, A2, "Europe"), COUNTIF(FIND("China"), A2, "Asia"), TRUE, "Other continents") would return the desired continent for each country.
TEXTSPLIT returns an array of strings from text that matches a specified delimiter. For instance, TEXTSPLIT("apples;bananas;cherries;dates", ";") would return {"apples", "bananas", "cherries", "dates"}.
REGEXMATCH & SUBSTITUTE
Regular Expressions (Regex) allow for complex pattern matching, and REGEXMATCH tests whether a text value matches the specified pattern.
Syntax: =REGEXMATCH(text, pattern)
Combine it with SUBSTITUTE to replace text that matches a pattern. For example, to remove commas from a list of numbers, you can use =SUBSTITUTE(text_cell, REGEXREPLACE(A1, ",", "", "global")
Date & Time Formulas
Dates and times can be tricky to work with in Excel, but these formulas help simplify date and time manipulations.
YEARFRAC
YEARFRAC calculates the fraction of the year represented by a date or a series of dates. Use it to calculate the time between two dates, including partial years, months, and days.
Syntax: =YEARFRAC-start_date, [end_date], [basis]
Basis can be 0 (actual basis), 1 (360 basis), 2 (365 basis), or 4 (4-quarter basis).
For example, YEARFRAC("2020-12-31", "2022-01-01", 2) would return 0.9984, representing 99.84%.
DATEIF & NETWORKDAYS
DATEIF returns the serial number of a date within a given year, and NETWORKDAYS calculates the number of workdays or non-workdays between two dates.
DATEIF("20-09-2021", "2021") would return 20 (the 20th day of the year).
NETWORKDAYS("2021-09-01", "2021-12-31") would return 117, the number of workdays (weekday, non-holiday) between two dates.
Now that you've mastered these advanced Excel formulas, it's time to put them into practice. Download your personalized Advanced Excel Formulas Cheat Sheet PDF here to keep these formulas handy. Print it out, stick it on your wall, or use it as a reference in your digital notebook. Stay versatile, keep learning, and happy Exceling!