Explanation Normally, SUMIFS is used with data in a vertical arrangement, but it can also be used in cases where data is arranged horizontally. The trick is to make sure the sum_range and criteria_range are the same dimensions. In the example shown, the formula in cell I5, copied down the column is: =SUMIFS(B5:G5,$B$4:$G$4,"red") Notice the criteria_range , B4:G4 is locked as an absolute reference to prevent changes as the formula is copied....

About This Shortcut This shortcut toggles the visibility of the total row in an Excel Table. It’s equivalent to using the “Total Row” checkbox on the Table tab of the ribbon. About This Shortcut The default behavior for drag and drop is to cut data in cells (similar to using Edit > Cut). Just select the cells you want to cut, hover the mouse cursor over the selection until the cursor turns into a four headed arrow, and drag the selection to a new location....

Which countries have the longest life expectancy? Bar charts work well for this kind of data because (1) the bars are easy to compare and (2) there is plenty of room for labels on the vertical axis. Here is the data used to plot this chart: How to make this chart Hold down control key and select ranges as shown above Click the insert column icon at Insert > charts Select the first 2d option The chart as inserted Select vertical axis; reverse sort order and set interval to 1 Add data Labels Select data series and set bar width Select and delete gridlines Select and delete horizontal axis Set title as desired Final chart after changes Pie charts are one of the simplest chart types in Excel, good for showing “part-to-whole” relationships with data in a small number of categories....

Explanation This example uses the UNIQUE function together with the FILTER function. The FILTER function removes data that does not meet required criteria, and the UNIQUE function further limits results to unique values only. Working from the inside out, the FILTER function is used to collect source data in group B with a quantity greater than 5: FILTER(B5:B16,(C5:C16="b")*(D5:D16>5)) // group is b, qty over 5 Inside FILTER, the expression used for the include argument is:...

Transcript What is conditional formatting? You can think of conditional formatting as automatic formatting that is triggered by conditions that you define. For example, you can use conditional formatting to automatically change the color of cells that contain values greater than or less than certain values. Conditional formatting is a great way to visually highlight important information in a worksheet. Let’s take a look. A common use case of conditional formatting is to highlight values in a set of data....

Purpose Return value Syntax =XIRR(values,dates,[guess]) values - Array or reference to cells that contain cash flows. dates - Dates that correspond to cash flows, in any order. guess - [optional] An estimate for expected IRR. Default is 0.1 (10%). Using the XIRR function The XIRR function calculates the internal rate of return for a series of cash flows that occur at irregular intervals. Payments are expressed as negative values and income as positive values....

About This Shortcut This shortcut displays the access keys needed to drive the ribbon with a keyboard only. On Windows F10 also works. No Mac equivalent. About This Shortcut This shortcut will move to the next ribbon control from the currently active control. Use SHIFT-TAB to move backwards.

Explanation In this example, the goal is to create automatic row numbers starting in cell B5 that match the data entered in column C. When new data is added to the list, the row numbers should increase as required. If items are deleted, the row numbers should respond accordingly. This has traditionally been a tricky problem in Excel because there is no built-in function to create and maintain row numbers. The article below explains several options....

Explanation This formula looks complicated, but, at the core, it is quite simple, and created in two parts like so: = (formula for number1) &":"& (formula for number2) On the left, the GCD function is used to calculated the greatest common divisor (GCD) of the two numbers. Then the first number is divided by the GCD. On the right, the same operations are performed with the second number. Next, the result of the right and left operations are joined together using concatenation, with the colon (":") as a separator....

Explanation This formula uses the MIN function as an alternative to the IF function . Although MIN is frequently used to find the minimum value in a larger set of numbers, it also works fine with just two values. Inside MIN, the first value is hardcoded as 1, the equivalent of 100% when formatted as a percentage . The second value is the result of B5 divided by C5. The MIN function simply returns the smaller of the two values:...

Explanation In this example, the goal is to test values in column B to see if they begin with a specific text string, which is “xyz” in the worksheet shown. This problem can be solved with the LEFT function, as explained below. LEFT function The LEFT function extracts a given number of characters from the left side of a text string. For example, the formula below returns the first three letters of “apple”, which is “app”:...

Purpose Return value Syntax =COTH(number) number - The hyperbolic angle. Using the COTH function The Excel COTH function returns the hyperbolic cotangent of a hyperbolic angle . Given 1.00 as input, the function returns 1.313035285 as output. =COTH(1) // returns 1.313035285 Explanation The hyperbolic cotangent is the reciprocal of the TANH function. =1/TANH(x) // equivalent to COTH(x) The plot below shows the COTH function’s output in Excel. Notes: Returns a #DIV/0!...

Explanation In this example, the goal is to count cells in the range B5:B16 that begin with specific text, which is provided in column D. For convenience, the range B5:B16 is named data . COUNTIF function The simplest way to solve this problem is with the COUNTIF function and a wildcard. COUNTIF supports three wildcards that can be used in the criteria argument : question mark (?), asterisk(), or tilde (~)....

Purpose Return value Syntax =COUPNCD(settlement,maturity,frequency,[basis]) settlement - Settlement date of the security. maturity - Maturity date of the security. frequency - Coupon payments per year (annual = 1, semi-annual = 2, quarterly = 4). basis - [optional] Day count basis (see below, default =0). Using the COUPNCD function Historically, bonds were printed on paper with detachable coupons. The coupons were presented to the bond issuer by the bondholder to collect periodic interest payments....

Explanation Data validation rules are triggered when a user adds or changes a cell value. In this example, we are using a formula that checks that the input doesn’t already exist in the named range “emails”: COUNTIF(ids,B5)<2 COUNTIF returns a count of the value in C5 inside the named range emails (C5:C9). If the count is less than 2, the expression returns TRUE and validation succeeds. If not, the expression returns FALSE and validation fails....

Purpose Return value Syntax =DATEDIF(start_date,end_date,unit) start_date - Start date in Excel date serial number format. end_date - End date in Excel date serial number format. unit - The time unit to use (years, months, or days). Using the DATEDIF function The DATEDIF function is designed to calculate the difference between two date values in years, months, or days. The result from DATEDIF is a number that corresponds to the time unit requested....

Transcript In this video we’ll look at how dynamic array behavior is native and deeply integrated in Excel. Although new dynamic array functions will get a lot of attention, it’s important to understand that dynamic array behavior is native and deeply integrated. All formulas will now run on a new calculation engine. This means that when a formula returns multiple results, these results will spill into multiple cells on the worksheet....

About This Shortcut The distance moved down depends on the size of the worksheet window. About This Shortcut Extends the current selection one screen to the right. Note: this shortcut is unusual on a Mac because the Command key is required (Option doesn’t work), even on Macs with Page Up / Page down keys.

Explanation The foundation of this formula is the MID function, which extracts a specific number of characters from text, starting at a specific location. To figure out where to start extracting text, we use this expression: SEARCH("(",B5)+1 This locates the left parentheses and adds 1 to get the position of the first character inside the parentheses. To figure out how many characters to extract, we use this expression: SEARCH(")",B5)-SEARCH("(",B5)-1 This locates the second parentheses in the text, and subtracts the position of the first parentheses (less one) to get the total number of characters that need to be extracted....

Explanation This formula takes advantage of the fact that dates are just sequential numbers in Excel. It determines the last day of the previous year and subtracts that value from the original date with this formula: =B5-DATE(YEAR(B5),1,0) The result is nth day of the year, based on the date in cell B5. Notice the day argument in the DATE function is supplied as zero. A nice feature of DATE is it can handle day values that are “out of range” and adjust the result appropriately....