Explanation In this example, the goal is to build a simple summary count table with a formula. Once created, the summary table should automatically update to show new values and counts when data changes. The article below walks through several options, from simple to very advanced. The more advanced options show how to sort the table in descending order by count. Manual formula Note that it is possible to build a summary table with formulas manually....

You can download the worksheet I used in the training below. 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 . Core Formula is a video training course with over 100 concise videos + practice worksheets focused entirely on Excel formulas and functions....

Explanation In this example, the goal is to extract the numbers from a set of property listings which describe the number of bedrooms and bathrooms, the size of the house in sq. ft., and the size of the lot in acres. Traditionally, this kind of problem has been quite difficult in Excel because each number must be extracted with a separate, carefully configured formula ( example ). However, in the latest version of Excel, new functions like TEXTSPLIT, TOROW, and DROP make the process much easier....

About This Shortcut Use this shortcut after find criteria has been set to step “back” and find the previous match (if any). This shortcut allows you to reverse the normal direction of “find next”. About This Shortcut This shortcut will create and insert a chart using data in current range as an embedded object in the current worksheet. Note: the Mac shortcut does not seem to be working on current versions of OS X and Excel 2011....

Explanation The goal is to return the first text value in each row, ignoring both blank cells and cells that contain numbers. This problem can be solved using the HLOOKUP function. In newer versions of Excel, you can use the XLOOKUP function, which is a more modern lookup function that can look up values in vertical or horizontal ranges. Wildcards in Excel formulas Some Excel functions support wildcards , which can be used to solve this problem....

Explanation In this example, the goal is to create a formula that will return the most recent day of the week, given a date and a target day of the week, abbreviated as “dow” in the generic formula. Excel tracks the day of the week internally as a specific number for each of the seven days. By default, Excel assigns 1 to Sunday and 7 to Saturday as seen below:...

Explanation In this example, the goal is to highlight the 5 bottom values in B4:G11 where the number 5 is a variable set in cell F2. This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don’t want to use named ranges, make sure you use absolute references for both of these ranges in the formula. This formula is based on the SMALL function , which returns the nth smallest value from a range or array of values....

Transcript In this lesson we’ll take a look at the number format called “General.” The General format is Excel’s default format for all cells. In a new worksheet, all cells have this format. Let’s take a look. The General format has just two basic rules. The first rule is that if a number contains decimal places, they will be displayed up to the number that fits inside the column. When the column width is increased or decreased, Excel will adjust the number of visible decimal places automatically....

Transcript Pivot tables have a useful feature that allows you to expand or collapse groups to see or hide details. Let’s take a look. Here we have a pivot table that shows product sales by category and product, grouped by year and quarter. Whenever you have a pivot table that summarizes data into various groups, you can interactively expand and collapse those groups. Pivot tables have special expand and collapse buttons that can be enabled on the Options tab of the PivotTable Tools Ribbon....

Transcript In this video, we’ll look at how to create a new table style. There are two primary ways to create a new table style: You can duplicate an existing style and customize, or 2. You can create a new style from scratch In this video, I’m going to create a simple minimal style from scratch. When creating a new style, you may want to turn off gridlines, to make it easier to see formatting....

Explanation About the #N/A error The #N/A error appears when something can’t be found or identified. It is often a useful error, because it tells you something important is missing – a product not yet available, an employee name misspelled, a color option that doesn’t exist, etc. However, #N/A errors can also be caused by extra space characters, misspellings, or an incomplete lookup table. The functions mostly commonly affected by the #N/A error are classic lookup functions, including VLOOKUP , HLOOKUP , LOOKUP , and MATCH ....

Explanation The goal is to create a formula that returns “Done” in column E when a cell in column D is not blank (i.e., contains a value). In the worksheet shown, column D records the date a task is completed. If column D contains a date (i.e. is not empty), we can assume the task is complete. This problem can be solved with the IF function alone or with the IF function and the ISBLANK function....

Purpose Return value Syntax =INTRATE(settlement,maturity,investment,redemption,[basis]) settlement - Settlement date of the security. maturity - Maturity date of the security. investment - The amount originally invested. redemption - The amount received at maturity. basis - [optional] Day count basis (see below, default =0). Using the INTRATE function The INTRATE function calculates the annual effective interest rate for a “fully invested” security. A fully invested security does not pay periodic interest before maturity....

Explanation The TEXT function can apply number formatting to numbers just like Excel’s built-in cell formats for dates, currency, fractions, and so on. However, unlike Excel’s cell formatting, the TEXT function works inside a formula and returns a result that is text. You can use TEXT to format numbers that appear inside other text strings. In this case, we concatenate the text “Last update” with the result provided by the TEXT function, which picks up a date from column B and formats it using the supplied number format....

Excel’s logical operators are used in formulas to perform comparisons, and to build formula criteria . Logical operators can be used in formulas on their own, or combined with each other and/or other functions. The table below lists the logical operators available in Excel: Operator Meaning Example = Equal to =A1=10 <> Not equal to =A1<>10 > Greater than =A1>100 < Less than =A1<100 >= Greater than or equal to =A1>=75 <= Less than or equal to =A1<0 Note: all Excel formulas must begin with an equal sign (=)....

Explanation In this example, the goal is to get the maximum value in the data for each month listed in column E. The easiest way to do this is with the MAXIFS function, which is designed to return a maximum value based on one or more criteria. In older versions of Excel without the MAXIFS function, you can use a traditional array formula. The article below explains both approaches. For convenience only, the formulas below use the named ranges amount (C5:C16) and date (B5:B16)....

Purpose Return value Syntax =MDURATION(settlement,maturity,coupon,yld,freq,[basis]) settlement - Settlement date of the security. maturity - Maturity date of the security. coupon - The security’s annual coupon rate. yld - The security’s annual yield. freq - Number of coupon payments per year (annual = 1, semi-annual = 2, quarterly = 4). basis - [optional] Day count basis (see below, default =0). Using the MDURATION function In finance, duration is a measure of the price sensitivity to changes in interest rates for an asset that pays interest on a periodic basis, like a bond....

Purpose Return value Syntax =MIRR(values,finance_rate,reinvest_rate) values - Array or reference to cells that contain cash flows. finance_rate - Required rate of return (discount rate) as percentage. reinvest_rate - Interest rate received on cash flows reinvested as percentage. Using the MIRR function The standard Internal rate of return function (IRR) assumes all cash flows are reinvested at the same rate as the IRR. The modified internal rate of return function (MIRR) accepts both the cost of investment (discount rate) and a reinvestment rate for cash flows received....

About This Shortcut This shortcut will move to the previous control in the dialog box. About This Shortcut This shortcut will move to the next tab in the dialog box.

Explanation In this example, the goal is to create a formula that will return “Pass” or “Fail” depending on whether a student has a passing score in at least 4 out of 6 subjects. This problem can be easily solved with a formula based on the COUNTIF function together with the IF function in a formula like this: =IF(COUNTIF(C5:H5,">=70")>=4,"Pass","Fail") COUNTIF function The COUNTIF function counts cells in a range that meet a single condition, referred to as criteria ....