Let’s take a look. Here we have a list of properties, that includes an address, a price, and a variety of other information. Let’s calculate the maximum and minimum values in this list. First, I’m going to create a named range for the prices in the list. You don’t need to do this, but it makes the formulas easier to read and copy. To get a maximum value, use the MAX function....
After you create a chart, you don’t have to worry about updating the chart when values change. As long as worksheet calculation is set to Automatic, Excel will automatically update the chart when values in the source data change. You can verify that Calculation is set to automatic on the Formulas tab of the ribbon. Just click Calculation Options and confirm that Automatic is checked. In this chart, the source values are are currently in the range C5:C9....
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. I’ll first get the length of each name with the LEN function. If I give LEN the entire range, results spill onto the worksheet into a dynamic array....
To use the fill handle to enter data following a custom pattern, start the pattern by entering data in at least two cells. Then, select those cells, and drag the fill handle to repeat the pattern. Let’s take a look. To use the fill handle to enter a regular series of numbers, enter the first two numbers, select both cells, and then drag the fill handle. We can use the same approach to enter a list of odd numbers, to count by eights, or, to count by any repeatable sequence of numbers....
Let’s take a look. Here we have the custom pivot table style that we created and applied to this pivot table earlier. We’d like to use this same style in a different workbook. But when we check the pivot table styles menu in that workbook, the custom style is not listed, and there is no obvious way to import or copy the style. To make this pivot table style available in another workbook, just follow this simple workaround....
Let’s take a look. Here we have a simple worksheet that highlights several cell references. Following the instructions in the table, let’s build simple formulas that use addition. The first two examples don’t require any cell references and can be input directly. Always use an equal sign “=” to start off the formula: to add 1 and 5, we enter: = 1 + 5 to add 23 and 47, we enter: = 23 + 47 For the next formula, we need to add C9 and D6....
This formula combines the two ranges provided (E5:F9 and H5:I10), by appending the second range to the first range, and returns an array of values that spill into B5:C15. The third argument provides a default value to use when the formula runs into errors combining ranges, for example when ranges have different column counts. This is a custom function created with LAMBDA, based on several Excel functions, including INDEX, SEQUENCE, IFERROR, ROWS, COLUMNS, and MAX....
where data is an Excel Table in the range B5:C16. The result is the sixth row in the table, since this is the first negative value in the Low column. Because we provide data for return_array, the result includes both the date and the value. See below for details and alternatives. XLOOKUP function A simple solution is to use the XLOOKUP function with Boolean logic. This is the approach in the workbook shown, where the formula in cell E5 is: Notice that lookup_value is given as 1....
where name (B5:B16), and score (D5:D16) are named ranges. The LARGE function is a straightforward way to get the nth largest value in a range. Simply provide a range for the first argument (array), and a value for n as the second argument (k): Working from the inside out, the first step is to get the “1st” largest value in the data with the LARGE function: In this case, the value in F5 is 1, so we are asking for the 1st largest score (i....
As the formula is copied across and down the table, it returns the top 3 scores for each student in the list. For example we can use LARGE to get the top 3 scores for Hannah like this: In the example shown, the formula in I5 looks like this: This is a clever use of mixed references that takes advantage of the numbers 1,2, and 3 already in the range I5:K5, so that they can be plugged into the formula for n:...
The result is the range C4:L5 sorted by score in descending order. However, the SORT function has an optional argument called “by_col” which allows sorting values organized in columns. To sort by column, this argument must be set to TRUE, which tells the SORT function that sort_index represents a row. In this case, we want to sort the data by Score, which appears in the second row, so we use a sort_index of 2....
In this video, we’ll look at how to move charts from one state to the other. Normally, when you choose a chart type on the ribbon, Excel creates an embedded chart on the same worksheet, which floats over the cell grid below. But you can also move a chart to a sheet all by itself. To move a chart into its own sheet, first, select the chart. Then, go to the Design tab under Chart Tools, and click the Move Chart button....
Once you have classes marked, you can turn enable an autofilter and then filter on each class as needed to list enrolled students. The formula in G6 relies on the COUNTIF function to count the presence of a given class (i.e. “math”, art", etc.) in a columns C through F: Class names are pulled from row 5, and references are mixed to allow the formula to be copied across and down the table....
The single quotes (’) in the formula above are necessary because “product data.xlsx” contains a space character. Note the data itself is in the range B5:E13. VLOOKUP formula The formula used to solve the problem in C5, copied down, is: This is a standard use of the VLOOKUP function to retrieve data from the 4th column in a table: lookup_value comes from B5 table_array is a reference to a range in an external workbook col_index is 4, to retrieve data from column 4 range_lookup is zero to force an exact match...
Let’s take a look. In this worksheet we have two highlighted cell references. Let’s create a formula that adds them together. We can enter =B9 + D6 We get a result of 35, as expected. And any change to cell B9 or D6 is reflected immediately. The cell references in this formula are relative references. What this means is that Excel evaluates the reference B9 as the cell two rows up, and two rows to the left....
where “data” is an Excel Table in the range B5:D16. The result is that XLOOKUP matches the text “RED” in row 5 of the table and returns 10 from the Qty column in the same row. XLOOKUP will match “Red” in row 3 of the table and return 17, even though the lookup value is “RED” in uppercase. We need a way to get Excel to compare case. The EXACT function is perfect for this job, but the way we use it is a little different....
In the first video, I talked about how fast they are. And how you can, using pivot tables, build reports way faster than even an advanced user building the same reports with formulas. In the second video, we looked at how good pivot tables are at answering questions about data, and how you can use pivot tables a little bit like a food processor to slice and dice data any way you want to answer questions and reveal key information....
Note: Excel now includes Dynamic Array formulas, which offer important new functions. Date and Time Functions Excel provides many functions to work with dates and times. NOW and TODAY You can get the current date with the TODAY function and the current date and time with the NOW Function. Technically, the NOW function returns the current date and time, but you can format as time only, as seen below:...
Note: “rate” is the named range F6. and the ending balance with: So, for each period in the example, we use this formula copied down the table: With the FV function The FV function can also be used to calculate future value. The equivalent formula is: The interest rate is used as-is, since we are compounding annually, nper is 1, since there is only one period per year, pmt is zero, since there are no additional payments, and pv is the starting balance, input as a negative value by convention....
