Posts

When to use Excel NETWORKDAYS.INTL Function Excel NETWORKDAYS.INTL function can be used when you want to get the number of working days between two given dates. It does not count the weekends and holidays, both of which can be specified by the user. It can be used when you want to calculate the benefits accrued to employees over time. It also enables you to specify the weekend (for example, you can specify Friday and Saturday as the weekend, or only Sunday as the weekend)....

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

Now don’t confuse gridlines with borders. Gridlines are visible on the entire worksheet while borders can be applied to the entire worksheet or to a selected region in the worksheet. You can change border settings such as color, width, style, etc., but in the case of gridlines, you get limited options to change the look of the gridlines. Working with Gridlines in Excel In this tutorial, you’ll learn: How to remove gridlines from the entire worksheet....

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

And one of the common tasks was to combine the data in such a way that there were no duplicate records. For example, below is a dataset that multiple records for the same region. And the final result needs to be a consolidated dataset where each country is reported only once. In this tutorial, I will show you how to combine duplicate rows and sum the values to create a single consolidated dataset....

As usually is the case, there were thousands of rows of data. There is no way she could have used Excel in-built charts as that would have taken her ages to create charts for each set of data points. This got me thinking, and fortunately, conditional formatting came to the rescue. I was able to quickly create something neat that fits the bill. Suppose you have sales data for 12 months for three products (P1, P2, and P3)....

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

Doing this manually would be a very tedious task. This is not really the kind of task I find fun, and usually prompts me to think “how can I avoid doing this?“. Since I already had my tip titles listed in an Excel sheet, this was the perfect task for a little bit of VBA. Creating the Folders from a List I set up a named range in my sheet called MakeFolderPath as an input for a folder path....

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

If you prefer written instruction instead, below is the tutorial. Hidden rows and columns can be quite irritating at times. Especially if someone else has hidden these and you forget to unhide it (or even worse, you don’t know how to unhide these). While I can’t do anything about the first issue, I can show you how to unhide columns in Excel (the same techniques can also be used to unhide rows)....

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

Note: You will need to save the completed workbook somewhere on your computer for the functions to work. How do you get information about the current workbook such as the file name, file path and the current sheet name? The CELL function can be a pretty handy tool for getting this type of information, but you’ll have to do a bit of work to get it. Without any adjustments CELL(“filename”,A1) will return the full file path and current sheet name, so some text parsing will be needed....

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