Explanation The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a #VALUE error. To workaround this limit you can use boolean logic and the LEFT, MID, and EXACT functions to parse and compare text. Note: this formula performs an exact match when both the lookup value and array values are greater than 255 characters. See below for other options....
Explanation The LARGE function is an easy way to get the nth largest value in a range: =LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest In this example, we can use the LARGE function to get a highest score, then use the score like a “key” to retrieve the associated name with INDEX and MATCH . Notice we are picking up the values for n from the range F5:F7, in order to get the 1st, 2nd, and 3rd highest scores....
Explanation 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. Note: I don’t know why the second argument for LARGE is called “k” ....
Transcript In this video, we’ll look at the recommended charts feature in Excel. The recommended charts button was added in Excel 2013. It provides access to more than 150 professionally design charts and, in general, it’s a good way to create new charts in later versions of Excel. The charts are good looking and in most cases they fit the data pretty well. To create a new chart in Excel with recommended charts, just select the data then the Recommended Charts button on the Insert tab of the ribbon....
About This Shortcut This shortcut will extend the selection around the active cell to include the “current region” around the active cell. See Ctrl A, and ⌘ A for very similar behavior. About This Shortcut The first time you use this shortcut, Excel will select the current region around the active cell, if one can be detected, otherwise the whole sheet is selected. The second time you use the shortcut (consecutively), Excel will select the entire worksheet....
About This Shortcut This shortcut will select all cells that don’t match the value in the active cell in a row or selection within a row. Note that this shortcut depends on the location of the active cell in a selection. You may want to start your selection with the active cell or move the active cell after the selection is made. About This Shortcut This shortcut will select all cells that don’t match the value in the active cell in a column, or selection within a column....
Purpose Return value Syntax =SORT(array,[sort_index],[sort_order],[by_col]) array - Range or array to sort. sort_index - [optional] Column index to use for sorting. Default is 1. sort_order - [optional] 1 = Ascending, -1 = Descending. Default is ascending order. by_col - [optional] TRUE = sort by column. FALSE = sort by row. Default is FALSE. Using the SORT function The SORT function sorts the contents of a range or array in ascending or descending order....
Explanation The goal of this example is to sum amounts by fiscal year, when the fiscal year begins in July. The first approach is a self-contained formula based on the SUMPRODUCT function. The second method uses SUMIF with column D as a helper column. Either approach will work correctly, and the best option depends on personal preference. Helper column To make the example easier to understand and to provide a simple way to use the SUMIF function (see below), column D is set up as a helper column that displays a fiscal year for each row, based on a July start....
Explanation In this example, the goal is to sum the last n columns in a set of data, where n is a variable that can be changed at any time. In the latest version of Excel, the easiest way to solve this problem is with the TAKE function . In older versions of Excel you can use the OFFSET function , as explained below. TAKE function The TAKE function returns a subset of a given array or range ....
Explanation Excel times are fractional numbers . This means you can add times together with the SUM function to get total durations. However, you must take care to enter times with the right syntax and use a suitable time format to display results, as explained below. Enter times in the correct format You must be sure that times are correctly entered in hh:mm:ss format. For example, to enter a time of 9 minutes, 3 seconds, type: 0:09:03....
Explanation At a high level, we use VLOOKUP to extract employee information in 4 columns with ID as the lookup value: =VLOOKUP($I$4,Table1,MATCH(H5,Table1[#Headers],0),0) The ID value comes from cell I4, and is locked so that it won’t change as the formula is copied down the column. The table array is Table1, with data in the range B5:F104. The column index is provided by the MATCH function. The match type is zero, so force VLOOKUP to perform an exact match....
Explanation At the core, this formula uses the COUNTIF function to count any cells that fall below a given value, which is hardcoded as 65 in the formula: COUNTIF(B5:F5,"<65") In this part of the formula, COUNTIF will return a positive number if any cell in the range is less than 65, and zero if not. In the range B5:F5, there is one score below 65 so COUNTIF will return 1....
Transcript Excel charts can have a surprisingly large number of parts, each with its own name. In this video, we’re going to break down a sample chart into parts, and give you the name for each. Here we have a basic column chart. The chart itself - the rectangle that holds everything else - is referred to as the Chart Area. When you select a chart, Excel will wrap the chart in a frame that contains eight handles....
Transcript In this video, we’ll look at how boolean algebra is used for AND and OR logic in formulas. In Boolean algebra, there are only two possible results for a math operation: 1 or 0, which, as we know, correspond to the logical values TRUE and FALSE. AND logic corresponds to multiplication . Anything multiplied by 0 is 0, and anything multiplied by 1 remains unchanged. If I multiply A times B, you can see how this works....
The Bubble Chart is a built-in chart type in Excel. Bubble charts are a special kind of XY chart that can display another data series which is used to scale the bubble (marker) plotted at X and Y values. You can think of a bubble chart as “X versus Y, scaled by Z”. Like a regular XY scatter chart, both axes are used to plot values – there is no category axis....
Transcript In this video we’ll look at how to apply conditional formatting to a table with a formula. Here we have a table that contains employee data. Let’s say we want to highlight people in this table who belong to group A. Conditional formatting works well in a table, but you can’t use structured references directly when you create the rule. I’ll use a helper column to help illustrate. To highlight people in group A using structured references, we’d want to use a formula like this:...
Explanation UTC timestamps like 2026-01-18T08:00:00Z are a common standard for representing dates and times, but Excel won’t correctly recognize this format without some help. If you try to apply date formatting to a UTC timestamp, nothing happens. In this example, the goal is to convert UTC timestamps to datetimes that Excel can recognize. In addition, we’ll look at how to convert UTC timestamps to datetimes in other time zones. What are UTC timestamp?...
Explanation This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the COUNTA function as an array like this: =COUNTA({"red";"amber";"green";"blue";"purple";"pink";"gray"}) Unlike the COUNT function , which counts only numbers, COUNTA counts both text and numbers. Since there are seven items in array , COUNTA returns 7....
Explanation The TEXT function takes numeric values and converts them to text values using the format you specify. In this example, we are using the format “mmm d” for both TEXT functions in E5. The results are joined with a hyphen using simple concatenation. Note: the other examples in column E all use different text formats. End date missing If the end date is missing, the formula won’t work correctly because the hyphen will still be appended to the start date (e....
Explanation Working from the inside-out, the WEEKDAY function takes a date and returns a number between 1 and 7. With default settings, the number 1 corresponds to Sunday and the number 7 corresponds to Saturday. The CHOOSE function simply maps numbers to values. The first argument is the number to map, and subsequent arguments represent associated values. In this case, 7 values have been provided in the order required to work with WEEKDAY’s Sunday through Saturday scheme....
