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.

The YEARFRAC function has an optional argument called “basis” that controls how days are counted when computing fractional years. The default behavior is to count days between two dates based on a 360-day year, where all 12 months are considered to have 30 days. The table below summarizes available options: Note that basis 0 (the default) and basis 4 both operate based on a 360 day year, but they handle the last day of the month differently....

with the following named ranges: actions = C5:C22. names = B5:B22, times = E5:E22 We are filtering these values first using two range/criteria pairs. The first pair provides a range of B5:B22 (names), with the name in H5 for criteria. The second pair uses the range C5:C22 (actions), with “in” as a criteria. MINIFS then returns the earliest (minimum) time where name is “Max” and action is “in”. The “last out” time is calculated in a similar way with the MAXIFS function:...

Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case. Once you save the rule, you’ll see the formatting applied to all empty cells. Empty vs. blank The ISBLANK function only returns TRUE when cell are actually empty. If a cell contains a formula that returns an empty string ("") ISBLANK won’t see these cells as blank, and won’t return true, so they won’t be highlighted....

where data (B4:G11) and input (F2) are named ranges. Note: Excel contains a conditional formatting “preset” that highlights bottom values. However, using a formula provides more flexibility. This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don’t want to use named ranges, make sure you use absolute references for both of these ranges in the formula. This formula is based on the SMALL function, which returns the nth smallest value from a range or array of values....

Let’s take a look. To add a title to a chart, first, select the chart. Then go to the Layout tab under Chart Tools on the ribbon. Controls for the chart title, and other “label-type” elements are in the Label group. Click Chart Title to see the two primary options. “Centered Overlay Title” places a title on the chart in the plot area. The “Above Chart” option places the title at the top of the chart and reduces the height of the plot area a bit to provide enough space....

Let’s take a look. Let’s look first at the Date code reference table. This table shows the date codes available for custom date formats. There are several codes each for days, months, and years, including abbreviated and non-abbreviated forms, and the option to add leading zeros to day and month numbers. Now let’s look at the table we need to format. In the left column, we have a list of dates....

Let’s take a look. Here we have a worksheet with some incorrect formulas. Let’s check these formulas against the instructions to understand what’s wrong. An easy way to check a formula is to double-click the cell. To get back out of edit mode without making changes, just press Escape. We can see that the first formula is adding the wrong cells together. It should be adding B7 to D9, but it’s adding B7 to D6....

After you’ve created a named range, you may want to modify its name, or change the cells it references. You can easily do this by using a feature called the “Name Manager.” You can find the Name Manager on the Formulas tab of the ribbon. Just click to open. You can also open the Name Manager using the keyboard shortcut Control + F3. Once the Name Manager is open, you have several options....

Here we have some test scores for a group of students. In column F, I want to set up a formula to display the top students by score. Now, I’m going to use the FILTER function, but we’ll need a way to determine the highest score, the second-highest score, and so on, and for this, I’ll use the LARGE function. LARGE returns the nth largest value in a data set....

where data1 (B5:B14) and data2 (C5:C14) are named ranges. When the result of F5-E5 is greater than 2, the IF function returns the sum of values in data1. When the result of F5-E5 is not greater than 2, IF returns the SUM of values in data2. The IF function takes three arguments like this: In this example, the logical test is the expression F5-E5>2: When this expression returns TRUE, the IF function calls the SUM function to sum values in data1: When the expression returns FALSE, IF calls the SUM function to sum values in data2: In either case, the SUM function returns a value to the IF function, and IF returns that value as the final result....

In the example shown, the formula in D5 is: This formula increments the number in column B by the value in column C, and outputs a string in the original format. Working from the inside out, this formula first extracts the numeric portion of the string in column B using the RIGHT function: The result returned is actually text like “001”, “003”, etc. but when we add the numeric value from C, Excel automatically changes the next to a number and performs the addition: Next, this numeric result goes into the TEXT function as the value, with a number format of “000”....

Testing more than one condition If you need to test for more than one condition, then take one of several actions, depending on the result of the tests, one option is to nest multiple IF statements together in one formula. You’ll often hear this referred to as “nested IFs”. The idea of nesting comes from embedding or “nesting” one IF function inside another. In the example shown, we are using nested IF functions to assign grades based on a score....

which returns the ten album titles in the XML. Note: FILTERXML is not available in Excel on the Mac, or in Excel Online. In the example shown cell B5 contains XML data that describes 10 music albums. For each album, there is information about the title, the artist, and the year. To parse this XML, the FILTERXML function is used 3 times in cells D5, E5, and F5 are as follows: In each case, the XPath expression targets a specific element in the XML....

Note if you type "" directly into a cell in Excel, you’ll see the double quote characters. However, when you enter as a formula like this: You won’t see anything, the cell will look blank. Also, if you are new to Excel, note numeric values are not entered in quotes. In other words: Wrapping a number in quotes (“1”) causes Excel to interpret the value as text, which will cause logical tests to fail....

Excel contains over 400 built-in functions, and a number of related shortcuts. To start off, there’s a shortcut for the Autosum feature, which automatically enters the SUM function. Select a range that includes a blank cell, then use Alt + = on Windows, Command + Shift + T on a Mac. You can use this for both rows and columns. You can even Autosum an entire table. When you’re entering a function, you can use autocomplete....

As you might already know, Paste special is a gateway to many powerful operations in Excel. To use Paste Special, just copy normally, then use the shortcut Ctrl + Alt + V in Windows, Ctrl + Command + V on the Mac. Using this shortcut doesn’t actually finish the Paste, it simply displays the Paste Special dialog, where you can choose which options you want. In Windows, you can type a letter to select options....

where dept (B5:B16) and group (C5:C16) are named ranges. The explanation below also includes a solution where source data is in an Excel Table. COUNTIFS function The COUNTIFS function is designed to count things based on more than one condition. Conditions are supplied to COUNTIFS in range/criteria “pairs” like this: In this problem, the first step is to create the shell of the summary table that contains one set of criteria in the left-most column, and the second set of criteria in the top row as column headers like this:...

where value (C5:C15), name (B5:B15), and stage (D5:D15) are named ranges. The result is a table that shows summary totals for each name by stage. In the example shown, the data in the range B5:E15 shows a sales pipeline where each row is an opportunity owned by a salesperson, at a specific stage. The formula in H5 is: The first part of the formula sums opportunities by salesperson: Sum range is the named range values Criteria range 1 is the named range name Criteria 1 comes from cell G5...

where values (C4:C15) and dates (B4:B15) are named ranges. MAX is nested inside XLOOKUP, and returns a value directly as the first argument: The lookup_value is delivered by MAX The lookup_array is the named range values (C4:C15) The return_array is is the named range dates (B4:B15) The match_mode is not provided and defaults to 0 (exact match) The search_mode is not provided and defaults to 1 (first to last)...