Explanation Note: In Excel 365 , the new SEQUENCE function is a better and easier way to create an array of numbers. The method explained below will work in previous versions. The core of this formula is a string that represents rows. For example, to create an array with 10 numbers, you can hard-code a string into INDIRECT like this: =ROW(INDIRECT("1:10")) The INDIRECT function interprets this text to mean the range 1:10 (10 rows) and the ROW function returns the row number for each row in that range inside an array....

Explanation In this example, the goal is to get the total number of days in a month based on any date in the month. This problem can be solved by combining the DAY function with the EOMONTH function. The EOMONTH function The EOMONTH function returns the last day of the month, a given number of months in the past or future. For example, with a start date of June 15, 2024, EOMONTH will return the following results with months set to -1,0, and 1:...

Purpose Return value Syntax =DCOUNT(database,[field],criteria) database - Database range including headers. field - [optional] Field name or index to count. criteria - Criteria range including headers. Using the DCOUNT function The Excel DCOUNT function counts matching records in a database using a specified field and criteria. The database argument is a range of cells that includes field headers, field is the name or index of the field to count, and criteria is a range of cells with headers matching those in database....

Purpose Return value Syntax =DSTDEV(database,field,criteria) database - Database range including headers. field - Field name or index to count. criteria - Criteria range including headers. Using the DSTDEV function The Excel DSTDEV function gets the standard deviation of sample data extracted from records matching criteria. Essentially, this function calculates standard deviation on a subset of data, like a “standard deviation IF” formula. The database argument is a range of cells that includes field headers, field is the name or index of the field to query, and criteria is a range of cells with headers that match those in database....

About This Shortcut This shortcut will extend the selection from the active cell to the home cell, which is typically A1. The result will be a selection defined by a rectangle with the upper left cell as A1 and the lower right cell as the active cell. About This Shortcut Extends the selection to the cell at the intersection of the last column (to the right) that contains data and the last row that contains data....

Explanation In this example, the goal is to filter data to show rows where dates have expired or will be expiring soon. In the table to the left, we have equipment that needs to be replaced every x months, where x appears in the “Months” column. The “Replaced” column shows the date equipment was replaced. The “Expires” column shows the date it will need to be replaced again. All data is in an Excel Table named data in the range B5:E16 and the dates to check are in the “Expires” column....

Explanation This formula uses two named ranges, “range1” (B5:B12) and “range2” (D5:D10). The core of this formula is the COUNTIF function, which returns a count of each value in both range inside the AND function: COUNTIF(range1,B5) // count in range1 COUNTIF(range2,B5) // count in range2 COUNTIF will either return zero (evaluated as FALSE) or a positive number (evaluated as TRUE) for each value in both ranges. If both counts are positive (i....

About This Shortcut This shortcut gets help for a selected control on the ribbon. No Mac equivalent. About This Shortcut This shortcut will open Excel’s options area. We don’t know of a dedicated shortcut for this in Windows, so you’ll need to use Alt + F to open the File menu, then T to access the Options window. On the Mac, options are called “preferences”, and the shortcut command + , will open preferences in most applications, not just Excel....

Transcript In this lesson we’ll cover the basic steps for creating a new conditional formatting rule. There are many different kinds of conditional formatting , but the basic steps are the same for all types. Let’s take a look. Here we have a table that shows five test scores for a group of students. Let’s create a conditional formatting rule to highlight all test scores over 90 in green. To create a new conditional formatting rule, first select the cells you’d like to format....

Transcript In this video we’ll look at how to create a dynamic named range with the INDEX function . Unlike INDIRECT and OFFSET , INDEX is a non-volatile function. This means that INDEX will not recalculate whenever a change is made to a worksheet. This makes INDEX ideal for professional models and for worksheets that contain a large amount of data. However, INDEX is a complex function that takes time to understand....

Transcript In this video, we’ll look at how to build a chart with multi-level axis labels. In some cases, you may want to create a chart that shows more than one level of axis labels. This is easy to do as long as you structure the source data in a particular way. Here we have a list of the busiest US airports. We have columns for region, city, airport code, and activity, which represents total take offs and landings....

Transcript In this video, we’ll look at options for customizing a value axis in an Excel chart. Here we have historical data showing average 30 year mortgage rates over a 5 year period. When I create a line chart, the vertical axis is a value axis showing the mortgage rate, and the horizontal axis is a category axis, grouping the data in specific date intervals. Let’s walk through some of the options for customizing the vertical value axis....

Transcript In this video we’ll look at a way to make basic in-place adjustments to numeric values. This is a handy way to quickly change things like dates, prices, and times without using formulas. Let’s take a look. Here we have some examples of information we want to change. In the first example, we want to increase prices by 10%. Obviously, we could just add a new formula that multiplies the existing price times 1....

Transcript Excel provides another method for creating borders around cells. Instead of selecting cells first and then applying a border, you can, instead, select a border tool and draw borders directly on the worksheet. Let’s take a look. Near the bottom of the border menu on the home tab of the ribbon is a group of commands for drawing borders. Excel has two basic modes for drawing borders: Draw Border and Draw Border Grid, and another mode for erasing borders called Erase Border....

Transcript In this lesson, we’ll look at the most basic way to enter data in an Excel worksheet—by typing. In future lessons, we’ll look at a number of shortcuts for entering data faster. To enter data in Excel, just select a cell and begin typing. You’ll see the text appear both in the cell and in the formula bar above. To tell Excel to accept the data you’ve typed, press enter....

Transcript In this video we’re going to look at how to make a nested IF formula more readable by adding line breaks. Here I have a worksheet that calculates sales commissions based on the commission structure shown in the table. For example, we can see that King sold $124,500 and gets a commission of 5%, worth about $6,000. On the left, I have a small test area with three formulas to calculate commission rates....

Transcript One common situation where absolute references are useful is when you need to copy and paste a formula that must always reference a value in the same cell. If we try to copy and paste our formula without first converting this reference to an absolute reference, our copied formulas will not work properly. Let’s take a look. Here we have a worksheet that tracks the number of hours worked and gross pay of a small group of people....

In Excel, the hyperbolic functions COSH , SINH , and TANH all take a number representing a hyperbolic angle as input. A hyperbolic angle is defined by the area of the sector on the right branch of the unit hyperbola x² - y² = 1, formed by the origin, the point (1,0), and a point on the unit hyperbola. For example, a hyperbolic angle of 1 corresponds to the sector formed on the unit hyperbola with an area of one-half ....

Explanation The ROW function, when entered into a cell with no arguments returns the row number of that cell. In this case, the first instance of the formula is in cell D6 so, ROW() returns 6 inside the formula in D6. We want to start with 1, however, so we need to subtract 5, which yields 1. As the formula is copied down column D, ROW() keeps returning the current row number, and we keep subtracting 5 to “normalize” the result back to a 1-based scale:...

About This Shortcut This shortcut increases the indent in a cell by one step each time it is used. On Windows, you can sometimes use Ctrl+Alt+Tab to indent and Ctrl+Alt+Shift+Tab to un-indent. However, The application switcher in Windows 7 seems to conflict with these shortcuts. On the Mac, Ctrl+M and Cmd+Shift+Tab also work. Cmd+M should work, but it conflicts with the Finder minimize window shortcut (see http://apple.stackexchange.com/questions/73886/disable-command-m-to-minimize-window) About This Shortcut This shortcut decreases the indent in a cell by one step each time it is used....