The first argument in SWITCH is called “expression” and can be a hard-coded constant, a cell reference, or a formula that returns a specific value to match against. Matching values and corresponding results are entered in pairs. SWITCH can handle up to 126 pairs of values and results. The last argument, default, is an optional value to return when there is no match. In the example shown, the formula in D5 is: SWITCH only performs an exact match, so you can’t include logical operators like greater than (>) or less than (<) in the logic used to determine a match....

Which retrieves data where the street column contains “rd”. In brief, the SEARCH function is set up to look for the text “rd” inside the street data in B5:B14. Because this range includes 10 cells, 10 results are returned. Each result is either a number (text found) or a #VALUE error (text not found): And the resulting array returned to the FILTER function as the include argument: This array is used by the FILTER function to retrieve matching data....

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. Challenge #2 How can the formula above be extended to check if “alpha” part of the code (A,B,C, etc.) is out of sequence?...

You have a fixed monthly payment, a start date, and a given number of months. What formula can you use to sum total payments by year, based on the following worksheet: In other words, what formula works in E5, copied across to I5, to get a sum for each year shown? I came up with a formula myself, but I’d love to see your ideas, too. If you’re interested, leave a comment with the formula you propose....

Note: In Excel’s default scheme, weeks begin on Sunday. However, this example assumes the first day of a week is Monday, configured with WEEKDAY’s second argument as explained below. How can we figure out the the roll back number? It turns out that the WEEKDAY function, with a small adjustment, can give us the rollback number we need. WEEKDAY returns a number, normally 1-7 for each day of the week....

If you want to highlight cells that begin with certain text, you can use a simple formula that returns TRUE when a cell starts with the text (substring) you specify. For example, if you want to highlight any cells in the range B4:G12 that start with “mi”, you can use: Note: with conditional formatting, it’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case....

In the example shown, we are using 4 different conditional formatting rules. Each rule highlights a quartile in the range B4:F11 using the QUARTILE function. The formulas look like this: Note that we are highlighting quartiles in a specific order, starting with quartile 4 and ending with quartile 1. It’s important that the formula be entered relative to the upper left most cell in the data, in this case cell B4....

To shade odd rows, just use ISODD instead: A MOD alternative If you’re using an older version of Excel (before 2007) you may not have access to ISEVEN and ISODD. In that case, you can use the “classic” formula for shading even or odd rows, which depends on the MOD function: MOD takes a number and a divisor as arguments, and returns the remainder. The ROW function provides the number, which is divided by 2, so MOD returns zero when the row is an even number and 1 if not....

When you create a chart using valid dates on a horizontal axis, Excel automatically sets the axis type to date. For example, this stock price data is spaced out over a period of more than 10 years, in random intervals. If plot this stock price data as a line chart, the horizontal axis is automatically set up as a category axis with a type of “date”. You can see category in the name, and if I open the format task pane to axis options, you can see Excel is using the Automatic setting, which, since we have valid dates, means we have options for minimum and maximum dates, as well as date intervals....

Once you have a pivot chart, you might want to create additional pivot charts to provide different views of the same data. This worksheet has a simple pivot table and pivot chart already set up. As always, any change to the pivot table is reflected in the pivot chart, and vice versa. Let’s say we want to add a second chart using the same data. I could duplicate the existing chart with the shortcut control + d....

Let’s take a look. One way to remove data in Excel is to use the Clear button on the home ribbon. Choose “Clear Contents” to clear just the contents. Choose “Clear All” to clear both the contents and the formatting. A faster way to clear content is to use the delete key. Just select the cells you’d like to delete, then press the delete key. Notice that deleting cells this way removes the data but not the formatting....

Although a #DIV/0! error is caused by an attempt to divide by zero, it may also appear in other formulas that reference cells that display the #DIV/0! error. For example, if any cell in A1:A5 contains a #DIV/0! error, the SUM formula below will display #DIV/0!: The best way to prevent #DIV/0! errors is make sure data is complete. If you see an unexpected #DIV/0! error, check the following: Note: if you try to divide a number by a text value, you will see a #VALUE error not #DIV/0!...

Let’s take a look. If you’d like to move or copy a worksheet from the current workbook into a brand new workbook, just right-click the worksheet tab you’d like to move and choose “Move or Copy” from the menu. Then, in the Move or Copy dialog box, switch the dropdown selector to (new book). Until you’re really comfortable with moving worksheets, it’s a good idea to check the “Create a copy” checkbox....

This guide aims to help you build formulas that work the first time. Note: language mavens will point out that “criterion” is singular and “criteria” is plural, but I’m going to use “criteria” in both cases to keep things simple. What do criteria do? Among other things, criteria: Direct logical flow with IF/THEN logic Restrict processing to matching values only Create conditional sums and counts Filter data to exclude irrelevant information Trigger conditional formatting rules...

Let’s take a look. The SUMIF function sums cells that satisfy a single condition that you supply. It takes three arguments: range, criteria, and sum range. Note that sum range is optional. If you don’t supply a sum range, SUMIF will sum the cells in range instead. For example, if I want to sum the cells in this range that contain the number 15, I enter B7:B12 for the range, and 15 for the criteria....

where points (B5:B25), age (C4:I4), and data (C5:I25) are named ranges. The formula returns is 89 points, based on an age of 28 in L5, and a lift of 295 in L6. Notes: this is an advanced formula. For step-by-step introduction to INDEX and MATCH, see this article. This is also an array formula that must be entered with control + shift + enter in Legacy Excel. In Excel 365, the formula does not need special handling....

Pivot Table Fields In the pivot table shown, there are four fields in use: Date, Location, Sales, and Weekday. Date is a Row field, Location is a Column field, Sales is a Value field, and Weekday (the helper column) is a Filter field, as seen below. The filter is set to include Mondays only. Helper Formula The formula used in E5, copied down, is: This formula uses the TEXT function and a custom number format to display an abbreviated day of week....

By convention, the X axis represents arbitrary values that do not depend on another variable, referred to as the independent variable. Y values are placed on the vertical axis, and represent the dependent variable. Pros Can show the relationship of one variable to another Visual display of correlation Ideal for many types of scientific data Cons Not as well understood as many other chart types Suitable only for data where correlation is expected...

This formula assigns a grade to the score in cell E7 based on the table seen in B6:C10, but the table used for the lookup is hard-coded into the formula. Normally, the second argument for VLOOKUP is provided as a range like B6:C10: When the formula is evaluated, the reference B6:C10 is converted internally to two-dimensional array like this: Each comma indicates a column, and each semi-colon indicates a row....

Excel provides a dedicated dialog box to access special groups of cells, called “Go To Special”. To access this dialog with the keyboard, type Control + G, then click the Special button (or use Alt + S) on Windows. There you’ll find a large list of options. As with the Paste Special dialog, on Windows you can use the underlined letters to select different options. So, let’s go through these options....