Posts

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....

Change the format for a part of your text in a cell. It’s a neat trick that will allow you to make key parts of the text really stand out to the viewer. You can also add line breaks within a cell to add white space and make it easier for anyone reading. Just press Alt + Enter when editing the cell to add a line break anywhere in your text!...

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....

Embedding a Youtube video in Excel could be helpful if you want the user to go through some instructions in the video before using the spreadsheet/model/dashboard. I sometimes use it during excel training session with live examples. In this blog post, I will show you how you can embed youtube video in Excel. Step 1 – Modify Youtube URL Step 2 – Embed Youtube Video in Excel Caution: Do not save in compatibility mode....

This prompt tells you that there is a circular reference in your worksheet and this can lead or an incorrect calculation by the formulas. It also asks you to address this circular reference issue and sort it. In this tutorial, I will cover all that you need to know about circular reference, and well as how to find and remove circular references in Excel. So let’s get started! What is Circular Reference in Excel?...

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....

Suppose we have some data for an employee list that will continually be updated and we want to make sure that any user adding to the list doesn’t add a duplicate entry. In this post we’ll learn how to use data validation to prevent a user from entering a value if it’s already been entered in the table. In our example we have a simple set of data that contains a field for employee ID, name and address and we will prevent any duplicated employee ID’s being entered....

The team that has built Excel has put in a lot of thinking behind it and they continuously work on improving the user experience. But there are still somethings that I find irritating and often need to change the default settings. And one such thing is dotted lines in the worksheet in Excel. In this tutorial, I will show you the possible reasons why these dotted lines appear and how to remove these dotted lines....

I often use scatter chart with many data points. One of the most irritating things is to spot the data point in Excel chart. Excel does not give you a way to display the names of the data points. While there are many add-ins available to do this, I will show you a super cool (without add-in) workaround to spot the data point you are looking for. Something as shown below: This technique instantly gives you a way to identify the company’s position in the scatter chart....

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...