The IMPORTCSV function imports data from a comma-separated (CSV) file into Excel and returns the result as a dynamic array that spills onto the worksheet. IMPORTCSV reads from a local file path or a URL. The delimiter is always a comma and
Summary To create a data validation rule that allows only letters and numbers (no punctuation, symbols, or spaces), you can use a custom formula based on the FIND, SEQUENCE, and LET functions. In the example shown, the data validation appli…
Summary To list every possible combination of items from multiple lists, you can use a formula based on the SEQUENCE function, the INDEX function, and the HSTACK function. In the example shown, the goal is to list every way to combine one s…
Combinations and permutations are two basic ways to count possibilities, used in probability, statistics, games, passwords, and lotteries. This article explains the difference between them, when order and repetition matter, and the Excel fu…
Summary To summarize granular weather station data by day, you can use the GROUPBY function together with a few helper functions. In the worksheet shown, the source data is in an Excel Table named data on the data sheet, with one reading ev…
The BYROW function applies a function to each row in array and returns one result per row in a single array. For example, if BYROW is given a range with 10 rows, it returns an array of 10 results, one per row. The calculation performed on e…
Excel has supported the complex number system for years. Engineers use it to solve problems related to electronics, fluid dynamics, and signal processing. The way Excel's formula engine implements complex numbers is an example of functional…
To match and summarize noisy data in Excel, where the same value appears in many different forms, you can use a map table together with the XLOOKUP function and the GROUPBY function. In the example shown, the formula in cell F5 is: where da…
If VLOOKUP finds more than one match, will you get the first match or the last match? It's a trick question. It depends :) This article explains this confusing topic in detail, with lots of examples. In the current version of Excel you can
Excel Tables are one of the most interesting and useful features in Excel. If you need a range that expands to include new data, and if you want to refer to data by name instead of by address, Excel Tables are for you. This article provides…
Many Excel experts believe that Pivot Tables are the single most powerful tool in Excel. This article provides more than 20 tips you should know to work productively with Excel Pivot Tables. Pivot tables are a reporting engine built into Ex…
If you use Excel formulas on a regular basis, you need to understand arrays. Arrays used to be an advanced topic, something only power users worried about. But with the introduction of dynamic arrays, arrays are now a central part of how Ex…