where “names” is the named range B5:B11, “rand” is the named range C5:C11, and “sort” is the named range D5:D11. The RAND function generates a random value at each row. Note: RAND is a volatile function and will generate new values with each worksheet change. The second helper column holds the numbers used to sort data, generated with a formula. The formula in D5 is: See this page for an explanation of this formula....

Because dates in Excel are just serial numbers (the first date in the standard Excel date system is January 1, 1900), you can adjust dates just adding or subtracting values. To solve this formula, Excel just adds 1 to the date in B6. The first formula therefore returns a new date of 1/31/2000, one day later than the starting date. Once the first formula is entered, it is copied down as far as needed....

In this first example, we have a big set of data that contains a lot of blank rows. If I move to the last cell, you can see that there’s over 36,000 rows of data, and, looking at the count, just over 33,000 rows actually contain data, which means over 3000 rows are blank. To quickly remove these blank rows, I can use Go To Special. First, pick a column that should always contain data....

Excel has a large number dialog boxes that you will use frequently. Some examples include Format Cells, find and replace, spelling, paste special, go to special, and many more. On a Mac, you have a more limited ability to select controls in dialog boxes, but it is possible to drive these dialogs entirely from the keyboard. First, to allow you to access all dialog controls, you need to enable a preference....

To hide columns, use Control + 0. You can also work with multiple columns at the same time. To unhide columns again, make a selection that spans the hidden columns, and use Control Shift 0. To hide rows, use Control + 9. Again, you’ll see a visual indication in row numbers to indicate that rows are hidden. To unhide rows again, make a selection that spans hidden rows, and use Control Shift 9....

The Format Task Pane was added in Excel 2013 and provides detailed controls for most chart elements. Previously, this sort of formatting was done with the Format dialog box. The main idea behind the Format Task pane is to provide a more complete set of controls to format things like charts without obscuring the worksheet below. The easiest way to display the Format Task Pane is to double-click on a chart....

where “xtable” is the named range E5:F10. Note: this is an array formula and must be entered with control + shift + enter. To parse the input string into an array or letters, we use MID, ROW, LEN and INDIRECT functions like this: LEN returns the length of the input text, which is concatenated to “1:” and handed off to INDIRECT as text. INDIRECT evaluates the text as a row reference, and the ROW function returns an array of numbers to MID: MID then extracts one character for at each starting position and we have: Essentially, we are asking VLOOKUP to find a match for “a”, “b”, and “c” at the same time....

which returns the 5 unique values in group A, as seen in E5:E9. Notice we are picking up the value “A” directly from the header in cell E4. Insider filter the expression C5:C16=E4 returns an array of TRUE FALSE values like this: This array is used to filter data, and the FILTER function returns another array as a result: This array is returned directly to the UNIQUE function as the array argument....

Where “In” is the Excel Table on the left, “Out” is the table in the middle. The key to this approach is to use Excel Tables, because Table ranges automatically expand to handle changes in data. This means we can get a total of all incoming red items with: And a total of all outgoing red items with: In both cases, the SUMIFS function generates a total for all red items in each table....

rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: nper - the total number of payment periods for the loan, 60, from cell C8. pv - The present value, or total value of all payments now, 5000, from cell C5. start_period - the first period of interest, 1 in this case, since we are calculating principal across the entire loan term....

The results in column C will update automatically when values in column B change. To convert from Celsius to Fahrenheit, use a “C” for Celsius and an “F” for Fahrenheit like this: Note: unit strings are case sensitive. Both the “C” and “F” must be uppercase. Fahrenheit to Celsius To convert from Fahrenheit to Celsius, the formula would be: Other conversions You can use CONVERT to convert weight, distance, time, pressure, force, energy, power, magnetism, temperature, liquid, and volume....

where “group” is the named range B5:B14, and “data” is the named range C5:C14. Note: this is an array formula and must be entered with control + shift + enter. To apply criteria, we use the IF function inside MODE to filter values in a range. In this example, the IF function filters values by group with an expression like this: This compares each value in the named range “group” against the value in E5, which is “A”....

where data (O5:S9), vunits (N5:N9), and hunits (O4:S4) are named ranges, as explained below. This formula uses a lookup table with named ranges as shown below: Named ranges: data (O5:S9), vunits (N5:N9), and hunits (O4:S4). Introduction The goal is to convert an expense in one time unit, to an equivalent expense in other time units. For example, if we have a monthly expense of $30, we want to calculate an annual expense of $360, a weekly expense of $7....

If the number in column B is negative, MAX returns zero. Otherwise, MAX returns the original number. With the MAX function The MAX function provides an elegant solution: This formula takes advantage of the fact that the MAX function works fine with small sets of data — even two values. If the value in B5 is a positive number, MAX will return the number as-is, since positive numbers are always greater than zero....

In this case, we need a formula that returns FALSE as long as entries in C6:C9 sum to a total equal to or below 1000. We use the SUM function to sum a fixed range and then simply compare the result to 1000 using less than or equal to. Note the range C6:C9 is entered as an absolute reference to prevent the reference from changing automatically for each cell that data validation is applied to....

where “rate” is the named range H4. The EFFECT function calculates the effective annual interest rate based on the nominal annual interest rate, and the number of compounding periods per year. To demonstrate how this works, the table shown in the example is set up with various compounding periods in column C. The nominal interest rate is provided in cell H4, which is the named range “rate”. The formula in D5 is: Because named ranges behave like absolute references, this formula can simply be copied down the table....

Date configuration By default, ACCRINTM will calculate accrued interest from the issue date to the settlement date. If you want to calculate total interest from the issue date to the maturity date, supply maturity date instead of settlement date. Example In the example shown, we want to calculate accrued interest for a bond with a 5% coupon rate. The issue date is 5-Apr-2016, the settlement date is 1-Feb-2019, and maturity date is 15-Apr-2026....

The EXACT function takes two arguments, text1 and text2, which should be valid text strings. If these values are entered directly into the function, they should be enclosed in double quotes (""). Examples Below are two examples of the EXACT function used with hardcoded strings. In the first example, the strings are identical, in the second example, the only difference is the capital “A”: In the example shown, the formula in D6, copied down the column, is: You can also use a normal equals sign (=) in a formula, but the comparison is not case sensitive: To count cells that contain specific text, taking into account upper and lower case characters, you can combine EXACT together with the SUMPRODUCT function like this: Detailed explanation here....

Example The IFNA function can be used to trap #N/A errors that may occur with the VLOOKUP function. In the example shown, the formula in F5, copied down, is: where xtable is the named range B5:C12. When the lookup value in column E is found in xtable, VLOOKUP returns the exchange range normally. When the lookup value is not found, VLOOKUP returns #N/A, and IFNA catches this error and returns “Not found”....

Another use for the Name Box is to navigate quickly to any range in a worksheet. If you type Z100 into the Name Box, the active cell will move to that address. If you type A1:A10 into the Name Box, that range will be selected. Finally, if you have one or more named ranges in a workbook, the Name Box behaves like a drop down menu. You can select and navigate quickly to any name....