Transcript An alternative to using the ribbon to apply fonts is to use the Format Cells dialog box. The Font tab in Format Cells has more options, but the ribbon has advantages for basic settings. Let’s take a look. As we looked at in a previous lesson, the Font group on the ribbon provides access to fonts, font sizes, font color, and basic font styles. The Font tab of the Format Cells dialog box contains all of these options and several more....

If you spend much time working with Excel formulas, you’ll start to run into the SUMPRODUCT function a lot. SUMPRODUCT seems to be the catch-all, do-all, go-to solution for many seemingly unrelated Excel problems. Why is SUMPRODUCT in so many Excel formulas? The main reason SUMPRODUCT appears so often in Excel formulas is that it supports array operations natively, and array operations combined with Boolean logic are a very good way to solve many problems in Excel....

Explanation By default, XLOOKUP will return the first match in a data set. However, XLOOKUP offers an optional argument called search_mode to control the order in which data is searched. Setting search mode to -1 causes XLOOKUP to search “last to first” or, in other words, search backwards through the data. You can see this option used in the example shown. The formula in G5, copied down, is: =XLOOKUP(F5,item,price,"no match",0,-1) The same formula without named ranges is:...

Explanation In this example, the goal is to calculate the average hourly rate per day based on the data shown. All data is in Excel Table named data in the range B5:F16. Note that we want to calculate a weighted average in this case. A weighted average hourly rate is the average rate at which the workers are paid, taking into account the number of hours worked at each rate. This weighted average is calculated by multiplying each rate by its corresponding weight (hours worked) for a given date, then dividing by the sum of the hours logged on that date....

Explanation In this example, the goal is to create a list of pay dates that follow a biweekly schedule. A biweekly pay schedule means employees are paid every two weeks on a given day of the week. Each pay period is 14 days, and there are usually 26 pay dates per year, though occasionally 27 depending on the calendar. In the worksheet shown above, pay dates are every other Friday, beginning on the first Friday of the year....

Explanation In this example, the goal is to count the number of cells in a range that contain negative numbers. For convenience, the range B5:B15 is named data . This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both methods are explained below. COUNTIF function The COUNT function counts the number of cells in a range that match the supplied criteria. For example, you can use COUNTIF like this:...

Explanation In this example, the goal is to count numbers that appear in column B. The COUNT function is designed to only count numeric values, but because all values in the range B5:B15 are text , COUNT will return zero. One approach is to split the characters in each text value into an array , then use the COUNT function to count numbers in the result. This approach is described below....

Explanation In this example, the goal is to sum or count a set of variances in different ways. Variances are listed in D5:D15, which is also the named range variance . The first formula in F5 simply sums all variances with the SUM function . =SUM(variance) // returns -175 Sum absolute variances The formula in F6 calculates the sum of absolute variances with the ABS function together with the SUMPRODUCT function :...

Explanation In this example, the goal is to count the number of exact matches in two ranges, ignoring the sort order or location of the values in each range. This problem can be solved with the COUNTIF function or with the MATCH function. Each approach is explained below. Note: Both formulas below use the SUMPRODUCT function to “tally up” the final count, because SUMPRODUCT handles arrays natively in Legacy Excel ....

Explanation This formula uses an expanding range for the criteria range inside COUNTIFS: COUNTIFS($B$5:B5,B5) Because the first reference is absolute and the second reference is relative, the range expands as the formula is copied down the column. The criteria is simply the value in the current row of column B. COUNTIFS returns the count of the current customer up to that point in the data. This means the first occurrence of a customer is 1, the second is 2, and so on....

Explanation In this example, the goal is to test a date to determine whether it is a workday. In Excel, you can use either the WORKDAY function or its more flexible sibling WORKDAY.INTL to accomplish this task. WORKDAY function The WORKDAY function calculates a date in the future or past that is, by definition, a workday. WORKDAY automatically excludes weekends (Saturday and Sunday) and can optionally exclude holidays. WORKDAY accepts 3 arguments: start_date , days , and (optionally) holidays ....

Purpose Return value Syntax =DECIMAL(number,radix) number - A text string representing a number. radix - The base of the number to be converted, an integer between 2-36. Using the DECIMAL function The DECIMAL function converts a number in a known base into its decimal number equivalent. For example, the DECIMAL function can convert the binary number 1101 into the decimal number 13. The number provided to DECIMAL should be a text string ....

About This Shortcut This shortcut will display the Find dialog box. About This Shortcut This shortcut will display the Find dialog with the Replace option selected. On a Mac, the Replace dialog is displayed, but the replace field is not selected as on Windows.

A dynamic named range , also called simply a “dynamic range” is a specially constructed range that expands automatically to accommodate new data. In the example shown, we have a small set of data in B5:B13, and two formulas calculating a minimum and maximum value like this: =MAX(data) =MIN(data) Where “data” is a dynamic named range corresponding to B5:B13. The formula used to create this dynamic named range is based on the OFFSET function:...

Explanation In this example, the goal is to create 4 times in the future at set intervals, based on a given start time. The intervals are 4 hours, 8 hours, 12 hours, and 24 hours. If a start time is changed, the future dates should recalculate as needed. How Excel handles times In Excel, dates are serial numbers and times are fractional parts of 1 day . This means the date and time values are just regular numbers and can be summed, added, and subtracted like other numbers....

Explanation The DATEDIF function can handle a variety of “date difference” calculations to calculate the difference between two dates in years, months, and days. DATEDIF takes 3 arguments: start date, end_date, and “unit”, which controls which result is returned. In this case, we want days ignoring years so we supply “yd” for unit. (For the full list of options, see the DATEDIF page). Once configured, the function is fully automatic and returns a result in the unit requested....

Explanation In this example, the goal is to output the time between a start date and an end date as a text string that lists years, months, and days separately. For example, given a start date of 1-Jan-2018 and an end date of 1-Jul-2018, the result should be a string like this: "1 years, 6 months, 0 days" DATEDIF solution The DATEDIF function is designed to calculate the difference between dates in years, months, and days....

Explanation This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and “flood” the text with spaces where we find one. Then we come back and grab text from the right. Working from the inside out, we use the SUBSTITUTE function to find all line breaks (char 10) in the text, and replace each one with 200 spaces: SUBSTITUTE(B5,CHAR(10),REPT(" ",200)) After the substitution, the looks like this (with hyphens marking spaces for readability):...

Explanation The DATE function creates a proper Excel date with given year, month, and day values. Then, it’s simply a matter of comparing each date in the range with the date created with DATE. The reference B4 is fully relative, so will update as the rule is applied to each cell in the range, and any dates greater than 8/1/2015 will be highlighted. Greater than or equal to, etc. Of course, you can use all of the standard operators in this formula to adjust behavior as needed....

One of the most important operations in Excel formulas is concatenation. In Excel formulas, concatenation is the process of joining one value to another to form a text string . The values being joined can be hardcoded text, cell references, or results from other formulas. There are two primary ways to concatenate in Excel: Manually with the ampersand operator (&) Automatically with a function like CONCAT or TEXTJOIN In the article below, I’ll focus first on manual concatenation with the ampersand operator (&), since this should be your go-to solution for basic concatenation problems....