Explanation We want to count how often items in columns B, C, and D appear together. For example, how often A appears with C, B appears with F, G appears with D, and so on. This would seem like a perfect use of COUNTIFS, but if we try to add criteria looking for 2 items across 3 columns, it isn’t going to work. A simple workaround is to join all items together in a single cell in a helper column , then use COUNTIFS with a wildcard to count items....
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. About This Shortcut After find has been used, this shortcut will search again for the same thing. Note: with Mac Excel 2011, you must close the Find dialog before this will work.
Transcript In this video, we’ll take a look at the error codes that Excel generates when there’s something wrong with a formula. There are 8 error codes that you’re likely to run into at some point as you work with Excel’s formulas. First, we have the divide by zero error. You’ll see this when a formula tries to divide a number by zero, as in this first example. Note that you’ll see the same error when a formula tries to divide a number by a cell that is empty....
Excel Tables offer an easy way to create dynamic ranges that adjust when data changes. This makes tables perfect for pivot tables, charts, and dashboards that need to show the latest data. This course covers the key benefits of tables, including a detailed review of structured references, the special formula language for tables. Examples include VLOOKUP, INDEX and MATCH, and SUMIFS. The course also covers, table styles, slicers, filtering, sorting, and removing duplicates....
Use the text below as a starting point and customize to suit you needs. Dear [boss], As you know, I spend a good part of my day in Excel. Although I can usually figure things out, I could be more productive with training. I think this might be a good option: https://exceljet.net/training The training is self-paced, with short videos I can fit into my schedule easily. The site is well organized, and they get good feedback ....
Explanation The goal is to search through a cell for one of several specified values and return the last match found when one exists. The worksheet includes a list of colors in the range E5:E11 (which is named list ) and a series of short sentences in the range B5:B16. The task is to add a formula in column C that will search through each sentence in B5:B16 and extract the last color list (E5:E11) that appears in each sentence....
Explanation In this example, the goal is to return a number that represents quarter (i.e. 1,2,3,4) for any given date. In other words, we want to return the quarter that the date resides in. ROUNDUP formula solution In the example shown, the formula in cell C5 is: =ROUNDUP(MONTH(B5)/3,0) The ROUNDUP function works like the ROUND function , except that ROUNDUP will always round numbers 1-9 up to a given number of digits, supplied as the num_digits argument ....
Explanation In this example, the goal is to display a Happy Birthday message when a birthday in a given cell matches the current date. The core of the problem is to compare the given birthday to the current date while ignoring the year. There are two main ways to approach the problem. The first way is to use the MONTH function and the DAY function to compare the birthday in column C to the current date....
A helper column is a non-technical term to describe a column added to a set of data to help simplify a complex formula or an operation that would be otherwise difficult. The concept is a little abstract, so here are three examples: You can use VLOOKUP to perform a lookup with multiple criteria by adding a helper column to the data. In the example above, a helper column is used to concatenate first and last names, so that VLOOKUP can be used to find departments using both names....
Transcript In this video, we’ll look at how to create a bar chart in Excel. Here we data that shows answers to the survey question “How do you rate your skill with Excel?” Let’s plot this data in a bar chart. A bar chart in Excel shows horizontal bars, and it’s a good option when you want to compare data with longer labels, since there is plenty of room for text to the left of the bars....
Transcript In this video, we’ll look at some ways you can convert TRUE and FALSE values in Excel to 1s and 0s. When working with more advanced formulas, especially array formulas, you need to know how to convert TRUE and FALSE values in Excel to their numeric equivalents, 1 and 0. This is best explained with an example. In this worksheet, I have a list of fruit names. Let’s count all the names with more than 5 characters....
Transcript Sometimes in Excel you may want to reference a large number of sheets that have the same structure. In this case, you can use a special trick called a “3D reference”. Here are the test scores we looked at earlier. The Summary sheet is pulling in the results from Week1 through Week5. Suppose we want to average those test scores? In that case, since we have all the test scores on one sheet already, we can just use the AVERAGE function and refer to the cells in columns D through H to get an average....
Transcript Excel provides a simple way to horizontally indent cell content. Indents work in steps, allowing you to use more than one level of indentation. Let’s take a look. Here we have a simple budget worksheet. Let’s apply some indentation to make the categories a little more readable. The easiest way to indent is to use the Indent buttons in the Alignment group on the home tab of the ribbon. One button increases the indent by one step, and the other button decreases the indent by one step....
Purpose Return value Syntax =IMCONJUGATE(inumber) inumber - The complex number in the form “x+yi”. Using the IMCONJUGATE function The Excel IMCONJUGATE function returns the conjugate of a complex number. For example, given the complex number “3+4i” as input, the function returns “3-4i” as output. =IMCONJUGATE("3+4i") // returns "3-4i" Excel handles complex numbers as strings formatted like “x+yi” or “x+yj”. Use the COMPLEX function to get the string representing a complex number....
Explanation In this example, the goal is to display the top 3 values in C5:C16 that match a specific group, entered as a variable in cell F4. If the group is changed, the formulas should calculate new results. For convenience, group (B5:B16) and value (C5:C16) are named ranges . The core of the solution is the LARGE function, which can be used to retrieve the “nth” largest value in a set of data....
A number format in Excel is a special code that controls how a number is displayed on the worksheet. For example, the number 0.51 can be displayed as 51% with the number format: 0.0% The date January 1, 2021 can be displayed as “1-Jan-20” with the number format: d-mmm-yy The most important thing to understand about number formats is that they control the display of numbers only – number formats have no effect on underling numeric values....
Purpose Return value Syntax =PERMUTATIONA(number,number_chosen) number - The total number of items. number_chosen - The number of items in each combination. Using the PERMUTATIONA function The Excel PERMUTATIONA function returns the number of permutations (combinations where order is significant) for a given number of items. The PERMUTATIONA function allows repetitions. To calculate the number of permutations without repetitions, use the PERMUT function . A permutation is a combination where order matters....
Pivot tables are an easy way to quickly count values in a data set. In the example shown, a pivot table is used to count the names associated with each color. Fields The pivot table shown is based on two fields: Name and Color. The Color field is configured as a row field, and the name field is a value field, as seen below: The Name field is configured to summarize by count:...
Pivot tables have a feature to group dates by year, month, and quarter. In the example shown, a pivot table is used to summarize support issues by month and by priority. Each row in the pivot table lists the count of issues recorded in a given month by priority (A, B, C). The Total columns shows the total count of issues recorded in each month. Note: the source data contains data for an entire year, but the pivot table is filtered to show only the first 6 months of the year, January through June....
Explanation This example is set up in two parts for clarity: (1) a formula to determine the top 3 amounts for each month and (2) a formula to retrieve the client name for each of the top 3 monthly amounts. Note there is no actual rank in the source data. Instead, we are using the LARGE function to work directly with amounts. Another approach would be to add rank to the source data with the RANK function , and use the rank value to retrieve client names....
