Explanation Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. You can use the PMT function to get the payment when you have the other 3 components. For this example, we want to find the payment for a $5000 loan with a 4.5% interest rate, and a term of 60 months. To do this, we configure the PMT function as follows:...

Explanation In this example, the goal is to count test scores in column C that are greater than 90. The simplest way to do this is with the COUNTIF function , which takes two arguments , range and criteria : =COUNTIF(range,criteria) All test scores are in the range C5:C16 and we want to count scores greater than 90 , so we configure COUNTIF like this: =COUNTIF(C5:C16,">90") // returns 2 COUNTIF returns 2, since there are two scores in C5:C16 that are greater than 90....

Explanation Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only counts instances of different keywords. The core of this formula is the ISNUMBER + SEARCH approach to finding text in a cell, which is explained in more detail here . In this case, we are looking in each cell for all words in the named range “keywords” (E5:E9)....

About This Shortcut This shortcut displays the Create Names dialog box, which can be used to automatically name ranges based on row and column headings. You can also use the name box to define a name based on an existing selection. There is also a shortcut to open up the name manager . About This Shortcut This shortcut will move focus to the name box , located above column A:...

About This Shortcut This shortcut will display the Delete dialog box, as long as whole columns or rows are not selected. If one or more rows are selected, this shortcut will delete the selected rows. If one or more columns are selected, this shortcut will delete the selected If one or more rows are selected, this shortcut will delete the selected rows. Note: In Mac 2016, Control - also works (same as Windows)....

About This Shortcut The ‘Go To’ window allows you to go to a named range or an address that you supply. It also has ‘Special’ button that will allow you to select cells based on several criteria - blank cells, cells that contain constants, cells with formulas, etc. Special is where the real power is. Note that you can also use F5 on Windows and fn F5 on a Mac to display the Go To window....

Explanation The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an elegant and versatile function, which this example illustrates nicely. In this example, SUMPRODUCT is configured with two arrays . The first array is the range that holds product pricing: $C$5:$C$9 Note the reference is absolute to prevent changes as the formula is copied to the right. This range evaluates to the following array:...

Purpose Return value Syntax =EOMONTH(start_date,months) start_date - A date that represents the start date in a valid Excel serial number format. months - The number of months before or after start_date. Using the EOMONTH function The EOMONTH function returns the last day of the month, a given number of months in the past or future. You can use EOMONTH to calculate expiration dates, due dates, and other dates that must land on the last day of a month....

Explanation Note: in this example, we are calculating the end position in order to extract a substring with a literal start and end position. However, if you know the number of characters to extract, you can just plug in that number directly. In the example on this page, we are using the MID function to extract text based on a start and end position. The MID function accepts three arguments: a text string, a starting position, and the number of characters to extract....

Explanation In this example, the goal is to determine the position of the nth occurrence of a specific character (delimiter) in the text strings in column B. This article explains two approaches: A modern formula based on the TEXTBEFORE function. A more traditional formula for older versions of Excel. The first option is simpler, and you should use it if you have the TEXTBEFORE function in your version of Excel. The second formula is more complex and makes sense if you don’t have TEXTBEFORE....

An Excel formula is a way of defining a specific calculation in a worksheet. In Excel, all formulas must begin with an equals sign (=). For example, to add 100 to a value in A1, you can write a formula like this: =A1+100 This formula will always add 100 to the value in A1, even when that value changes. Formulas with Functions Excel contains hundreds of pre-built formulas called functions ....

Details The Problem We have a list of alphanumeric codes. Each code consists of a single letter (A, B, C, etc.) followed by a 3-digit number. These codes should appear in alphabetical order, but sometimes they are out of sequence. We want to flag out-of-sequence codes. Challenge #1 What formula in the “Check” column will place an “x” next to a code that is out of sequence? In this challenge, we are only checking that the numeric portion of the code is out of sequence, not that the letter itself is out of sequence....

Explanation This approach uses SUMPRODUCT to count the occurrence of every value in the table, one row at a time. Only when the same value appears in the same location in all three rows is a count generated. For cell B4, the formula is solved like this: =SUMPRODUCT(($B$4:$E$4=B$4)*($B$5:$E$5=B$5)*($B$6:$E$6=B$6))>1 =SUMPRODUCT(({1,1,1,1})*({1,0,1,0})*({1,0,1,0}))>1 =SUMPRODUCT({1,0,1,0})>1 =2>1 =TRUE Note that row references are fully absolute , while cell references are mixed , with only the row locked....

Explanation In this example, the goal is to highlight all values in K4:M7 ( values ) that appear in the range B4:I15 ( data ). The range K4:M7 is named “values” for readability and convenience only. If you don’t want to use a named range, use an absolute reference instead. Although this is a difficult problem for the human eye, it is exactly the kind of thing Excel does very well....

Transcript In this video, we’ll look at how to add a secondary axis to chart. Here we have some website data that includes orders and the conversion rates for five different channels. Let me first insert a column chart to plot orders per channel. Now, let’s say I’d like to also add the conversion rate into this chart. I can easily do that by selecting the chart and dragging the data area to include conversion....

Transcript In this video, we’ll look at how to apply a table style to a table. Table styles control the look and feel of an Excel Table. Tables styles allow you to format an entire table with a single click, and the style is applied continuously to a table as new rows and columns are added. You can find table styles listed on the Design tab of the Table Tools ribbon, whenever you have at least one cell in a table selected....

Transcript In this video, we’ll look at some ways you can apply font formatting to a chart. In this worksheet we have a chart with basic formatting. If you want to apply a certain font, font color, or font size globally to a chart, the fastest way is to use either the home tab on the ribbon, or the Font command in the right-click menu. In either case, start by selecting the chart area....

Transcript In this video, we’ll look at how to create a pie chart in Excel. Here we have data that shows market share for desktop browsers in 2016. Let’s build a pie chart to plot this data. Pie charts show a “part to whole” relationship, and they work best with a limited number of categories. The horizontal layout of this data is kind of awkward so let’s transpose the data to a vertical format first....

Transcript In this video, we’ll plot American generations in a chart with floating bars. In this worksheet we have a list of six generations. Each generation has a start year and end year, which represent birth years. How can we plot these generations in an easy-to-read bar chart? This seems like a simple problem, but a solution takes a little creative thinking. To help illustrate the challenge, I’ll plot this data in the most obvious way....

Transcript In this video, we’ll look at how to copy and lock structured references in a formula. Structured references behave differently from other references when copied. Let’s look at some examples. First, notice if I copy and paste a formula that contains structured references, column names won’t change. For example, if I add a new column to the end of this table, then copy and paste the tax formula into the new column, Excel brings in the same formula without changes....