Purpose Return value Syntax =RAND() Using the RAND function The RAND function returns a random decimal number between 0 and 1. For example, =RAND() will generate a number like 0.422245717. The RAND function takes no arguments . RAND recalculates when a worksheet is opened or changed. RAND is a volatile function , and can cause performance issues in large or complex worksheets. Examples RAND takes no arguments: =RAND() // returns number like 0....

Explanation This formula relies on the helper table visible in the range B4:D10. Column B contains the six numbers we want as a final result. Column C contains the probability weight assigned to each number, entered as a percentage. Column D contains the cumulative probability, created with this formula in D5, copied down: =SUM(D4,C4) Notice, we are intentionally shifting the cumulative probability down one row, so that the value in D5 is zero....

Explanation First, you should know that Excel contains two functions, CLEAN and TRIM, that can automatically remove line breaks and extra spaces from text. For example to strip all line breaks from a cell, you could use: =CLEAN(B5) For a quick demo of CLEAN and TRIM, watch this video . In this case, however, we are removing line breaks and replacing them with commas , so we are using the SUBSTITUTE function instead of CLEAN....

Explanation The replace function lets you replace text based on its location and length. In this case, we want to strip off the drive and path and leave only the document name. The length of this part of the string (text) is 24 and the starting position is 1, and the pattern never changes. The REPLACE function can handle this easily, we just need to provide a cell reference (B6), a starting position (1), the number of characters to replace (24), and the text to use for the replacement (""):...

Explanation The goal is to generate a series of sequential weekend days (Saturday and Sunday) with a formula. The start date is entered in cell B5. The number of dates to create (n) is entered in cell B8. If either of these two values are changed, a new list of weekend dates should be generated. In the current version of Excel, the easiest way to solve this problem is to use the SEQUENCE function inside the WORKDAY....

Purpose Return value Syntax =SUMX2MY2(array_x,array_y) array_x - The first range or array containing numeric values. array_y - The second range or array containing numeric values. Using the SUMX2MY2 function The SUMX2MY2 function returns the sum of the difference of squares of corresponding values in two arrays. The “m” in the function name stands for “minus”, as in “sum x 2 minus y 2 “. SUMX2MY2 takes two arguments , array_x and array_y ....

About This Shortcut With add to selection mode enabled, you don’t need to hold down the control key to add non-adjacent cells to the current selection. About This Shortcut Pressing the escape key while editing a cell aborts the operation and leaves the cell in it’s original state.

Explanation This formula is composed of two lookups for the same client. The first lookup finds the onsite rate for the client in column B and multiplies the result by the number of hours in column C: =VLOOKUP(B5,rates,2,0)*C5 The second lookup finds the offsite rate for same client and multiplies the result by the number of hours in column D: VLOOKUP(B5,rates,3,0)*D5 In the final step the two results are added together:...

The Waterfall Chart is a built-in chart type in Excel 2016+. Waterfall charts are used to plot the cumulative result of values as a visual running total. The initial and final values sit directly on the horizontal axis and intermediate values are plotted as floating columns - positive values extend upward, and negative values extend downward. The effect is a staircase like display is useful for showing how an initial value is affected by subsequent positive and negative changes....

Exceljet courses are single-user access, and the course player tracks progress at the user level. However, you can purchase more logins at an affordable price, either now or later. The way it works is that you buy one full-price course or bundle, then purchase additional logins as needed. Each additional login is $50.00, or you can buy a 10-pack for $250.00 ($25.00/each). All pricing is a one-time fixed fee, and course access is permanent....

About This Shortcut This shortcut applies the Date format “dd-mmm-yy” to the selection. You can also use the shortcut Control + 1 to open the Format cells dialog box, which allows you to choose other date formats and to apply a custom date format . Once you have applied a date format, you can use Paste Special > Formats to apply the format to other cells. About This Shortcut This shortcut will apply a default Time format to selected cells....

Explanation In this example, the IFERROR function is used to trap and suppress the #DIV/0! error that occurs when there is no value for Orders (column D). Without IFERROR, the formula C5/D5 would display a #DIV/0! error in E6 and E9. The IFERROR function takes two arguments: a value (usually entered as a formula), and a result to display if the formula returns an error. The second argument is only used if the first argument throws an error....

A clustered column chart displays more than one data series in clustered vertical columns. Each data series shares the same axis labels, so vertical bars are grouped by category. Clustered columns allow the direct comparison of multiple series, but they become visually complex quickly. They work best in situations where data points are limited. Pros Allow direct comparison of multiple data series per category Can show change over time Cons More difficult to compare a single series across categories Become visually complex as categories or series are added Tips Limit data series and categories Avoid all 3d variants A 100% stacked column chart is an Excel chart type meant to show the relative percentage of multiple data series in stacked columns, where the total (cumulative) of stacked columns always equals 100%....

A Combo Chart is a hybrid of two more chart types, for example, a column chart plus a line chart. Pros Able to display multiple chart types in a single chart Able to plot values with completely different sales Can show how one factor is influenced by another Cons Inherently more complex than other chart types Can become complicated quickly

About This Shortcut With a single cell selected, this shortcut will enter a value and leave the same cell selected. About This Shortcut With multiple cells selected, this shortcut will enter the same data in all cells in the selection at once. This is a great way to skip a copy & paste step. Cells do not need to be contiguous; use Control (Win) or Command (Mac) to select non-contiguous cells before using Control + Enter....

Purpose Return value Syntax =CONCAT(text1,[text2],...) text1 - First text value, cell reference, or range. text2 - [optional] Second text value, cell reference, or range. Using the CONCAT function The CONCAT function concatenates (joins) values supplied as references or constants. Unlike the CONCATENATE function (which CONCAT replaces), CONCAT will accept a range of cells to join, in addition to individual cell references. The CONCAT function automatically ignores empty cells. The CONCAT function accepts multiple arguments called text1, text2, text3, etc....

Purpose Return value Syntax =CORREL(array1,array2) array1 - The first set of data values. array2 - The second set of data values. Using the CORREL function The CORREL function calculates the Pearson correlation coefficient between two data sets. It measures both the strength and direction of the linear relationship between variables, providing a standardized measure that ranges from -1 to 1. Key features Returns values between -1 and 1 (inclusive) Positive values close to 1 indicate positive correlation Negative values close to -1 indicate negative correlation Values close to zero indicate weak correlation Unit-independent and standardized measure Both arrays must have the same number of data points Works with numbers only - text and logical values are ignored Note: Excel also provides PEARSON function which is identical to CORREL....

Purpose Return value Syntax =COVAR(array1,array2) array1 - The first set of data values. array2 - The second set of data values. Using the COVAR function The COVAR function calculates the population covariance between two data sets. It measures the degree to which two variables vary together, providing insight into their linear relationship. The return value from the function is a single number that can be positive, negative, or zero , depending on the relationship between the variables....

Explanation One of the key skills you need to be good with Excel formulas is concatenation . Put simply, concatenation is just a fancy name for joining text together. In Excel formulas, the primary operator for concatenation is the ampersand (&). A good example of a simple concatenation task is the creation of an email address using a first and last name. There are many ways to create an email address, but the core problem is to join together a name and a domain, as seen in the worksheet shown....

Explanation To include double quotes inside a formula, you can use additional double quotes as escape characters . By escaping a character, you are telling Excel to treat the " character as literal text. You’ll also need to include double quotes wherever you would normally in a formula. For example, if cell A1 contains the text: The Graduate and you want wrap that text inside double quotes (""), you can use this formula:...