Posts

Excel has a great tool for visualizing data called Conditional Formatting. If you work with data in Excel (and who doesn’t these days?) you’ll find it incredibly useful. By creating simple rules that highlight just the data you are interested in, you can spot key information very quickly. To help get you started, and to give you some inspiration, here are some cool ways that you can use Excel conditional formatting to help you understand data faster....

where “color” (C5:C15) and “qty” (D5:D15) are named ranges. Example When F5 is selected, the following dropdown appears: When the user makes a selection, the correct sum is returned. The named ranges “color” (C5:C15) and “qty” (D5:D15) are for convenience only. The formula in G5 performs a conditional sum based on the current dropdown selection in F5. The outermost function is an IF statement, which checks if the selection is “all”: If so, the formula returns the sum of quantity column as a final result....

Because dates are just numbers, you can easily perform arithmetic on dates. For example, with the date June 1, 2020 in A1, you can add 10 days like this: And subtract 7 days: Date formats Because Excel dates are serial numbers, you’ll sometimes see the raw numbers on a worksheet when you expect a date. To display date values in a human-readable date format, apply a number format of your choice....

Note: all Excel formulas must begin with an equal sign (=). This is a syntax requirement, not a logical comparison. Math operators are listed here. See this list of formulas for many examples of logical operators in formulas. Note that a text value is greater than any number in Excel. 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....

In the example shown, the formula in F6 is: In this example, we assume that the reinvestment rate is the same as the cost of capital, so we set both the finance_rate and reinvest_rate to the value in F4, which is 10%. Notes The values array must contain at least one positive value and one negative value. Values should be in chronological order. MIRR assumes cash flows at regular periods....

Let’s look at another example using the same normal distribution defined by a mean of 3 and standard deviation of 2. In this case, the threshold corresponding to the probability of 0.84134 is equal to 5. In other words, the probability of an event occurring below 5 for this normal distribution is equal to 0.8413. Notes The mean describes the center or “balancing point” of the normal distribution. The standard deviation describes the shape of the bell-shaped curve....

To perform a numeric conversion, the NUMBERVALUE function uses the custom separators you provide. The decimal_separator is the character used to separate integers from fractional values in the source text. The group_separator is the character used to group text by thousands in the source text. Both separators should be enclosed in double quotes (""). When decimal_separator and group_separator, Excel uses separators for the current locale. Examples To convert the text string “10,15” to the number 10....

On the Mac, there is no equivalent shortcut. 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.

SUMX2MY2 takes two arguments, array_x and array_y. Array_x is the first range or array or range of numbers, and array_y is the second range or array of numbers. Both arguments can be provided as an array constant or as a range. Examples In the example shown above, the formula in E5 is: which returns -144 as a result. Equation The equation used to calculate the sum of squares is: This formula can be created manually in Excel with the exponentiation operator (^) like this: With the example as shown, the formula below will return the same result as SUMX2MY2:...

With these inputs, the TBILLEQ function returns a yield of 2.53%, with percentage number format applied. Entering dates In Excel, dates are serial numbers. Generally, the best way to enter valid dates is to use cell references, as shown in the example. To enter valid dates directly inside a function, the DATE function is the best option. About treasury bills A treasury bill (also called a T-Bill) is a short-term debt obligation issued by the US Treasury Department....

where total is the named range D15 and column C contains decimal values with the percentage number format applied. Note: in the example, the percentage values in column C are unrounded decimal numbers like 0.359897172, which accounts for the 0.20 difference in the calculation above. The total of all expenses is in cell D15, which is also the named range “total”. To perform this calculation in Excel, we simply multiply the percentage in column C by the total in cell D15....

where start dates are in column B, and end dates are in column C. Note: See below for a few options that use the LET function to simplify and extend the formula. DATEDIF solution The DATEDIF function is designed to calculate the difference between dates in years, months, and days. There are several variations available (e.g. time in months, time in months ignoring days and years, etc.) and these are set by the “unit” argument in the function....

where B5:B13 and E5:E7 contain valid dates, and C5:C13 contains amounts. Working from the inside out, the expression: generates strings like “0117” using the values in column B and E, which are then compared to each other. The result is an array like this: where TRUE represents dates in the same month and year. The number 1 is then divided by this array. The result is an array of either 1’s or divide by zero errors (#DIV/0!...

The result is “September” the name of the current worksheet in the workbook shown. In older versions of Excel which do not provide the TEXTAFTER function, you can use an alternate formula based on the MID and FIND function. Both approaches are explained below. Workbook path The first step in this problem is to get the workbook path, which includes the workbook and worksheet name. This can be done with the CELL function like this: With the info_type argument set to “filename”, and reference set to cell A1 in the current worksheet, the result from CELL is a full path as a text string like this: Notice the sheet name begins after the closing square bracket ("]")....

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 dates between 8/1/2015 and 11/1/2015 are highlighted. Note we are including the start and end dates by using greater than or equal to (>=) and less than or equal to (<=) Use other cells for input You don’t need to hard-code the dates into the rule....

When you apply conditional formatting, you might want to add a key to your worksheet to make the rules which trigger formats clear. In this lesson, we’ll look at how to build a key using the same conditional formatting already in the worksheet. Let’s take a look. Here we have same worksheet we looked at previously. We have three conditional format rules defined - one highlights scores over 95 in green, one highlights scores below 70 in red, and one highlights scores below 75 in yellow....

Pivot tables are excellent tools for counting and summing data, but you might struggle to get a unique or distinct count, because this feature is hidden in a normal pivot table. Let me illustrate with this set of data. Notice we have date, state, color, quantity, and a total. I’ll insert a pivot table to summarize the data. Now, to see how many states we have in the data, I can add State as a Row field....

In the example shown, the formula in C5 is: The asterisk () is a wildcard for one or more characters, so it is used to create a “begins with” test. The values in the criteria are supplied in an “array constant”, a hard-coded list of items with curly braces on either side. When COUNTIF receives the criteria in an array constant, it will return multiple values, one per item in the list....

where “mins” is the named range E5:E9, and “results” is the named range G5:G9. Although the table in this example includes both maximum and minimum values, we only need to use the minimum values. This is because when LOOKUP can’t find a match, it will match the next smallest value. LOOKUP is configured like this: The lookup values come from column B. The lookup vector is entered as the named range “mins” (E5:E9) The result vector is entered as the named range “results” (G5:G9)...