Excel Pivot Tables
Pivot tables are the fastest and easiest way to quickly analyze data in Excel. This article is an introduction to Pivot Tables and their benefits, and a step-by-step guide with sample data.Read more
Dave Bruns
XLOOKUP with logical criteria
XLOOKUP can handle arrays natively, which makes it a very useful function when constructing criteria based on multiple logical expressions.
In the example shown, we are looking for the order number of the first order to Chicago over $250. We are constructing a lookup...Read more
XLOOKUP with multiple criteria
In this example, the goal is to look up a price using XLOOKUP with multiple criteria. To be more specific, we want to look up a price based on Item, Size, and Color. At a glance, this seems like a difficult problem because XLOOKUP only has one value for lookup_value and lookup_array...Read more
Define range based on cell value
This formula relies on a specific behavior of INDEX — although it seems that INDEX returns the value at a particular location, it actually returns a reference to the location. In most formulas, you wouldn't notice the difference – Excel simply evaluates the reference and...Read more
VLOOKUP if blank return blank
In this example, the goal is create a VLOOKUP formula that will return an empty cell when the lookup result is an empty cell.
When VLOOKUP can't find a value in a lookup table, it returns the #N/A error. You can use the IFNA function or...Read more
XMATCH Function
XMATCH is a modern replacement for the MATCH function. It is a flexible and versatile function with a number of useful features:
- The ability to look up values in vertical or horizontal ranges.
- Exact matching plus "next larger" and "next ...Read more
XLOOKUP Function
XLOOKUP is Excel's modern all‑in‑one lookup function. It lets you search a row or column for a value and retrieve the corresponding value from another range, without the frustrating limitations that plagued older functions like VLOOKUP, HLOOKUP, and LOOKUP. It can even return more than one...Read more
Conditional formatting based on another column
In this example, a conditional formatting rule highlights cells in the range D5:D14 when the value is greater than corresponding values in C5:C14. The formula used to create the rule is:
=$D5>$C5
The rule is applied to the entire range D5:...Read more
Last n weeks
In the image shown, the current date is August 24, 2019.
Excel dates are serial numbers, so they can be manipulated with simple math operations. The TODAY function always returns the current date.
Inside...Read more
Last n months
In this example, the goal is to create a formula that will return TRUE if a date is in the last complete 6 month period, starting in the previous month. This means the date must fall between a calculated start date and end date, which requires two logical tests. The formula uses the AND function...Read more