About This Shortcut This shortcut is an alternative to pressing enter/return when entering data. It’s useful when the next value you want to enter is to the right. About This Shortcut This shortcut is an alternative to pressing enter/return when entering data. It’s useful when the next value you want to enter is to the left.
Explanation In this example, the goal is to extract a set of records that match a partial text string . To keep things simple, we are only matching one field in the data, the last name (“Last”). The core operation of this formula comes from the FILTER function (new in Excel 365 ) which extracts matching data from a range based on a logical filter: =FILTER(data,logic) The challenge in this example is to construct the logic needed to match records based on a partial match....
Explanation The goal is to identify invoice numbers in range D5:D11 that are missing in range B5:B16 (named list ). Two good ways to solve this problem in Excel are the COUNTIF function and the MATCH function . Both approaches are explained below. COUNTIF function COUNTIF counts cells in a range that meet a given condition (criteria). If no cells meet the criteria, COUNTIF returns zero. The generic syntax for COUNTIF is:...
Explanation This is a pretty standard use of the SUMIFS function. In this case, we need to sum amounts based on two criteria: type (forecast or actual) and group. To sum by type, the range/criteria pair is: type,G$4 where type is the named range D5:D14, and G4 is a mixed reference with the row locked in order to match the column header in row 4 when the formula is copied down....
Explanation The EOMONTH function returns the last day of a month based on a given date. The 2nd argument is months , which specifies how many months in the future or past to move before returning the last day. By traveling back 2 months, then adding one day, we can calculate the first day of the previous month from any given date. In the example shown, months is supplied as -2, which causes EOMONTH to return 4/30/2015....
Explanation FIND returns the position (as a number) of the first occurrence of a space character in the text. This position, minus one, is fed into the LEFT function as num_chars . The LEFT function then extracts characters starting at the left side of the text, up to (position - 1). Handling one word If a cell contains only one word, this formula returns an error. One way to fix this problem is to wrap the original formula in the IFERROR function like so:...
About This Shortcut Movement is to the left through worksheets and will stop at the last worksheet to the left. To move to the first tab/worksheet in a workbook, hold down the control key and click the left navigation arrow in the lower left corner of the workbook. Mac Excel As of November 2025, there seems to be a conflict with Microsoft’s published shortcuts Control + PageDn (Next worksheet) and Control + PageUp (Previous worksheet)....
Explanation When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the address of the active cell (B5) is used for the row (5) and entered as a mixed address , with column D locked and the row left relative. When the rule is evaluated for each of the 40 cells in B5:E12, the row will change, but the column will not....
Transcript In this video we’ll look at how to calculate and highlight expiration dates. Let’s say your company has started a membership program of some kind and your boss just sent you a set of data. She’s given you a list of 1,000 people that have renewed a membership in the last year or so, and she’s looking for several things. First, she wants you to calculate an expiration date one year in the future, on the last day of the same month that membership was last renewed....
Transcript When you’re building formulas in Excel, it’s common to reference cells that are on different worksheets, or even in different workbooks. Let’s take a look. Here we have a workbook with five weeks of test scores for a group of students, and a summary sheet that’s been set up to hold test scores for all five weeks. What we need to do is create formulas on the Summary sheet that pull in the correct values from the other sheets....
Transcript In this lesson we’ll look at how to format cells with a particular font and to change the font size. Let’s take a look. Here we have a simple coffee menu. Let’s improve the formatting by applying some custom fonts. To apply a font to one or more cells, first select the cells you’d like to format. In this case, let’s start by applying a new font to the entire worksheet....
Transcript Unfortunately, there is no built-in way to move a custom pivot table style to another workbook. However, there is a simple workaround you can follow when you want to use a style again in a different workbook. Let’s take a look. Here we have the custom pivot table style that we created and applied to this pivot table earlier. We’d like to use this same style in a different workbook....
About This Shortcut Moves current selection one screen to the left of the active selection. The distance covered depends on the size of the window. About This Shortcut Moves current selection one screen above the active selection. The distance covered depends on the size of the window. If there are 25 rows visible (of the same height), the active selection will move 25 rows up.
About This Shortcut Moves current selection one screen to the right of the active selection. The distance covered depends on the size of the window. About This Shortcut Moves current selection one screen to the left of the active selection. The distance covered depends on the size of the window.
Explanation In this example, the XOR function contains two expressions, one to test for an “x” in column C, and one to test for an “x” in column D. C5="x" // TRUE if coffee is "x" D5="x" // TRUE if tea is "x" With two logical criteria, XOR has a particular behavior, summarized in the table below: Coffee Tea Result TRUE FALSE TRUE FALSE TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE At each row in column E, XOR evaluates values in columns C and D and returns a TRUE or FALSE result....
About This Shortcut This shortcut launches the Excel help system. On Windows, use the F1 key to launch Excel’s help system. On a Mac, use Command + /. Note: in Excel 2016 for Mac, you can use F1 to launch help. About This Shortcut This shortcut will allow multiple levels of undo; each time you use it Excel will step back one level.
Purpose Return value Syntax =PROB(x_range,prob_range,lower_limit,[upper_limit]) x_range - A range of numeric values representing the possible outcomes. prob_range - A range of probabilities corresponding to the values in x_range (must sum to 1). lower_limit - The lower bound for the probability calculation. upper_limit - [optional] The upper bound for the probability calculation. If omitted, PROB returns the probability that x equals lower_limit. Using the PROB function The PROB function calculates probabilities for discrete probability distributions by summing the probabilities of all values in the x_range that fall within the specified limits....
Explanation In this example, the goal is to return a random group (“A”, “B”, or “C”) at each new row. The simplest way to do this is to use the RANDBETWEEN function with the CHOOSE function. In the current version of Excel, it is also possible to generate all random groups in one step with the RANDARRAY function. Both approaches are explained below. The CHOOSE function The CHOOSE function returns a value from a list of values using an index number....
Purpose Return value Syntax =REGEXEXTRACT(text,pattern,[return_mode],[case_sensitivity]) text - The text value to extract from. pattern - The pattern to extract. return_mode - [optional] 0 = first match, 1 = all matches, 2 = capture groups. case_sensitivity - [optional] 0 = Case sensitive, 1= Case-insensitive. Default is 0. Using the REGEXEXTRACT function The REGEXEXTRACT function extracts text matching a specific regex pattern from a given text string. For the advanced Excel user, this function is a major upgrade....
Explanation The Excel CEILING function rounds a number up to a given multiple. The multiple to use for rounding is given as the second argument ( significance ). If the number is already an exact multiple, no rounding occurs. In this example, we want to round up to the nearest half, so we provide 0.5 to CEILING as the multiple. In the example shown, the formula in C5 is: =CEILING(B5,0.5) // returns 1....
