To get started, let’s first set up some dates, so we have a visual representation to refer to. In C5, I’ll add a start date. Then, I’ll add and copy a formula below that simply adds “1” to each date above. The result is that we get a list of 14 consecutive dates. Now let’s show the day of the week for each date. There are several ways we can do this....
Let’s take a look. Here we have an empty pivot table using the same source data we’ve looked at in previous videos. Let’s add Product as a Row Label, and Total Sales as a Value. Now let’s sort the pivot table by Total Sales in descending order. To illustrate how Value Filters work, let’s filter to show only shows products where Total Sales are greater than $10,000. This will eliminate all of the products below “White Chocolate”....
Let’s take a look. In this worksheet, on the left, I have a list of 20 names. On the right, I have a much larger list of over 1000 names. How can I quickly figure out which names in the smaller list also appear in the bigger list? Well, there are several ways that we can approach this with formulas in Excel. We could, for example, use the VLOOKUP function or the MATCH function to find exact matches....
You apply number formatting (especially dates) to a value A cell containing a amount of text in a cell is formatted as a number A cell formatted as a date or time contains a negative value To fix, try increasing the column width first. Drag the column marker to the right until you have doubled or even tripled the width. If the cell displays properly, adjust the width back down as needed, or apply a shorter number format....
Let’s take a look. By default, Excel charts will automatically move and resize when cells underneath them change. We can see this behavior in action if we increase or decrease the width of any columns underneath the chart. The same is true of rows. If we increase or decrease row heights, the chart automatically expands and contracts. You can control this behavior by adjusting the chart’s position property. Click the Format Selection button on the Chart Tools Layout tab....
Let’s take a look. SUMIFS has three required arguments: sum_range, criteria_range1, and criteria1. After that you can enter additional range and criteria pairs to add additional conditions. In the first set of tables, we’re using the named range called “number” and a named range called “color.” I’ll enter the formulas in column H to handle the conditions in column F. To SUM all numbers equal to 15, I enter the range “number” for both the sum_range and criteria_range1 and I enter “15” for criteria1....
This is an array formula, and must be entered using Control + Shift + Enter (CSE). When given a range of cells (an array of cells) ISERROR function will return an array of TRUE/FALSE results. In the example, this resulting array looks like this: {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE} Note that the 6th value (which corresponds to the 6th cell in the range) is TRUE, since cell B9 contains #N/A. The MATCH function is configured to match TRUE in exact match mode....
In the example shown, the formula in G7 is: Where “Sex” is a named range for C3:C15 and “Score” is the named range D3:D15. Note: this is an array formula and must be entered using Control + Shift + Enter. The problem in this case is that we don’t want LARGE to operate on every value in the range, just values that are either male or female (M or F)....
Pie charts work best to display data with a small number of categories (2-5). The Pie of Pie Chart provides a way to add additional categories to a pie chart without generating a pie chart too complex to read. When configuring a Pie of Pie chart, Excel provides a setting that moves the smallest n slices of the pie to another smaller pie, where n can be adjusted to suit the data....
where key is the named range C4:G4. Note: This is an array formula. In Excel 365, enter normally. In older versions of Excel, you must enter with control + shift + enter. In cell I7, we have this formula: working from the inside-out, this expression is evaluated first: The result is an array of TRUE FALSE values like this: TRUE values indicate a correct answer, FALSE values indicate an incorrect answer....
Even basic formulas require absolute and relative references, so one of the most useful formula shortcuts is the toggle for references. For example, to complete this multiplication table, we need to multiply the left column by the top row. This means we have to lock both the column and row references to make sure they don’t change. Use F4 on Windows and Command + T on a Mac to toggle through the 4 options for absolute and relative references....
In this worksheet, we have some basic data subtotaled by region and quarter. The shortcut for grouping rows or columns in Excel is Alt Shift right arrow in Windows and Command Shift K on a Mac. If you only have cells selected (not entire rows or columns) this shortcut will cause Excel to display the Group dialog box. There, you can tell Excel to group either Rows or Columns. You can speed things up by selecting entire rows or columns before you group....
Where data is an Excel Table in the range B5:E16. In this example, the goal is to sum total hours in cell H5 and calculate total hours per person in the range H8:H10. All data is in an Excel Table named data in the range B5:E16. The table is used for convenience only, and is not required to solve the problem. The main challenge in this example is to correctly display time as a duration instead of time of day....
where id (H4) and planets (B5:B13) are named ranges. With the number 5 in cell H2, the formula in H5 returns “Jupiter”. Note: This example is a workaround to the problem of mismatched numbers and text, which causes VLOOKUP to return an #N/A error. If there is no mismatch, the workaround is not necessary and you can use a normal VLOOKUP formula. Typically, the lookup column in the table contains values that look like numbers, but are in fact numbers entered as text....
If you have a lookup value in cell A1 and lookup values in a range named table, and you want a cell to be blank if no lookup is found, you can use: If you want to return the message “Not found” when no match is found, use: IFNA option In Excel 2013, the IFNA function is available to trap and handle #N/A errors specifically. The usage syntax is the same as with IFERROR:...
Let’s take a look. Recall from an earlier lesson that copying and pasting the formula in cell D11 to other locations caused the cell references to change. To illustrate how absolute cell references work, let’s convert our formula to use absolute references and try again. To make a reference absolute, you need to add dollar signs to the address: one to lock the row, and one to lock the column....
In the example shown, the formula in E5 is: which is then copied down the table. Note that AVERAGE automatically ignores blank cells and text, but zero values are included. 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....
This formula returns TRUE if the substring is found, and FALSE if not. Note the SEARCH function is not case-sensitive. See below for a case-sensitive formula. SEARCH function (not case-sensitive) The SEARCH function is designed to look inside a text string for a specific substring. If SEARCH finds the substring, it returns a position of the substring in the text as a number. If the substring is not found, SEARCH returns a #VALUE error....
The result is 9, since there are nine values greater than 50 in the three ranges shown. Note: In Excel 365, the VSTACK and HSTACK functions offer a new approach to this problem. See below for an example. INDIRECT and COUNTIF The INDIRECT function converts a given text string into a proper Excel reference: One approach is to provide the ranges as text in an array constant to INDIRECT like this: Then pass the result from INDIRECT into the COUNTIF function like this: INDIRECT will evaluate the text values and pass the references into COUNTIF as the range argument....
Using the example above, you can get the value from the field “Total” in a record where color is “Red” and “Day” is Tue with either of the two formulas below: The DGET function is designed to extract a single value based on matching criteria. When more than one record matches criteria, DGET will throw the #NUM error. Criteria options The criteria can include a variety of expressions, including some wildcards....
