Transcript When you’re working with text, you often need to pinpoint the location of some bit of text inside another. You can then use this position to extract or replace the text. In this video we’ll look at how to locate the position of one text string inside another. Let’s take a look. Excel contains two functions that can help you locate the position of text inside other text. The first function is FIND ....
Transcript In this video, we’ll look at how to use conditional formatting to highlight entire rows using multiple criteria. Here we have an example we looked at previously. With one conditional formatting rule that uses a formula, we’re able to highlight rows based on the task owner. This works well. But what if we want to highlight rows based on both priority and owner? In that case, we’ll need to extend the formula to handle 2 conditions....
Transcript In this video, we’ll look at how to create a histogram chart. A histogram chart displays the count of items grouped into bins using columns. Starting in Excel 2016, the histogram chart is a built-in option. In this worksheet, I’ve got a list of 100 names and ages. Let’s plot this data in a histogram chart. To start out, select a cell in the data. If you have a lot of data, there’s a good chance you’ll find a histogram option in Recommended charts....
Transcript In this video, we’ll look at how to create a Pareto chart. A Pareto chart plots the distribution of data in columns by frequency, sorted in descending order. A line showing cumulative percentage is plotted on a secondary axis. Starting with Excel 2016, the Pareto chart is a built-in chart type. In this worksheet, I’ve got a list of 100 reported issues classified by type. There are six types total, listed in column E....
Transcript VLOOKUP is one of the most important lookup functions in Excel. The V stands for “vertical” which means you can use VLOOKUP to look up values in a table that’s arranged vertically. Let’s take a look. Here we have a list of employees in a table. Let’s use VLOOKUP to build a simple form that retrieves the information for a given employee based on their ID number. The first thing I’ll do is create a named range for the data in the table, and a named range for the ID that we’ll be using to look things up....
Explanation The goal is to do something if a cell contains a given substring. For example, in the worksheet above, a formula returns “x” when a cell contains “abc”. If you are familiar with Excel, you will probably think first of the IF function. However, one limitation of IF is that it does not support wildcards like “?” and “*”. This means we can’t use IF by itself to test for a substring like “abc” that might appear anywhere in a cell....
Explanation In this example, the goal is to increase the prices shown in column C by the percentages shown in column D. For example, given the original price of $70.00, and an increase of 10%, the result should be $77.00. The general formula for this calculation, where “x” is the new price, is: x=old*(1+percentage) x=70*(1+10%) x=70*1.10 x=77.00 Converting this to an Excel formula with cell references, the formula in E5 becomes:...
Explanation In this example, the goal is to look up Width and Length based on inputs for Code (K6) and Size (K7). While finding the correct row based on the Code value is straightforward, the problem of how to best retrieve both columns of data (Width and Length) is more challenging. One good way to solve this problem is with an INDEX and MATCH formula, but you can also use XLOOKUP as explained below....
Explanation Excel did not originally offer the TEXTSPLIT function. This article describes how to use the LAMBDA function to create a custom function that splits text as a workaround. It’s a good example of how the LAMBDA function can be used to bridge a gap, but the workaround is no longer necessary. I leave the article below for historical reference only. - Dave The first step in creating a custom LAMBDA function is to verify the logic needed using standard formula....
Explanation When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains all column numbers for the range. If you want only the first column number, you can use the MIN function to extract just the first column number, which will be the lowest number in the array:...
A logical test (also called a “logical expression) is an expression that returns either TRUE or FALSE. For example, to test if A1 equals “apple”, you can use a formula like this: =A1="apple" // returns TRUE or FALSE To test if the value in A1 is between 5 and 10, you can use a formula like this: =AND(A1>5,A1<10) // returns TRUE or FALSE Logical tests in Excel formulas can be simple or quite complex, depending on the formula....
Purpose Return value Syntax =MODE.SNGL(number1,[number2],...) number1 - A number or cell reference that refers to numeric values. number2 - [optional] A number or cell reference that refers to numeric values. Using the MODE.SNGL function The MODE.SNGL function returns the most frequently occurring number in a set of numeric data. If supplied data does not contain any duplicate numbers, the MODE.SNGL function returns a #N/A error. The MODE.SNGL function takes multiple arguments in the form number1 , number2 , number3 , etc....
About This Shortcut This keyboard shortcut will move the current selection to the previous column left. We don’t know of any Windows equivalent; let us know if you find one. About This Shortcut This keyboard shortcut will cycle the active cell through non-adjacent selections, starting at the left and moving to the right.
Explanation In this example, the goal is to create a clickable link that will result in a ready-to-send email. The mailto link protocol The mailto link protocol allows five variables as shown in the table below: Variable Purpose mailto: The primary recipient(s) &cc= The CC recipient(s) &bcc= The BCC recipient(s) &subject= The email subject text &body= The email body text Notes: (1) separate multiple email addresses with commas. (2) Not all variables are required....
About This Shortcut This shortcut will scroll the screen to show the active cell on the worksheet. It works when there is only one cell selected or when there are multiple cells selected. Note: Older Excel versions on the Mac may use Command + Delete instead of Control + Delete. About This Shortcut This shortcut will move the active cell around the four corners of a selection in a clockwise direction....
About This Shortcut Use this shortcut to snap objects to the Excel grid, including charts, shapes, smart art, and text boxes. This works both for dragging objects to a new location on the worksheet, as well as resizing objects so that their edges align to the grid. This is a very handy way to ensure objects are exactly the same size without enabling the snap-to-grid setting in Excel. About This Shortcut This shortcut toggles the display of objects on the worksheet (e....
Explanation In geometry, the formula for calculating the surface area of a right cone is: The Greek letter π (“pi”) represents the ratio of the circumference of a circle to its diameter. In Excel, π is represented in a formula with the PI function , which returns the number 3.14159265358979, accurate to 15 digits: =PI() // returns 3.14159265358979 To square a number in Excel, you can use the exponentiation operator (^):...
Transcript In this video, we’ll introduce the idea of an Excel Table. So, what is an Excel Table? An Excel table is a rectangular range of data that has been defined and named in a particular way. To illustrate, here I have two rectangular ranges of data. Both ranges contain exactly the same data but neither one has been defined as a table. Next, I’ll convert the range on the right to a proper Table....
Transcript An absolute reference is a cell reference that intentionally won’t change when a formula is copied. There are many situations where absolute references are helpful. Probably the most common case is where you want to use a value in a specific cell in multiple calculations. This might be an hourly rate, a currency conversion ratio, or a constant of some kind. In cases like this, you need a way to tell Excel not to change a cell reference....
Some older Excel functions don’t spill when you give them a range as input. This article explains why this happens, which functions are affected, and how to fix it. Dynamic Arrays and Spilling The spilling problem explained The simple solution List of affected functions Why the plus sign? There are other reasons why a formula might not spill. This article focuses on one specific case: a specific group of older functions that will not accept a range in place of a single value....
