About This Shortcut If freeze pane is enabled, this shortcut will go to the upper left cell in the current pane. If freeze pane is not enabled, this shortcut will go the cell upper left cell in the worksheet (A1). About This Shortcut This shortcut enables and disables “End mode”. In End mode, arrow keys move you farther across the worksheet. In End mode, pressing an arrow key will take the cursor to the first nonblank or blank cell in the same row or column....
Explanation The formulas shown in the example all use the AVERAGE function with a relative reference set up for each specific interval. The 3-day moving average in E7 is calculated by feeding AVERAGE a range that includes the current day and the two previous days like this: =AVERAGE(C5:C7) // 3-day average The 5-day and 7-day averages are calculated in the same way. In each case, the range provided to AVERAGE is enlarged to include the required number of days:...
About This Shortcut This shortcut displays the Research window. About This Shortcut This shortcut opens the Macro Dialog Box
You can use a pivot table to display the top or bottom values in a set of data. In the example shown, one pivot table is used to show the top 3 scores in a set of data, and another pivot table is used to show the bottom 3 values in the same set of data. Because one pivot table was cloned from another, they share the same pivot cache, and both will update when either pivot is refreshed....
Explanation In this example, the goal is to sum the amounts in column D by week number, using the dates in column C to determine the week number. The week numbers in column G are manually entered. The final results should appear in column H. All data is in an Excel Table named data in the range B5:E16. This problem can be solved with the SUMIFS function and the WEEKNUM function as explained below....
Explanation In this example, the goal is to calculate total amounts in column C that occur in the last 30 days, based on a current date of December 30, 2022. There are three basic approaches to solving this problem: (1) a traditional approach based on the SUMIFS function , (2) a more flexible approach that uses Boolean logic and the SUMPRODUCT function , a modern approach based on the FILTER function ....
Explanation This formula uses the SUMPRODUCT function to sum the result of two expressions that yield arrays. The goal is to sum only time greater than 30 minutes, the “surplus” or “extra” time. The first expression subtracts 30 minutes from every time in the named range “times”: times-TIME(0,30,0) This results in an array like this: {-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667} The second expression is a logical test for all times greater than 30 minutes:...
Transcript In this video, we’ll introduce the SEQUENCE function. One of the new functions that comes with the dynamic array version of Excel is SEQUENCE. The SEQUENCE function lets you generate numeric sequences, which can be used for dates, times, and more. The SEQUENCE function takes four arguments . The first argument, rows controls how many rows SEQUENCE returns. This argument is required. The second argument, columns , controls the number of columns returned by SEQUENCE....
Transcript In this video, we’ll answer the question “what is an array?” The term “array” comes from programming, but you’ll hear it come up often in the context of more advanced Excel formulas. What does it really mean? An array is a structure or container that holds a collection of items. For example, this array contains 3 items, the numbers 10, 20, and 30: {10,20,30} And this array contains three text strings:...
Explanation In this example, the goal is to add days to a date. This is a frequent task in Excel when you need to calculate a new date by adding a specified number of days to an existing date. Here are some examples of situations where this might be useful: Calculate a due date by adding a given number of days to a start date. Determine an expiration date by adding days to the manufacturing date....
Explanation A parallelogram is a quadrilateral (4-sided) shape with two pairs of parallel sides. The opposite sides of a parallelogram are the same length, and the opposite angles have the same measure. In Euclidean geometry, the area enclosed by a parallelogram is defined by this formula: A= bh , where b stands for base and h stands for height. In Excel, the same formula can be represented like this: =b*h So, for example, to calculate the area of a parallelogram where b is 5, and h is 4, you can use a formula like this:...
Explanation In this example, the goal is to average a list of values that may contain errors. The values to average are in the named range data (B5:B15). Normally, you can use the AVERAGE function to calculate an average. However, if the data contains errors, AVERAGE will return an error. You can see this in cell E5, which contains the average function: =AVERAGE(data) // returns #N/A This happens because B9 and B13 contain the #N/A errors, and this is a common problem in Excel: errors in the data tend to percolate up to summary calculations....
A Candlestick chart is a built-in chart type in Excel normally used to show stock price activity. You’ll find this chart under the Stock category of chart types, with the name Open-High-Low-Close, sometimes abbreviated OHLC. This chart type automatically plots the full range of values as a single line, with indicates high and low in a given time interval. On top of the line is a bar which indicates open and close values....
About This Shortcut This shortcut will close the current workbook window. You can also use Ctrl + F4 in Windows, which is the original shortcut for closing a workbook. About This Shortcut Use this shortcut to open the print preview window. In later versions of Excel on Windows, Control + P has the same result. About This Shortcut This shortcut will close Excel. On a Mac, you can use Command + Q to quit almost any application....
Explanation By default, Excel is not case-sensitive. For example, with “APPLE” in A1, and “apple” in A2, the following formula will return TRUE: =A1=A2 // returns TRUE To compare text strings in a case-sensitive way, you can use the EXACT function . The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not....
In Excel 2010 or later, certain functions are meant to be replaced with new functions to provide better results and names which (theoretically) make more sense. For example, the RANK function , which generates a rank for number against a range of numbers is listed as a compatibility function, and is replaced by the RANK.AVG and RANK.EQ functions. Compatibility functions can still be used in later versions of Excel to ensure backward compatibility....
About This Shortcut This shortcut confirms any change make to a ribbon or dialog window box. For example, enter a value in an input like width or height, and press enter. You can also use the tab key to confirm a change and move to the next control. About This Shortcut This shortcut gets help for a selected control on the ribbon. No Mac equivalent.
Explanation In this example, the goal is to count the number of cells in B5:B15 that contain a given number of characters, where the number of characters n is provided as a variable in cell E4. SUMPRODUCT with LEN One way to solve this problem is to use the SUMPRODUCT function with the LEN function . In the example shown, the formula in E6 is: =SUMPRODUCT(--(LEN(B5:B15)=E4)) Working from the inside out, the LEN function is used to get the length of each value in the range like this:...
Explanation In this example, the goal is to count cells that do not contain a specific substring. This problem can be solved with the COUNTIF function or the SUMPRODUCT function . Both approaches are explained below. Although COUNTIF is not case-sensitive, the SUMPRODUCT version of the formula can be adapted to perform a case-sensitive count. For convenience, data is the named range B5:B15. COUNTIF function The COUNTIF function counts cells in a range that meet supplied criteria....
Purpose Return value Syntax =COUNT(value1,[value2],...) value1 - An item, cell reference, or range. value2 - [optional] An item, cell reference, or range. Using the COUNT function The COUNT function returns the count of numeric values in the list of supplied arguments . COUNT takes multiple arguments in the form of value1 , value2 , value3 , etc. Arguments can be individual hardcoded values, cell references, or ranges up to a total of 255 arguments....
