Posts

For example, you may not be able to choose the state unless you have selected the country first. This construct ensures that you do not miss out on filling some of the data. Something as shown below A colleague wanted a similar construct in Excel, and the only thing that came to my mind was Data Validation. How to Restrict Data Entry in a Cell in based on Another Cell In this tutorial, I will show you how to restrict entries in a cell based on a formula....

Common functions that perform aggregate operations include, MAX, MIN, COUNT, COUNTA, AVERAGE, AND, OR, and SUM. In many cases, an aggregate value is the desired result. However, because Excel can handle arrays and array operations, there are situations where a single value is not desired. Author 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....

Early this week, I was going through our annual holiday calendar to check for the next holiday, and plan a vacation if possible. And that gave me the idea for this Excel holiday calendar template. In this tutorial, I will show you how to calculate the date of a given holiday (such as Labor Day). And you can also get an awesome Excel holiday list template (free download) at the end of this blog....

Conditions are provided to the IFS function as test/value pairs, and IFS can handle up to 127 conditions. Each test represents a logical test that returns TRUE or FALSE, and the value that follows will be returned when the test returns TRUE. In the event that more than one condition returns TRUE, the value corresponding to the first TRUE result is returned. For this reason, it is important to consider the order in which conditions appear....

The MODE function takes multiple arguments in the form number1, number2, number3, etc. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MODE ignores empty cells, text values, and the logical values TRUE and FALSE. The MODE function will accept up to 255 separate arguments. Examples MODE returns the most frequently occurring number in supplied data. For example, If there are no duplicate numbers, the MODE function returns the #N/A error: In the example shown, the formula in L5, copied down, is: Note: the MODE function is now classified as a “compatibility function”....

There are two types of permutations: The PERMUT function calculates permutations where repetitions are not allowed. To calculate permutations where repetitions are allowed, use the PERMUTATIONA function. Example To use PERMUT, specify the total number of items and “number_chosen”, which represents the number of items in each combination. For example, to calculate 3-number permutations for the numbers 0-9, there are 10 numbers and 3 chosen, so the formula is: This result can be seen in cell D8 in the example shown....

In the example shown, the formula in G6 is: 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.

Note: In Mac 2016, Control - also works (same as Windows). 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.

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.

The SUBTOTAL function can either include or exclude values in hidden rows, which makes it especially useful in Excel Tables or filtered data. By default, SUBTOTAL excludes values in rows hidden by a filter. Regardless of the calculation performed, SUBTOTAL returns an aggregate result from a set of data. Note: the SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting. Examples Below are examples of SUBTOTAL configured to SUM, COUNT, and AVERAGE the values in a range....

Syntax The generic syntax for SUMIF looks like this: The SUMIF function takes three arguments. The first argument, range, is the range of cells to apply criteria to. The second argument, criteria, is the criteria to apply, along with any logical operators. The last argument, sum_range, is the range that should be summed. Note that sum_range is optional. If sum_range is not provided, SUMIF will sum cells in the first argument, range....

She wanted to know how to apply bold font format to a specific part of a string within a cell. For example, apply the bold format to only the word ‘Hello’ from ‘Hello World’. And she wanted to do this for hundreds of cell at once. Since there is no inbuilt functionality in Excel that can do that, I created a simple macro that uses the Excel VBA InStr function (you will see how to do this in Example 4 in this tutorial)....

A lot of times, you may encounter a data set where only one cell is filled with data and the cells below it are blank till the next value. Something as shown below: While this format works for some people, the problem with this sort of data is that you cannot use it to create Pivot Tables or use it in calculations. And this has an easy fix! In this tutorial, I will show you how to quickly fill down cells in Excel until the next filled value....

While this does no harm in most cases, it’s good to have a proper understanding of what a formula is and what a function is in Excel. And, of course, it is going to give you a lot of opportunities to correct others and brag about your own Excel knowledge (if you’re into that kind of fun) In this article, I will explain the difference between formula and function in Excel (with examples)...

In the example shown, the formula in D5 is: Next, the formula calculates the number of days required to “roll back” to the last requested weekday in the month prior (i.e. the month of the original date): Inside WEEKDAY, EOMONTH is again used to get the first day of the next month. From this date, the value for day of week is subtracted, and the result is fed into WEEKDAY, which returns the number of days to roll back....

where “holidays” is the named range G5:G9. In the example shown, we have the project start date in column C, and days in column D. Days represents the duration of he project in work days. In column E, the WORKDAY function is used to calculate an end date. Holidays are provided as the named range “holidays”, G5:G9. With these inputs, WORKDAY add days to the start date, taking into account weekends and holidays, and returns January 7, 2019 as the calculated end date....