where data is the named range B5:B15. The result is 7, because there are seven cells in the range B5:B15 that contain “blue” or “green”. This formula is case-sensitive because the FIND function is case-sensitive. Note: It is also possible to use a simpler formula based on a helper column, as explained below. Note: The main formula described on this page is case-sensitive because the FIND function is case-sensitive. If you want a formula that is not case-sensitive, you can substitute the SEARCH function for the FIND function....

As the formula is copied down, it returns a count of each color in column B. This formula uses the full column reference B:B for convenience. You can also use an absolute reference like $B$5:$B$16. An Excel Table would also be a good option. See also: Running count of occurence in list, and Summary count with COUNTIFS COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria....

where data is the named range B5:B16, and each timestamp includes both a date and time. See below for the formulas used to generate the summary table in E7:F9. Basic count To get a count of individual dates that occur in B5:B16, ignoring time values, the formula in F4 is: Working from the inside out, the INT function is used to remove the time values from the timestamps like this: This works because Excel dates are just serial numbers and Excel times are fractional dates that appear as decimal values....

When a different ID is typed into cell G5, the VLOOKUP function retrieves a new name and points from the data in B5:D16. Note: the LET function is new in Excel 365. It’s part of the new dynamic array functions in Excel. If points are over 300, the message is extended: In the article below, we’ll build up the formula step-by-step, then streamline the final formula with the LET function....

“Array of arrays” is not related to the TEXTSPLIT function per se, but TEXTSPLIT is an easy way to see the limitation in action. When TEXTSPLIT is used on a single cell, it returns the text split by delimiter in a single array whose values spill onto the worksheet into multiple cells. However, when you call TEXTSPLIT on a range of cells, TEXTSPLIT returns an array of results where each result is itself an array, creating an “array of arrays”....

ISOWEEKNUM takes just one argument, date, which must be a valid Excel date. Examples In the example shown, the formula in D5, copied down, is: The formula in E5, copied down the table, is: By default the standard WEEKNUM function will start week number 1 on the first day of the year, then increment week numbers on Sundays after that. The ISOWEEKNUM function increments on Mondays, and starts week 1 on the first week that contains a Thursday....

Example In the example shown, we want to calculate the yield of a bond with a settlement date of 1-Feb-2019. The bond matures on 15-Feb-2022, and pays a coupon rate of 5%, with the first coupon paid on 15-Feb-2019. Payments are semi-annual, the day count basis is US 30/360, and the redemption value is $100. In the example shown, the formula in F5 is: With these inputs, the ODDFYIELD function returns a yield of 6....

The RANDBETWEEN function takes two arguments: bottom and top. Bottom represents the lower bound for a random number, and top represents the upper bound. RANDBETWEEN includes both top and bottom values in the range of integers that may be returned. Examples Below are basic examples of RANDBETWEEN formulas: Multiple results To generate multiple random numbers in multiple cells, select the target cells, enter the RANDBETWEEN function, and press control + enter to enter the same formula in all cells at once....

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

On Windows, Ctrl 4 also toggles underlining. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

For example, for an asset with an initial cost of $10,000, a useful life of 5 years, and a salvage value of $2,000, depreciation in year 1 can be calculated with SYD as follows: In the worksheet as shown, the formula in C7, copied down, is: Sum of years calculation In the example, the asset has a useful life of 5 years. Excel sums the years like this: This sum is then used in the calculation as follows: The asset loses 8000 in value over a 5 year period, so depreciation in period 1 = 5/15 * 8000 = $2,667, depreciation in period 2= 4/15 * 8000 = $2,133 , and so on....

Dear [boss], As you know, I spend a good part of my day in Excel. Although I can usually figure things out, I could be more productive with training. I think this might be a good option: https://exceljet.net/training The training is self-paced, with short videos I can fit into my schedule easily. The site is well organized, and they get good feedback. Would the company cover the cost as part of my skills development?...

Note: if you just need to calculate a “normal” quarter based on a quarter system that starts in January, you can use this simpler formula. In the example shown, the formula in cell D5 is: This formula returns a number, 1-4 that corresponds to a quarter system that begins in April and ends in March. In the case of fiscal quarters, we can use this same idea to map any incoming month (1-12) to one of 4 quarter values....

The result is the last value in column B. The data in B:B can contain empty cells (i.e. gaps) and does not need to be sorted. Note: This is an array formula. But because LOOKUP can handle the array operation natively, the formula does not need to be entered with Control + Shift + Enter, even in older versions of Excel. Working from the inside out, we use a logical expression to test for empty cells in column B: The range B:B is a full column reference to every cell in column B....

There are two primary ways to create a new table style: You can duplicate an existing style and customize, or 2. You can create a new style from scratch In this video, I’m going to create a simple minimal style from scratch. When creating a new style, you may want to turn off gridlines, to make it easier to see formatting. To create a new style, open the table styles menu and select New style from the bottom....

Let’s take a look. Before you clear conditional formats, you might want to verify where they are currently applied. Excel provides a convenient shortcut to select all cells with conditional formatting applied in the Find and Select menu, on the Home tab of the ribbon. Choose Find & Select, then Conditional Formatting. Excel will then select all cells that have conditional formatting applied in the current worksheet. You can then select Manage Rules from the Conditional Formatting menu and view the details of each rule....

Let’s take a look. Here’s the table of names we looked at previously. Let’s add some formatting, then look at how to find this formatting. Let’s fill several cells that contain the name Ann with yellow. Let’s also bold two of these cells. Now let’s bring up the Find and Replace dialog box and search for the name Ann, with the option “Match entire cell contents” selected. As before, “Find Next” moves through all cells that contain Ann, one at a time, including the cells we just formatted....

Let’s take a look. Here we have a simple coffee menu. Let’s improve the formatting by applying some custom fonts. To apply a font to one or more cells, first select the cells you’d like to format. In this case, let’s start by applying a new font to the entire worksheet. The easiest way to apply a font is to use the menu in the Font group on the home tab of the ribbon....