Looking for more? Try chatting with the Exceljet Chatbot.
In this example, the goal is to sum numeric values by month while ignoring the year that contains the date. The solution below is based on the SUMPRODUCT function, the MONTH function, and Boolean algebra. For convenience, amount...Read more
In this example, the goal is to extract the top 3 quiz scores for each name from the 5 scores that appear in columns C, D, E, F, and G. In other words, for each name listed, we want the best score, the 2nd best score, and the 3rd best score. This problem can be solved with the LARGE function....Read more
The Excel HSTACK function combines arrays horizontally into a single array. Each subsequent array is appended to the right of the previous array....Read more
In this example, the goal is to create an "locked" reference that won't change when columns or rows are added or deleted in a worksheet, or during a copy / paste / cut operation.
The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not...Read more
The Excel SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest value, etc.) from a set of numeric data.Read more
The Excel REPT function repeats characters a given number of times. For example, =REPT("x",5) returns "xxxxx".Read more
In this example, the goal is to count "z" or "c" values in the named range data, but only when the column header is "A" or "B". The formula used to perform this calculation is based on the SUMPRODUCT...Read more
Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use Boolean logic to create a lookup array on the fly composed...Read more
The Excel NOT function returns the opposite of a given logical or Boolean value. When given TRUE, NOT returns FALSE. When given FALSE, NOT returns TRUE. Use the NOT function to reverse a logical value.Read more
In this example, the goal is to retrieve information about the lowest three estimates in the data shown. The problem is that there are some duplicate values in the estimate column. This means we will have some trouble trying to display the names of the 2nd and 3rd lowest suppliers because the...Read more
The Excel WORKDAY function returns a date in the future or past that is a given number of working days from a specified start date, excluding weekends and (optionally) holidays. You can use the WORKDAY function to calculate things like start dates, delivery dates, and completion dates...Read more
The Excel LET function lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read and write....Read more
The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of find_text inside within_text as a number. SEARCH supports wildcards, and is not case-sensitive....Read more
In this article, we take a short tour of some "unconventional" pivot tables you probably haven't seen before. These are interesting pivot tables created to analyze something other than sales data.Read more
In this example, the goal is to get monthly currency exchange rates for a given currency pair (i.e., USD > EUR, USD > GBP, CAD > JPY, etc.). Currency abbreviations are entered in cells F5 and F6, and the start and end dates are entered in cells F7 and F8. If any of these four...Read more
The Excel GROUPBY function is designed to summarize data by grouping rows and aggregating values. The result is a summary table created with a single formula.Read more
Are nested IFs evil? Are they necessary? Are there alternatives? The answer is Yes! This in-depth article explores nested IF formulas in detail, with lots of tips, and several alternatives.Read more
If you open up a spreadsheet that you did not create, you may run into the "__xlfn" prefix before certain functions. The generic meaning of xlfn is "unsupported function". Typically, it indicates that the worksheet was created in a newer version of Excel and uses functions not yet available in...Read more
The CONCAT and TEXTJOIN functions make it possible to join values together in a range of cells. Since a range is just an array, this opens the door to some interesting new formulas that loop through values.Read more
Although VLOOKUP is a pretty simple function, there is plenty that can go wrong. Quickly learn more than 20 things you should know about VLOOKUP to make your experience pleasant and profitable.Read more
The Excel WORKDAY.INTL function returns a date in the future or past that is a given number of working days from a specified start date, excluding weekends and (optionally) holidays. Unlike the simpler WORKDAY function, WORKDAY.INTL can be...Read more
