About This Shortcut This shortcut inserts a new worksheet (tab) into the active workbook. The new sheet is inserted to the left of the currently selected sheet. About This Shortcut Movement is to the right through worksheets and will stop at the last worksheet to the right. To move to the last tab/worksheet in a workbook, hold down the control key and click the right navigation arrow in the lower left corner of the workbook....
About This Shortcut Move to the next pane in a worksheet that has been split. On Windows, F6 and Shift-F6 will also move between the worksheet, the status bar, and the ribbon. About This Shortcut Move to the previous pane in a worksheet that has been split.
Purpose Return value Syntax =NA() Using the NA function The NA function returns the #N/A error. #N/A means “not available” or “no value available”. You can use the NA function to display the #N/A error when information is missing. Note that if you use the NA function this way, other formulas that depend on cells that contain the #N/A error will also display #N/A, unless you specifically trap and manage the error....
Purpose Return value Syntax =NORMSINV(probability) probability - A probability corresponding to the standard normal distribution (CDF). Using the NORMSINV function The NORMSINV function returns the inverse of the standard normal cumulative distribution. Given the probability of an event occurring below a threshold value, the function returns the z-score of the threshold. For example, NORMSINV(0.8413447) returns 1 since the probability of an event occurring below 1 standard deviation from the mean is 0....
To create a pivot table that shows the last 4 weeks of data (i.e. a rolling 4 weeks), you can add a helper column to the source data to flag records in the last 4 weeks, then use the helper column to filter the data in the pivot table. In the example shown, the current date is August 25, 2019, and the pivot table shows 4 complete previous. When new data is added over time, the pivot table will continue to track the previous 4 weeks based on the current date....
Explanation It is surprisingly tricky to get INDEX to return more than one value to another function. To illustrate, the following formula can be used to return the first three items in the named range “data”, when entered as a multi-cell array formula. {=INDEX(data,{1,2,3})} The results can be seen in the range D10:F10, which correctly contains 10, 15, and 20. However, if we wrap the formula in the SUM function :...
Explanation The gist of this formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the “nth occurrence” and the TRIM function to get rid of the extra space. In this snippet, the delimiter ( delim ) is replaced with the number of spaces equal to the total length of the string: SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))) Then the formula uses the MID function to extract the nth substring....
Purpose Return value Syntax =SUBTOTAL(function_num,ref1,[ref2],...) function_num - A number that specifies which function to use in calculating subtotals within a list. See table below for full list. ref1 - A named range or reference to subtotal. ref2 - [optional] A named range or reference to subtotal. Using the SUBTOTAL function The SUBTOTAL function is designed to run a given calculation on a range of cells while ignoring cells that should not be included....
Explanation In this example, the goal is to sum the smallest n values in a set of data, where n is a variable that can be easily changed. At a high level, the solution breaks down into two steps (1) extract the n smallest values from the data set and (2) sum the extracted values. This problem can be solved with the SMALL function together with the SUMPRODUCT function, as explained below....
Explanation In this example, the goal is to look up a given income value in a tax table and return the correct tax rate for that income. The tax rate is organized into 5 tiers in the range F5:F9 with the corresponding tax rate in the range G5:G9. For convenience, the range F5:G9 is named tax_data . The explanation below shows how to retrieve the correct tax rate for each income with the VLOOKUP function....
Explanation In this example, the goal is to calculate an average for each group and region in the data as shown in the worksheet. For convenience, data is an Excel Table in the range B5:D16. This problem can be easily solved with the AVERAGEIFS function . Like the COUNTIFS function and SUMIFS function , the AVERAGEIFS function is designed to accept multiple criteria entered in [range, criteria] pairs. As long as this information is supplied correctly, the behavior of AVERAGEIFS is fully automatic....
Explanation This formula uses the “greater than or equal to” operator with text, something you might not have tried before. When Excel compares text, it decides which value is “greater” than another based on rules that follow the ASCII specification . Inside COUNTIF, the range argument is supplied as the named range “countries” (B4:B13), and the criteria is supplied as “less than or equal to” the value in C5. In each row, COUNTIFS returns the number of values that are less than or equal to the current value, which creates a sequential list of numbers (i....
Explanation In this example, the goal is to display the biggest 3 gainers and losers in a set of data where Start and End columns contain values at two points in time, and Change contains the percentage change in the values. The data in B5:E16 is defined as an Excel Table with the name data . Two formulas are required, one to return the top 3 gainers in the table, and one to return the top 3 losers....
Explanation In this example, the goal is to retrieve information about the lowest three estimates in the data shown. The problem is that there are some duplicate values in the estimate column. This means we will have some trouble trying to display the names of the 2nd and 3rd lowest suppliers because the tie values will cause INDEX to return the same name. One way to break ties like this is to add a helper column with values that have been adjusted, and then rank those values instead of the originals....
Explanation In this example, the goal is to test values in column B to see if they end with a specific text string, which is “jwb” in the worksheet shown. This problem can be solved with the RIGHT function, as explained below. RIGHT function The RIGHT function extracts a given number of characters from the right side of a text string. For example, the formula below returns the last three letters of “apple”, which is “ple”:...
Explanation In this example, the goal is to return a TRUE or FALSE result for each value in column B, based on whether it appears in the range E5:E9, which has been named “things” for convenience. Context Imagine you have a list of values in the range B5:B16 and you want to check each value to see if it appears in another list of values in the range E5:E9, which has been named “things”....
Concatenation refers to the operation of joining values together to create a text string. In Excel, you can concatenate with the concatenation operator, which is an ampersand (&) or by using one of several functions for concatenation. In the screen above, the formulas used for concatenation are: =B6&" and pears" =B7&" apples" ="The term is "&12&" months" ="Sale ends "&TEXT(B8,"mmmm d") Note that in the last example, we are using the TEXT function to format the date in B8 as a date in “mmm d” format....
Explanation In the Excel time system, one 24-hour day is equal to 1. This means times and hours are fractional values of 1, as shown in the table below: Hours Time Fraction Value 1 1:00 AM 1/24 0.04167 3 3:00 AM 3/24 0.125 6 6:00 AM 6/24 0.25 4 4:00 AM 4/24 0.167 8 8:00 AM 8/24 0.333 12 12:00 PM 12/24 0.5 18 6:00 PM 18/24 0.75 21 9:00 PM 21/24 0....
Purpose Return value Syntax =COUPDAYS(settlement,maturity,frequency,[basis]) settlement - Settlement date of the security. maturity - Maturity date of the security. frequency - Number of coupon payments per year (annual = 1, semi-annual = 2, quarterly = 4). basis - [optional] Day count basis (see below, default =0). Using the COUPDAYS function Historically, bonds were printed on paper with detachable coupons. The coupons were presented to the bond issuer in order to collect periodic interest payments....
Purpose Return value Syntax =CSCH(number) number - The hyperbolic angle. Using the CSCH function The Excel CSCH function returns the hyperbolic cosecant of an angle. Given the hyperbolic angle of 1, the function returns 0.850918128 as output. =CSCH(1) // returns 0.850918128 Explanation The hyperbolic cosecant function is the reciprocal of the hyperbolic sine function. =1/SINH(a) // equivalent to CSCH(a) Purpose Return value Syntax =DEGREES(angle) angle - Angle in radians that you want to convert to degrees....
