Transcript In this video we’ll take a look at how to use the COUNTIF function to solve a common problem: how to find values in one list that appear in another list. Or, how to find values in a list that don’t appear in another list. Let’s take a look. In this worksheet, on the left, I have a list of 20 names. On the right, I have a much larger list of over 1000 names....

Transcript When you copy and paste formulas, Excel will change all relative references to reflect the new location of the formula. Usually, this is exactly what you want. However, there are times when you’ll want to copy a formula exactly so that cell references don’t change. Let’s take a look at a few ways to do that. First, let’s take a look at the problem. Here’s a simple spreadsheet that captures hours worked in a week and displays a total....

Quick Links Overview Validation Formulas Dependent Dropdown Lists What is a dropdown list? Dropdown lists allow users to select a value from a predefined list. This makes it easy for users to enter only data that meets requirements. Dropdown lists are implemented as a special kind of data validation. The screen below shows a simple example. In column E, the choices are Complete, Pending, or Cancelled, and these values are pulled automatically from the range G5:G7:...

About This Shortcut Normally, when you press the Enter key, Excel moves the cursor to the next cell. To insert a line break (i.e. a new line) inside a cell, you need to use a shortcut. Here at the steps: (1) Move the cursor where you want to break the line (2) Type Alt + Enter (3) Make sure “wrap text"is enabled to see lines wrap in cell: Notes You can use this technique to make nested IF formulas easier to read ....

Transcript In this video, we’ll introduce table styles. Table styles are a big part of formatting tables in Excel. In fact, Excel ships with over 50 built-in styles. Table styles apply a lot of formatting, but not all formatting. You can use table styles to apply border, fill, font formatting, and things like zebra striping. However, you can’t use table styles to apply number formatting , alignment , or conditional formatting....

Explanation The goal is to calculate the correct invoice status (“OK”, “Paid”, or “Overdue”) using the following rules: If there is an “x” in the “Paid” column, return “Paid”. If there is not an “x” in the “Paid” column, and if the age is less than 31 days, return “OK” If there is not an “x” in the “Paid” column, and if the age is not less than 31 days, return “Overdue” This problem can be solved by nesting one IF function inside another....

Explanation This formula uses the COUNTA function to count values in a range. COUNTA counts both numbers and text to so works well with mixed data. The range B4:B8 contains 5 values, so COUNTA returns 5. The number 5 corresponds to the last row (last relative position) of data in the range B4:B100. Note: This approach will fail if the range contains blank/empty cells. This formula based on the LOOKUP function can handle empty cells in the data....

Explanation This formula relies on concatenation to assemble a valid location for the HYPERLINK function. In cell D5, the link location argument is created like this: "#"&B5&"!"&C5 // returns ""#Sheet1!A1"" which returns the string “#Sheet1!A1”. The formula then resolves to: =HYPERLINK("#Sheet1!A1","Link") Which returns a valid link. The cell value in column C is entirely arbitrary and can be any cell you like. It could also be hardcoded into the formula as a string like this:...

To display data in categories with a count and percentage breakdown, you can use a pivot table. In the example shown, the field “Last” has been added as a value field twice – once to show count, once to show percentage. The pivot table shows the count of employees in each department along with a percentage breakdown. Fields The pivot table shown is based on two fields: Department and Last ....

To build a pivot table that shows latest n values by date, you can add the date as a value field set to show maximum value, then (optionally) add a field as a row column and filter by value to show n values. In the example shown, Date is a value field set to Max, and Sales is a Row field filtered by value to show top 1 items. Pivot Table Fields In the pivot table shown, there are three fields, Name, Date, and Sales....

Purpose Return value Syntax =QUARTILE.EXC(array,quart) array - A reference containing data to analyze. quart - The quartile value to return, 1-3. Using the QUARTILE.EXC function Use the QUARTILE.EXC function to get the quartile for a given set of data. QUARTILE.EXC takes two arguments, the array containing numeric data to analyze, and quart, indicating which quartile value to return. The QUARTILE.EXC function accepts 3 values for the quart argument, as shown in the table below....

Explanation In this example, the goal is to remove the asterisk (*) at the end of each text city/country name in column B. As usual, there are many ways to solve a problem like this in Excel. In the article below, we’ll look at two good options. The first is a traditional formula based on the LEFT function and the LEN function. This formula will work in any version of Excel....

About This Shortcut Inside a cell, this shortcut will extend the selection by one word to the right each time the right arrow key is pressed. About This Shortcut Inside a cell, this shortcut will extend the selection by one word to the left each time the left arrow key is pressed.

About This Shortcut This shortcut selects one or more table columns in a table, when the cursor is in an Excel table. Behavior changes as the shortcut is used more than once. First time: column data. Second time: column data + column header. Third time: entire worksheet column. About This Shortcut This shortcut selects the data area of a table, when the cursor is in an Excel table. Behavior changes as the shortcut is used more than once:...

Purpose Return value Syntax =SORTBY(array,by_array,[sort_order],[array/order],...) array - Range or array to sort. by_array - Range or array to sort by. sort_order - [optional] Sort order. 1 = ascending (default), -1 = descending. array/order - [optional] Additional array and sort order pairs. Using the SORTBY function The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array with a formula. The result from SORTBY is a sorted copy of the data which will " spill " onto the worksheet into a range....

Explanation The SQRT function is fully automatic and will return the square root of any positive number. For example, to get the square root of 25, you can use: =SQRT(25) // returns 5 To get the square root of 16: =SQRT(16) // returns 4 To get the square root of a number in cell A1: =SQRT(A1) // square root of A1 Negative numbers If you give SQRT a negative number, it returns a #NUM!...

Explanation This worksheet demonstrates a clever way to look up prices that change based on a selected tier. Imagine a pricing system where the cost of a product depends on both the product color and a tier (e.g., “Bronze,” “Silver,” or “Gold”). The challenge is to pull the correct price based on both inputs. At the core of this solution is a standard INDEX and MATCH formula. However, since the prices are organized in blocks corresponding to each tier, we need a way to “step” through the prices correctly based on the selected tier....

Explanation In this example, the goal is to sum values in the range D5:D16 when they are less than the value entered in cell F5. This problem can be easily solved with the SUMIF function or the SUMIFS function. The main challenge in this problem is the syntax needed for cell F5 in the criteria, which involves concatenation . SUMIF function The SUMIF function is designed to sum cells based on a single condition....

Purpose Return value Syntax =SYD(cost,salvage,life,period) cost - Initial cost of asset. salvage - Asset value at the end of the depreciation. life - Periods over which asset is depreciated. period - Period to calculation depreciation for. Using the SYD function The Excel SYD function returns the “sum-of-years” depreciation for an asset in a given period. The calculated depreciation is based on initial asset cost, salvage value, and the number of periods over which the asset is depreciated....

Purpose Return value Syntax =TOCOL(array,ignore,scan_by_column) array - The array to transform. ignore - Setting to ignore blanks and errors. scan_by_column - Scan array by column. TRUE = by column, FALSE = by row (default). Using the TOCOL function The TOCOL function transforms an array or range into a single column. By default, TOCOL will scan values by row, left to right. However, TOCOL can also be configured to scan the array by column, top to bottom....