As I mentioned earlier, every worksheet has an “active cell”, which you can see displayed in the name box. When the active cell is off screen, you can scroll it into view, without actually changing the active cell, using Control + backspace on Windows, Command + Delete on a mac. If you extend this selection in any direction, you’ll see that the active cell doesn’t change. Only the selection changes....
In a workbook that contains multiple worksheets, you can go to the next worksheet using Control + PgDn on Windows, Fn + Control + down arrow on a Mac. To move to the previous worksheet, use Control + Page up on Windows, Fn + Control + up arrow on a Mac. On a Mac, you can also use Option plus right and left arrow keys to move through worksheets. To open a menu that lets you navigate through all worksheets on a workbook, right-click the worksheet navigation area, then select a sheet....
Structured references are used to refer to Excel tables in formulas. The syntax for structured references allows you to precisely target different parts of the table. Let’s walk through some examples. All these formulas refer to Table1 at the left. Since the formulas are outside the table, they all include the table name. The formulas in column I are actually entered as text. In each case, I’ll temporarily convert to a real formula so you can see exactly what range is being targeted....
Where data is an Excel Table in the range B5:F16. The result is $205, the sum of Total where the Color is “Red” OR “Blue”. Note the SUMIFS function is not case-sensitive. SUMIFS + SUMIFS Before looking at the more advanced option shown in the example, it’s important to note that we can solve this problem with two calls to SUMIFS like this: The first SUMIFS sums Total where the color is “Red”, and the second SUMIFS sums Total where the color is “Blue”....
where category is the named range B5:B122. The results in column H are decimal values with the percentage number format applied. The range is the named range category (B5:B122), and the criteria as is supplied as a reference to F5, which simply picks up the text value in column F. As the formula is copied down, it returns the count of each category listed F5:F9. To calculate the percentage shown in column H, we need to divide the count per category by the total count....
This may seem a little silly — how hard can it be to figure out the last day of a month? — but it’s actually a bit tricky, since each month has a different numbers of days, and February changes in leap years. Enter the EOMONTH function EOMONTH takes two arguments: a start date, and months. Months represents the number of months to move in the future or past. So, for example, with May 12, 2017 in cell B5:...
Note: The table name is not required. However, Excel will add the table name automatically if omitted. In the example shown, the formula in B5 is: The first ROW returns 5, since ROW is provided no argument, and resides in cell B5. The second ROW uses a structured reference: The header row resolves to the range $B$4:$F$4, so ROW returns 4. For the first 3 rows of the table, we have:...
where “first” is a reference to the first cell to include in the average and “last” is a reference to the last cell to include. The result is a range that includes the N cells to average. To get the first cell in the range, we use INDEX like this: The array is the entire Sales column, and row number worked by subtracting (n-1) from total rows. In the example, F4 contains 3, so the row number is 10-(3-1) = 8....
The FILTER function takes three arguments: array, include, and if_empty. Array is the range or array to filter. The include argument should consist of one or more logical tests. These tests should return TRUE or FALSE based on the evaluation of values from array. The last argument, if_empty, is the result to return when FILTER finds no matching values. Typically this is a message like “No records found”, but other values can be returned as well....
The MAXA function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. Examples Like the MAX function, the MAXA function returns the largest number in the supplied data: MAXA can be used with constants, cell references, or ranges: MAXA vs. MAX The primary difference between MAX and MAXA is that MAXA evaluates TRUE and FALSE values as 1 and 0, and text values as zero when these values appear in a range or in a cell reference....
NETWORKDAYS.INTL takes four arguments: start_date, end_date, weekend, and holidays. The start_date, end_date and holidays arguments must be valid Excel dates. The weekend argument controls which days of the week are considered weekends, and therefore not included in the count. Holidays are also treated as non-working days and will not be included in the result. Both the weekend and holidays arguments are optional. By default, NETWORKDAYS.INTL will exclude Saturdays and Sundays, but this can be customized as explained below....
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.
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.
Note: in Excel 2016 for Mac, you can use F1 to launch help. 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.
Both formulas return TRUE if the value in A1 is greater than 65. The TRUE function is provided for compatibility with other spreadsheet applications and there is no need to use it if you are creating a spreadsheet in Excel. To return TRUE as a result in a formula, just enter TRUE directly into a cell or formula. Note that logical expressions will automatically generate TRUE and FALSE results. For example, the formula below will TRUE or FALSE: To test a condition and return different results based on whether the results are TRUE or FALSE, see the examples on this page....
In the example shown, the formula in F5 is: E5 contains the string “calc” so, after concatenation, the MATCH function looks like this: and returns 3 inside index as “row_num”: Although the range B5:D5 is horizontal and contains just one row, INDEX correctly retrieves the 3rd item in the range: “calc 1500”. 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....
This approach can be useful when a worksheet is routinely edited in a way that would break traditional cell references. This reference to cell B5 won’t change during copy/paste/cut operations, or when columns or rows are inserted/deleted in a worksheet. The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not susceptible to changes, like a normal cell reference. It will continue to evaluate to the same location regardless of changes to the worksheet....
