where “supplier” is the named range C5:C15, and “client” is the named range B5:B15. Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365. Since the lookup value is an array with 10 values, MATCH returns an array of 10 results: Each item in this array represents the first position at which a supplier name appears in the data. This array is fed into the IF function, which is used to filter results for Client A only: IF returns the filtered array to the MODE function: Notice only positions associated with Client A remain in the array....

Pareto charts highlight the biggest factors in a set of data. Following the idea of 80/20 analysis, they try to show which (approximately) 20% of the categories contribute 80% of the data being measured. They are often used in process improvement to show which factors deserve the most attention. Pros Quickly highlights most important data Excel automatically builds histogram and adds Pareto line Cons Less common chart type may be difficult to ready for many people Built-in Pareto Chart is not as easy to customize as a standard combo chart...

Fields The pivot table shown is based on three fields: Color, Size, and Qty. The Color field is configured as a Rows field, and the Size field is configured as a Columns field. The Color field is also configured as a Value field. The Color field is configured to summarize by count in the Values area: Because the colors are text values, the pivot table automatically performs a count instead of a sum....

In the example shown, the formula in D6 is as follows: First, when you have a formula like this where one function (in this case ROUND) wraps around all others, it’s often helpful to work from the outside in. So, at the core, this formula is rounding the value in B6 using the ROUND function: Where x is the number of significant digits required. The tricky part of this formula is calculate x....

After you’ve created a chart, you have several options for selecting and customizing individual chart elements. First, you can use the Chart Elements menu on the Format tab of the ribbon. After you select a chart, this menu will contain a list of the main chart elements. You can use this menu to navigate to the chart title, the plot area, the data series, and so on. Once an element is selected, you’ll see the name displayed here....

where holidays is the named range E5:E6. Because the days argument is set to 1, the formula returns the “next working day” on each new row. Holidays are provided as the named range E5:E6, which contains dates. Notice December 24 and 25 are excluded from the list since these days are holidays. All dates are formatted with the custom number format " ddd d-mmm-yyyy". Custom weekends If you need take into account custom weekends (i....

In this worksheet, we have several thousand rows of data. Let me show you some shortcuts that will help you move around this data quickly. Every Excel worksheet has an active cell, and you’ll see the address of the active cell displayed in the name box, to the left of the formula bar. If you want to scroll the active cell into focus, you can use the shortcut Control + Backspace on Windows, or Command + Delete on the Mac....

which converts the amount in column B from US Dollars (USD) to the currency indicated in column D, where the named range “xtable” refers to G5:H10. The core of this formula is the VLOOKUP function, configured like this: Here, the lookup value comes from column D, table array is the named range “xtable”, column index is 2, and match type is set to exact match. In this configuration, VLOOKUP finds the currency in the table, and retrieves the conversion rate from column H....

Note: you can also use Flash Fill in Excel 2013 and above, and the “Text to columns” feature in earlier versions of Excel. Both approaches are simpler than the formulas described below. However, for a formula-based solution, read on. 50 ft x 200 ft 153 ft x 324 ft Etc. In a spreadsheet, it’s a lot more convenient to have actual numbers so that you can use them in calculations as you wish....

where “data” is the named range C5:E9. The INDEX function has a special and non-obvious behavior: when the row number argument is supplied as zero or null, INDEX retrieves all values in the column referenced by the column number argument. Likewise, when the column number is supplied as zero or nothing, INDEX retrieves all values in the row referenced by the row number argument: In the example for formula, we supply the named range “data” for array, and we pick up the column number from H2....

where color is the named range C5:C16. As the formula is copied down, the COUNTIF function returns a count for each value in column E in the range C5:C16. COUNTIF function The COUNTIF function takes two arguments: a range of cells to count, and the criteria to use for counting. For example, to count cells equal to “red” in a range, we could use COUNTIF like this: In this example, we want a count for 3 colors, so we have manually created a small table that lists all colors in column E....

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. You can access the Font tab of the Format Cells dialog box using the arrow at the bottom of the font group or using the keyboard shortcut Control-Shift-F....

You can think of conditional formatting as automatic formatting that is triggered by conditions that you define. For example, you can use conditional formatting to automatically change the color of cells that contain values greater than or less than certain values. Conditional formatting is a great way to visually highlight important information in a worksheet. Let’s take a look. A common use case of conditional formatting is to highlight values in a set of data....

This formula uses the named ranges “amounts” (D5:D104) and “dates” (C5:C104). But we don’t want to hard-code dates, we want Excel to generate these dates for us. Normally, this is a pain, because if you add month names as text (i.e. “January”, “February”, “March”, etc.) in column F you have to go to extra trouble to create dates you can use for criteria. However, in this case, we use a simple trick to make things easier: In column F, instead of typing month names, we add actual dates for the first of each month (1/1/2016, 2/1/2016, 3/1/2016, etc....

where “data” is the named range B5:B13. Note: this is an array formula, and must be entered with control + shift + enter. The ISNUMBER function returns TRUE for numeric values, and FALSE for other values (including blanks), and the ROW function returns row numbers, so the result of this operation is an array row numbers that correspond to numeric entries: This array goes into the LARGE function with the array constant {1,2,3} for k....

which guarantees the result will never exceed 100%. Although MIN is frequently used to find the minimum value in a larger set of numbers, it also works fine with just two values. Inside MIN, the first value is hardcoded as 1, the equivalent of 100% when formatted as a percentage. The second value is the result of B5 is divided by C5. The MIN function simply returns the smaller of the two values:...

Context Let’s say you have a list of text strings in the range B5:B8, and you want to find out if these cells contain all of the words in another range, E5:E7. You could build a formula that uses nested IF statements to check for each item, but that won’t scale well if you have a lot of things to look for. Each time you add an word to look for you’ll need to add another nested IF and adjust parentheses....

where data is the named range B5:B14, and exclude is the named range D5:D7. This is an array formula and must be entered with control + shift + enter, except in Excel 365. Note: This formula is complex. See below for a more streamlined version of the formula based on the REDUCE function. Note: This formula is complicated by the “contains” requirement. If you just need a formula to count cells that do not equal many things, you can use a more straightforward formula based on the MATCH function....

In the example shown, the formula in H6 is: Which returns 1 since the value “Osborne” does not appear in B6:B11. For range, we give COUNTIF the named range list1 (B6:B11), and for criteria, we provide the named range list2 (F6:F8). Because we give COUNTIF more than one criteria, we get more than one result in a result array that looks like this: {2;1;0} We want to count only values that are missing, which by definition have a count of zero, so we convert these values to TRUE and FALSE with the “=0” statement, which yields: {FALSE;FALSE;TRUE} Then we force the TRUE FALSE values to 1s and 0s with the double-negative operator (–), which produces: {0;0;1} Finally, we use SUMPRODUCT to add up the items in the array and return a total count of missing values....

where date is the named range B5:B16. Note: In older versions of Excel, you can count unique items with the COUNTIF function, or the FREQUENCY function, as explained below. In the example shown, each row in the table represents a stock trade. On some dates, more than one trade is performed. The goal is to count trading days – the number of unique dates on which some kind of trade occurred....