Explanation Note: FILTER is a new dynamic array function in Excel 365 . In other versions of Excel, there are alternatives , but they are more complex. In this example, the goal is to filter the data shown in B5:G15 by year, then sort the results in descending order. In addition, the result should include the Group column, sorted in the same way. The problem breaks down into two main steps:...
Explanation The FILTER function can filter data using a logical expression provided as the include argument. In this example, this argument is created with an expression that uses the ISNUMBER and MATCH functions like this: =ISNUMBER(MATCH(color,list,0)) MATCH is configured to look for each color in C5:C15 inside the smaller range J5:J7. The MATCH function returns an array like this: {1;#N/A;#N/A;#N/A;2;3;2;#N/A;#N/A;#N/A;3} Notice numbers correspond to the position of “found” colors (either “red”, “blue”, or “black”), and errors correspond to rows where a target color was not found....
Explanation The FV function is a financial function that returns the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate. An annuity is a series of equal cash flows, spaced equally in time. In this example, a $5000 payment is made each year for 25 years, with an interest rate of 7%....
Purpose Return value Syntax =FVSCHEDULE(principal,schedule) principal - The initial investment sum. schedule - Schedule of interest rates, provided as range or array. Using the FVSCHEDULE function The FVSCHEDULE function calculates the future value of a single sum based on a schedule of interest rates. The interest rates can vary in each period. As such, FVSCHEDULE can be used to find the future value of an investment with a variable or adjustable rate....
Explanation In this example, the goal is to return an array with nine subtotals, one for each of the colors named in column B. The numbers to sum are contained in data which is the named range C5:I13. This is an example of a problem where the goal is to create an array of sums rather than a single sum. We can’t use a function like SUM by itself, because SUM will aggregate results and return a single value....
Explanation This formula uses the NETWORKDAYS function calculate total working days between two dates, taking into account weekends and (optionally) holidays. Holidays, if provided, must be a range of valid Excel dates. Once total work days are known, they are simply multiplied by a fixed number of hours per day, 8 in the example shown. The NETWORKDAYS function includes both the start and end date in the calculation, and excludes both Saturday and Sunday by default....
Transcript In this video, we’ll look at how to build a clustered column chart in Excel. A clustered column chart groups multiple date series by category in vertical columns. Clustered column charts can be a good way to show trends in each category, when the number of data series and categories is limited. In this worksheet, we have data that represents quarterly sales across 4 regions: East, West, North, and South....
Transcript In this video, we’ll look at how to build a combo chart. What is a combo chart? A combo chart is just a name for a chart that uses more than one chart type. For example, a column chart combined with a line chart. Here we have annual financial data for Google, including Revenue, Net Income, and Profit Margin. To give you a little context, I’m going to build a straight column chart first, so you can see why you might want to use a combo chart....
Transcript Another example of a situation where you may need a mixed reference is a running total. Let’s take a look. With normal totals, you can just sum a range and be done. The SUM function adds together all the values in the range and reports the result. But what if you want to create a running total? Well, you could enter the first total as a custom formula, and then enter the rest of the formulas so that they add the current month to the previous total....
Explanation The goal is to identify dates in column B that fall between a given start date and end date. The start and end dates are exposed as inputs on the worksheet that can be changed at any time, labeled “Start” and “End” in the example shown. Named ranges For convenience, both start (E5) and end (E8) are named ranges that can be used directly in the formula. If you prefer not to use named ranges, use absolute references like $E$5 and $E$8 to prevent these references from changing as the formula is copied down the table....
Explanation Note: in the current version of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER function. The core of this formula is the INDEX function, with AGGREGATE used to figure out the “nth match” for each row in the extraction area: INDEX(data,nth_match_formula) Almost all of the work is in figuring out and reporting which rows in “data” match the search string, and reporting the position of each matching value to INDEX....
Purpose Return value Syntax =ISNA(value) value - The value to check if #N/A. Using the ISNA function The ISNA function returns TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type. The ISNA function takes one argument, value , which is typically a cell reference. Examples If A1 contains the #N/A error, ISNA returns TRUE: =ISNA(A1) // returns TRUE ISNA returns FALSE for other values and errors:...
About This Shortcut This keyboard shortcut will cycle through every cell in a selection, moving from top left to bottom right. About This Shortcut This keyboard shortcut will cycle through every cell in a selection, moving from bottom right to top left.
About This Shortcut This shortcut performs two important actions in Excel: It converts formatted text (i.e., italic, bold, font, etc.) to plain text. It converts formulas to values. Traditionally, the Paste Special dialog box has been the easiest way to do this. However, this shortcut removes the extra step of selecting “Values”, performing Paste Special > Values in a single step. The result depends on the content of the clipboard:...
Purpose Return value Syntax =PRICE(sd,md,rate,yld,redemption,frequency,[basis]) sd - Settlement date of the security. md - Maturity date of the security. rate - Annual coupon rate. yld - Annual required rate of return. redemption - Redemption value per $100 face value. frequency - Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4. basis - [optional] Day count basis (see below, default =0). Using the PRICE function The Excel PRICE function returns the price per $100 face value of a security that pays periodic interest....
About This Shortcut This shortcut displays the Print pane in the File tab in windows. On a Mac, it displays the Print dialog box. About This Shortcut Use this shortcut to open the print preview window. In later versions of Excel on Windows, Control + P has the same result.
Purpose Return value Syntax =REGEXREPLACE(text,pattern,replacement,[occurrence],[case_sensitivity]) text - The text value to process. pattern - The pattern to replace. replacement - The text to replace with. occurrence - [optional] The instance to replace. Default = 0 = all instances. case_sensitivity - [optional] 0 = Case sensitive, 1= Case-insensitive. Default is 0. Using the REGEXREPLACE function The REGEXREPLACE function replaces text matching a specific regex pattern in a given text string. You can think of REGEXREPLACE as a much more powerful version of the simplistic SUBSTITUTE function ....
Explanation The goal is to remove the forward-slash ("/") from the URLs in column B when it is present as the last character. When a URL does not end with a forward slash ("/") the original URL should be returned without modification. Despite the fact that Excel offers many functions designed to work with text strings, there is no entirely straightforward way to solve this problem. The simplest method is to use a formula based on the LEFT function with help from LEN and RIGHT....
Explanation The goal is to use a formula to check if all cells in a row are blank or empty and return TRUE or FALSE. One way to solve this problem is with the SUMPRODUCT function, as seen in the worksheet above. Another approach is to use the newer BYROW function. Both methods are described below. SUMPRODUCT function The SUMPRODUCT function is a Swiss Army Knife function that appears in all kinds of formulas because it can handle many array operations natively in older versions of Excel....
Explanation The core of this formula is the COUNTA function, configured with an expanding range like this: COUNTA($B$5:B5) As the formula is copied down the column, the range starting with B5 expands to include each new row, and COUNTA returns a running count of all non-blank entries in the range. The result of COUNTA is then divided by “size”, configured as a named range F4. Using a cell on the worksheet for group size allows the grouping to be changed at any time without editing the formula....
