Looking for more? Try chatting with the Exceljet Chatbot.
In this example, the goal is to look up and retrieve an entire row of values in a set of data. For example, when a value like "Neptune" is entered into cell H5, all values in the range C11:F11 should be returned. For convenience and readability, project (B5:B16) and data...Read more
The Excel LOWER function converts a text string to all lowercase letters. Numbers, punctuation, and spaces are not affected.Read more
In this example, the goal is to extract the nth word from the text string given in column B. The article below explains two approaches. The first approach is based on the new TEXTSPLIT function, which makes it very...Read more
The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6-character codes. The...Read more
The general goal is to return the first text value in a range. Specifically, we have dates in column B and some city names in column C. We want a formula to find the first city listed in the range C5:C16. Because some cells in C5:C16 are empty, and some contain zeros, we need to ignore...Read more
In this example, the goal is to calculate a running total in column D of the worksheet as shown. A running total, or cumulative sum, is a set of partial sums that changes as more data is collected. Each calculation represents the sum of values up to that point. In this example, each calculation...Read more
The goal is to generate a series of "custom" days of the week based on a start date entered in cell B5. For example, you might want to list sequential dates for any of the following combinations of days:
In this article, we explain how to use concatenation to display friendly and useful messages in your spreadsheets. The messages are dynamic and respond instantly to changes, so the effect is polished and professional.Read more
In this example, the goal is to count the number of cells in B5:B15 that contain a given number of characters, where the number of characters n is provided as a variable in cell E4.
One way to solve this problem is to use the ...Read more
In this example, the goal is to generate a list of the sheet names in an Excel workbook with a formula. Unfortunately, there is no simple way to do this with a formula in Excel. However, it can be done with a two-step approach:
In this example, the goal is to calculate the time remaining before an expiration date. There are many ways to do something like this in Excel, and in this article, we'll look at three different approaches:
An interesting problem in Excel is how to look up information related to the maximum value in a set of data. For example, if you have a dataset of property listings and prices, you might want to find details about the property with the highest price. The best way to solve this problem depends on...Read more
With the introduction of Dynamic Arrays in Excel formulas, there is more emphasis on arrays. The #CALC! error occurs when a formula runs into a calculation error with an array. The #CALC! error is a "new" error...Read more
The Excel MINUTE function extracts the minute component of a time as a number between 0-59. For example, with a time of 9:45 AM, minute will return 45. You can use the MINUTE function to extract the minute into a cell, or feed the result into another function like the ...Read more
In this example, the goal is to get all names in a given group into the same row, in separate columns, as seen in the worksheet. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns using common values, which in this case are the...Read more
At the core, this formula builds a level 1 and level 2 number and concatenates the two numbers together with a period (".") as a separator. The result is a value like "1.1". The "level 1" number is generated with COUNTA like this:
...Read more
The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or unexpected in a spreadsheet. You may see #DIV/0!...Read more
In Excel 2010 or later, certain functions are meant to be replaced with new functions to provide better results and names which (theoretically) make more sense. For example, the RANK function, which generates a rank for number against a range of numbers is...Read more
In this example, the goal is to count birthdays by year. The source data is an Excel Table named data in the range C5:C16. The birthdays we want to count are in the Birthday column. In column E, the years of interest have been previously ...Read more
This formula relies on the FILTER function to retrieve data based on a logical test built with the COUNTIF function:
=FILTER(list1,COUNTIF(list2,list1))working from the inside out, the...Read more
In this example, the goal is to display the top 3 values in C5:C16 that match a specific group, entered as a variable in cell F4. If the group is changed, the formulas should calculate new results. For convenience, group (B5:B16) and value (C5:C16) are...Read more
In this example, the goal is to calculate durations in "days" starting from the start date and time in cell G5 and ending at the dates and times shown in column B. The twist is that we want to classify the durations using the custom labels shown in column E, starting with "Day 0" for the first...Read more
The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message, or run a different calculation when an error occurs....Read more
In this example, the goal is to sort a list of names and birthdays by month and year. The complication is that the birthdays also include a birth year, so if we try to sort the raw data by birthdays, we'll end up with a list of birthdays sorted first by year. We will actually see the oldest...Read more
In this example, the goal is to replace the comma separated values in column B with the line break separated values seen in column D. In a problem like this, the first step is to identify the delimiter, which is the character (or characters), that separate each value we want to...Read more
In this example, the goal is to get the minimum value for each group in the data as shown. The easiest way to solve this problem is with the MINIFS function. However, there are actually several options. If you need more flexibility (you need to work with arrays instead of ranges), you can...Read more
In this example, the goal is to create initials or acronyms with a formula using the data in column B as the source text. The formula should parse the text in column B, build a list of capital letters used to start words and join the capital letters together in a single text string. The article...Read more
In this example, the goal is to display the biggest 3 gainers and losers in a set of data where Start and End columns contain values at two points in time, and Change contains the percentage change in the values. The data in B5:E16 is defined as an Excel Table...Read more
In this example, the goal is to look up and retrieve an entire column of values in a set of data. For example, when a value like "Q3" is entered into cell H4, all values in the range E5:E16 should be returned. For convenience and readability, quarter (C4:F4) and ...Read more