As the formula is copied down, the numbers in column B are padded with a progressively larger number of zeros based on the number in column C. Note that the result is a text value. Fixed number The TEXT function returns a number formatted as text, using the number format provided. The TEXT function can apply number formats of any kind to numbers. It is most often used when you want to maintain number formatting for a number when concatenating that number with other text....

In the example shown, the formula in E4 is: replaces only the 2nd instance (2 comes from D4) of “x” in text in B4, with “~” character. The result looks like this: 100x15~50 Next, FIND locates the “~” inside this string and returns the position, which is 7 in this case. Note: we use “~” in this case only because it rarely occurs in other text. You can use any character that you know won’t appear in the text....

In the example shown, the formula in I5 is: Which returns the number 4, representing the position in this list of the the most expensive property. In this case, that value is 849900. This number is then supplied to the MATCH function as the lookup value. The lookup_array is the same range C3:C11, and the match_type is set to “exact” with 0. With those arguments, MATCH locates finds the maximum value inside the range and returns the relative position of the value in that range....

By wrapping substrings in the asterisks, Excel evaluates the formula like this: COUNTIF counts the values where ever they appear in the cell. Since we are giving COUNTIF multiple values to look for, we receive a count for each value in an array like this: {1;0;1;1} . Finally, SUMPRODUCT returns the sum of all items in the array. Any result greater than zero returns TRUE. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

Excel has many dialog boxes that you will use frequently. These includes things like Format Cells, dialog boxes for find and replace, spelling, paste special, and many more. All of these dialogs support common features that let you drive them from the keyboard. To illustrate, we’ll look at the Format Cells dialog, which provides many options in a tabbed interface. First, notice this dialog has an active Tab, and this corresponds to the tab that was active the last time you used it....

The result are the comma separated values in B5 sorted in alphabetical order. Working from the inside out, the first step is to extract the values into an array that can be sorted with the SORT function. This is done with the FILTERXML and the SUBSTITUTE functions in this snippet: This formula is explained in detail here. In brief, we use SUBSTITUTE to convert the text values into a very simple XML format, then use FILTERXML to extract the values into an array....

When this formula is entered, the result is $192. This is the sum of numbers in the range F5:F16 when cells in C5:C15 contain “Red”. Note that the SUMIFS function is not case-sensitive. SUMIFS solution In the example shown, the solution is based on the SUMIFS function. The formula in cell I5 is: In this formula, sum_range is F5:F16, criteria_range1 is C5:C16, and criteria1 is “red”. The result ($192) is the sum of numbers in the range F5:F16 when cells in C5:C15 contain “Red”....

The result is $13,500, the sum of Amounts in the range C5:C16 when the date in B5:B16 is between 15-Sep-22 and 15-Oct-22, inclusive. Note: for SUMIFS to work correctly, the worksheet must use valid Excel dates. All dates in Excel have a numeric value underneath, and this is what allows SUMIFS to apply the logical criteria described below. SUMIFS function The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria....

In the example shown, the formula in H5 is: Where codes is the named range J4:J5 and values is the named range K4:K5. Context Sometimes you may want to sum multiple values retrieved by a lookup operation. In this example, we want to sum holiday time taken each week based on a code system, where F = a full day, and H = a half day. If a day is blank, no time was taken....

Working from the inside out, SUBSTITUTE is configured to replace the nth occurence of a space character, where n comes from column C, the text comes from column B, and “#” is hardcoded. The resulting string is returned to the FIND function, configured to look for “#”. Since the “#” is the 12th character in the text, FIND returns 12. We don’t want to include the space character itself in, so we subtract 1: LEFT returns the final result from the formula, “The cat sat”....

where quantity (C4:F4) and discount (C5:F5) are named ranges. In the example shown, the data in C4:F5 contains quantity-based discounts. As the quantity increases, the discount also increases. The table to the right shows the discount returned by XLOOKUP for several random quantities. The formula in F5, copied down, is: The same formula without named ranges is: XLOOKUP’s arguments are configiured like this: The lookup_value comes from cell E5 The lookup_array is the named range quantity (C4:F4) The return_array is the named range discount (C5:F5) The not_found argument is not provided The match_mode is set to -1 (exact match or next smaller) The search_mode is not provided and defaults to 1 (first to last)...

where names (B5:B15) is a named range. Setting search mode to -1 species a “last to first” search. In this mode, XMATCH will match the lookup value against the lookup array, starting with the last value, and moving toward the first: Retrieve date and amount XMATCH returns a position. Typically, XMATCH is used with the INDEX function to return a value at that position. In the example show, we can use INDEX and XMATCH together to retrieve the date and sales for each name as follows: where dates (C5:C15) and sales (D5:D15) are named ranges....

Like all stacked bar charts, the first data series (next to the axis) is easy to compare visually, but subsequent data series are harder to compare, since they aren’t aligned to a common element. Pros Able to show part-to-whole changes over time Multiple categories and data series in compact space Cons Difficult to compare all but first series Stacked bars normalized to 100% so absolute value dimension is lost Become visually complex as categories or series are added...

The result in column D is text – the number is embedded in a text string with the correct number of zeros. Read below for details and the steps needed to apply Option 2. There are two basic ways to solve this problem: (1) convert the number to text with leading zeros (2) apply a custom number format to display the number with leading zeros. The best approach depends on your needs....

rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: nper - the total number of payment periods for the loan, 60, from cell C8. pv - The present value, or total value of all payments now, 5000, from cell C5. start_period - the first period of interest, 1 in this case, since we are calculating principal across the entire loan term....

In the example shown, the formula is solved like this: Days remaining from today If you need to calculate days remaining from today, use the TODAY function like so: The TODAY function will always return the current date. Note that after the end_date has passed, you’ll start to see negative results, because the value returned by TODAY will be greater than the end date. You can use this version of the formula to count down days to an important event or milestone, count down days until a membership expires, etc....

Where “data” in an Excel Table in the range B5:D15. The result is the quantity associated with “Red” (5), excluding “RED”. Note that if there is more than one match (i.e. exact match duplicate colors), SUMPRODUCT will sum matching results. Note: while this is an array formula, it does not need to be entered with Control + Shift + Enter, since SUMPRODUCT handles arrays natively. The SUMPRODUCT function multiplies arrays together and returns the sum of products....

Column charts work best where data points are limited (i.e. 12 months, 4 quarters, etc.). With more data points, you can switch to a line graph. Pros Easy to read Simple and versatile Easy to add data labels at ends of bars Cons Become cluttered with too many categories Clustered column charts can be difficult to interpret Tips Add data labels where when it makes sense Avoid all 3d variants...

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: Translated, this means: the value in the current row of the Group column equals “A”....

Which copies values from every 3rd row in column B as the formula is copied down. In Excel, it’s difficult to create formulas that skip rows following a certain pattern, because the references in the formula will automatically change as the formula is copied in 1-step increment across cells. However, with a little work it’s possible to construct formula references that follow specific patterns. In this example, we are using the OFFSET function, which is designed to create references to other cells, or cell ranges, based on a given starting point, or origin....