Note: In Mac 2016, Control - also works (same as Windows). 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, this shortcut doesn’t appear to work if slicers are on the worksheet. 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.
In the example shown, the formula in D5 is: The SORT function returns 10 sorted results. Even though the formula is entered just once in cell D5, all 10 values “spill” into the range D5:D14 automatically. The range of results returned by a formula that spills is called a spill range. When something on the worksheet blocks a spilled array formulas, it will return a #SPILL! error. Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
SUMXMY2 takes two arguments, array_x and array_y. Array_x is the first range or array or range of numbers, and array_y is the second range or array of numbers. Both arguments can be provided as an array constant or as a range. Examples In the example shown above, the formula in E5 is: which returns 28 as a result. Equation The equation used to calculate the sum of squares is: This formula can be created manually in Excel with the exponentiation operator (^) like this: With the example as shown, the formula below will return the same result as SUMX2MY2:...
where data is an Excel Table in the range B5:D16. The result is a list of the values in Group A that are within +/- 0.05 of 1.2. Background reading This article assumes you are familiar with Excel Tables and the FILTER function. If not, see: Excel Tables - introduction and overview Value is within tolerance - formula example The FILTER function - introduction and overview Basic FILTER function example (video) Dynamic Array Formulas (paid training)...
Here’s a little puzzle for you…how can you use Excel to generate a list of dates that are weekends only? For example, a list of Saturday Sunday pairs like this: A couple years ago, I found and described a formula that will do it using the WEEKDAY function and some tricky date logic handled with IF: With a date in A1, you can enter the formula in A2 and drag down to get your list of weekend dates....
In this example, a $5000 payment is made each year for 25 years, with an interest rate of 7%. To calculate future value, the FV function is configured as follows like this in cell C7: with the following inputs: rate - the value from cell C5, 7%. nper - the value from cell C6, 25. pmt - negative value from cell C4, -100000 pv - 0. type - 0, payment at end of period (regular annuity)....
where data is the named range C5:I13. The result is an array of with 7 sums, one for each column in the range, as seen in row 15. Note: in older versions of Excel you can use the MMULT function, as explained below. With the BYCOL function In Excel 365, the most straightforward way to generate subtotals for each column is with the BYCOL function. The purpose of BYCOL is to process data in a “by column” fashion....
To highlight duplicate columns, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula used to highlight duplicate columns is: Note that row references are fully absolute, while cell references are mixed, with only the row locked. With a helper row If you don’t mind adding a helper row to your data, you can simplify the conditional formatting formula quite a bit. In a helper row, concatenate all values in the column....
Once you have a chart, you may want to add or remove data series. If all data series are in contiguous cells, it’s easy to just select the chart, and drag the data range selectors to include or exclude cells as needed. However, if the data series are not in contiguous cells, you’ll need to use another approach. For example, here if we want to plot all 3 test results, but ignore the two quiz results, we can’t use the data range selector....
Let’s take a look. To apply a fill, start by selecting the cells you want to fill. Then, open the Format Cells dialog box and navigate to the Fill tab. There are two basic types of fills. The first is a solid background color. Just select the color you’d like to use from the color grid. You’ll see the color appear below in the sample area. When you’re happy with the color, just click OK to apply....
Let’s take a look. Here we have a table that shows hours worked and gross pay for a small team. Everyone is paid the same hourly wage, so the formula used to calculate pay uses an absolute reference to cell C3. Now lets say you want to convert these formulas to use a named range. First, I’ll name cell C3 “hourly_rate” using the name box. Just type the name and press Return....
The examples below show misconfigured formulas that return the #NAME error and the steps needed to fix the error and get a working formula again. Function name misspelled In the example below, the VLOOKUP function is used to retrieve an item price in F3. The function name “VLOOKUP” is spelled incorrectly, and the formula returns #NAME? When the formula is fixed, the formula works properly: Range entered incorrectly In the example below, the MAX and MIN functions are used to find minimum and maximum temperatures....
Let’s take a look. The easiest way to update a pivot table manually is to right-click anywhere in the table and choose “Refresh” from the menu. This causes Excel to reload the source data and display the latest information in the pivot table. If the source data has not changed since the last refresh, the pivot table will not change. When you Refresh a pivot table, it’s important to understand that the only thing changing is data running through the pivot table....
INDEX function | MATCH function | INDEX and MATCH | 2-way lookup | Left lookup | Case-sensitive | Closest match | Multiple criteria | More examples The INDEX Function The INDEX function in Excel is fantastically flexible and powerful, and you’ll find it in a huge number of Excel formulas, especially advanced formulas. But what does INDEX actually do? In a nutshell, INDEX retrieves the value at a given location in a range....
Let’s take a look. Here we have one table that lists sales by salesperson, and another that shows the commission that should be earned based on the sales amount. Let’s add a VLOOKUP formula in column D to calculate the appropriate commission rate based on the sales figure shown in column C. As usual, I’ll start off by naming the range for the lookup table. I’ll call it “commission_table”. This will make our VLOOKUP formula a lot easier to read and copy....
This formula combines the two ranges provided (B5:C12 and E5:F10), by appending the second range to the right of the first range. The result is returned as an array of values that spill into H5:K12. The third argument provides a default value to use when the formula runs into errors combining ranges, for example when ranges have different column or row counts. In the example below, the formula in cell C5 is: This is a custom function created with LAMBDA, based on several Excel functions, including INDEX, SEQUENCE, IFERROR, ROWS, COLUMNS, and MAX....
