Explanation In the worksheet shown, we have a list of values in column C. The goal is to dynamically average the last n values using the numbers in cell E5 for n . Since the list may grow over time, the key requirement is to average amounts by position. For convenience only, the values to average are in the named range data (C5:C16). In the latest version of Excel, the best way to solve this problem is with the TAKE function , a new dynamic array function in Excel....

Explanation This formula is a good example of how structured references can make working with data in Excel much easier. At the core, this is what we’re doing: =AVERAGE(first:last) where “first” is a reference to the first cell to include in the average and “last” is a reference to the last cell to include. The result is a range that includes the N cells to average. To get the first cell in the range, we use INDEX like this:...

Explanation The REPT function simply repeats values. For example, this formula outputs 10 asterisks: =REPT("*",10) // outputs ********** You can use REPT to repeat any character(s) you like. In this example, we use the CHAR function to output a character with a code of 110. This character, when formatted with the Wingdings font, will output a solid square. CHAR(110) // square in Wingdings To calculate the “number of times” for REPT, we scale values in column C by dividing each value by 100....

Explanation In this example, the goal is to perform a case-sensitive lookup on the name in column B, based on a lookup value entered in cell E5. By default, Excel is not case-sensitive. This means that standard lookup functions like VLOOKUP , XLOOKUP , and INDEX and MATCH are also not case-sensitive. These formulas will simply return the first match, ignoring upper and lower case. The classic way to work around this limitation is to build a lookup formula that incorporates the EXACT function, which performs a case-sensitive comparison....

Explanation In this example, we have a simple set of data in B5:D16 that includes ID, Name, and Points. The goal is to generate a custom message for any name in the list by entering a valid ID in cell G5. The message uses the name from column C and the points in column D like this: "Hi [name], you have [points] points." If points are over 300, the message is extended:...

Explanation This example uses the UNIQUE function . With default settings, UNIQUE will output a list of unique values, i.e. values that appear one or more times in the source data. However, UNIQUE has an optional third argument, called occurs_once that, when set to TRUE, will cause UNIQUE to return only values that appear once in the data. In the example shown, UNIQUE’s arguments are configured like this: array - B5:B16 by_col - FALSE occurs_once - TRUE Because occurs_once is set to TRUE, UNIQUE outputs the 2 values in the data that appear just once: “purple”, and “gray”....

Purpose Return value Syntax =EVEN(number) number - The number to round up to an even integer. Using the EVEN function The EVEN function rounds numbers up to the next even integer. EVEN always rounds numbers away from zero, so positive numbers become larger and negative numbers become smaller (i.e. more negative). EVEN takes just one argument, number , which should be a numeric value. With positive numbers, EVEN rounds number up to the next even integer....

I run a free email newsletter focused on solving problems with Excel formulas. Each week, I take a detailed look at how to solve a specific problem with an Excel formula. The emails go out Friday morning. Below is a summary of recent emails to give you an idea of what I talk about. No spam, and you can unsubscribe at any time. Sign up below .

Explanation In this example, the goal is to extract the time portion of a date that contains time (also called a “datetime”). Since dates in Excel are serial numbers and times are fractional values of a day, the task is to extract the decimal portion of the serial number. This is easy to do with the MOD function, and other methods mentioned below. Note that if you are comparing extracted times to other time values, there is a subtle floating point precision issue you should be aware of....

About This Shortcut This shortcut copied data from the cells above to cells below, without having to copy and paste. To fill down into more than one cell at once, make a selection that includes multiple rows below. Cell references in formulas will update normally. Source and target cells do not need to be next to each other, but you’ll need to select the target cell first before the source cell in this case....

Purpose Return value Syntax =GCD(number1,[number2],...) number1 - The first number. number2 - [optional] The second number. Using the GCD function The GCD function returns the greatest common divisor of two or more integers. The greatest common divisor is the largest positive integer that divides the numbers without a remainder. In other words, the largest number that goes into all numbers evenly. The GCD function takes one or more arguments called number1 , number2 , number3 , etc....

Explanation In this example, the goal is to extract the first name from names that appear in format, where the middle name is optional. The easiest way to do this is with the newer TEXTBEFORE function. In older versions of Excel, you can use an alternative formula based on the LEFT function and the FIND function. Both formulas are explained below. Note: this formula does not account for titles (Ms., Mr....

Explanation In this example, the goal is to return the fiscal year for any given date, where a fiscal year starts in July as seen in the worksheet. By convention a fiscal year is denoted by the year in which it ends. So, if a fiscal year begins in July, then the date August 1, 2018 is in fiscal year 2019. The formula in D5, copied down, is: =YEAR(B5)+(MONTH(B5)>=C5) On the left, the YEAR function first returns the year from the date in B5:...

Explanation In this example, the goal is to calculate the “original” value when the current value and percentage change are known. For example, if we know the current value is 1100, after an increase of 10%, we want to calculate the original value (1000) with an Excel formula. Although the language may vary, this kind of formula can be used in different ways: To calculate an original value based on a known percentage change....

Transcript Pivot table slicers provide the same function as filters - they allow you to selectively display and hide the items that appear in fields. However, instead of drop-down menus, they provide large friendly buttons that are always visible. Let’s take a look. Here we have a pivot table that shows sales by Product. If we wanted to filter the entire pivot table by Region, we know from an earlier video that we could just add Region as a Report Filter, and use it to filter as needed....

Transcript Excel ships with a number of pre-built pivot table styles. Using these styles, you can format an entire pivot table with a single click. Let’s take a look. Pivot table styles are available on the Design tab of the PivotTable Tools ribbon. To apply a pre-built style, select any cell in the pivot table and navigate to the Design tab. All available styles are listed in the PivotTable styles group....

In this video, we look at some tips for building more complex formulas and also for keeping things simple. You can download the worksheet I used in the training below. More formula videos in this series: Excel formulas - 5 ways to use VLOOKUP How to build logical formulas How to build a complex formula (or not) 20+ formula tips More formula training We cover many more formula techniques in our premium course on Excel formulas, Core Formula ....

Explanation The #NUM! error occurs in Excel formulas when a calculation can’t be performed. For example, if you try to calculate the square root of a negative number, you’ll see the #NUM! error. The examples below show formulas that return the #NUM error. In general, the fixing the #NUM! error is a matter of adjusting inputs as required to make a calculation possible again. Example #1 - Number too big or small Excel has limits on the smallest and largest numbers you can use....

Transcript Once a conditional formatting rule has been created, it can be easily revised. You can change the condition that triggers the formatting, the formatting itself, and the cells that the conditional format applies to. Let’s take a look. Once a conditional formatting rule is defined, it can be revised or deleted. To see and edit a conditional formatting rule, select Manage Rules from the Conditional Formatting menu. By default the Manage Rules dialog box is set to the Current Selection....

Purpose Return value Syntax =IMEXP(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMEXP function The Excel IMEXP function returns the exponential of a complex number. For example, given “0+πi” as input, the function returns “-1+3.23108914886517E-15i” as output. The output is essentially -1, but due to floating point precision, it contains a very small imaginary component. =IMEXP(COMPLEX(0,PI())) // returns -1 + 3.23108914886517E-15i Given real number input, the function behaves like the exponential function and models exponential growth....