Excel supports both full column , and full row references. A full row reference is a reference that refers to an entire row . For example, to sum all of the values in row 1, you can use a formula like this: =SUM(1:1) Notice a full row reference is entered like other ranges, with a colon (:) separating the starting point from the ending point. Since there are no columns in a full row reference, the literal translation of the range 1:1 is “every cell in row 1”....
Purpose Return value Syntax =GAMMALN.PRECISE(x) x - A positive real number for which you want to calculate the natural logarithm of the gamma function. Using the GAMMALN.PRECISE function The GAMMALN.PRECISE function returns the natural logarithm of the gamma function, ln(Γ(n)), for a given number. This is useful in statistical calculations, such as those involving probability distributions, where the gamma function appears in the denominator and direct computation could result in very large or very small numbers....
Explanation In this example, the goal is to extract the last name from a list of names in “Last, First” format as seen in column B. In the current version of Excel, the easiest solution is to use the TEXTBEFORE function. In older versions of Excel, it can be solved with a more complex formula based on the LEFT and FIND functions. Both approaches are explained below. Modern solution In the current version of Excel, the TEXTBEFORE function is the best way to solve this problem....
Transcript In this video, we’ll look at how to add and remove data series in a chart. 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....
Transcript So what’s a mixed reference? A mixed reference is a reference that’s part relative and part absolute. Let’s take a look. So, we’ve looked at both relative and absolute references, and also at a situation where we needed to use both at the same time. These are sometimes called “mixed references.” A good example of a problem that requires a mixed reference is a multiplication table. If we enter a formula to multiply the numbers in column B by row 4, and use only relative references, we end up with huge numbers....
Transcript Excel keeps track of the workbooks you’ve opened recently. When you’re ready to resume work on a workbook, it’s often faster to open it from the Recent Workbook list. Let’s take a look. You can find the list of recently opened workbooks on the File tab, under Recent. Here you’ll see a list of workbooks, with the most recently opened workbooks at the top. There is a checkbox at the bottom of the recent files list that says “Quickly access this number of recent workbooks”....
Transcript In this lesson, we’ll look at how to replace what you’ve found. Find and Replace is a powerful tool when making global changes to worksheets. Let’s take a look. Here we have the worksheet we looked at previously. Let’s run through a few examples to explore how Find and Replace work. First, let’s replace every occurrence of Ann with Anna . We need to enter Ann in the “Find what” box, and enter Anna in the “Replace with” box....
Transcript In this video, we’ll look at how to use conditional formatting to highlight rows and columns associated with exact match lookups. Whenever you have a lookup table visible to users, a nice touch is to highlight the rows and columns that match the current lookup. This makes it easy for users to see where a value is coming from, and it’s a nice way to make your spreadsheets more friendly and transparent....
Transcript You’ve probably noticed that functions use parentheses, and inside those parentheses are certain inputs. These inputs have a special name: arguments. Let’s look at some examples. Arguments can be required or optional. Some functions take three or more arguments, and some functions don’t take any arguments at all. A good example of a function that takes no arguments is the TODAY function , which returns the current date. To use it, just enter an equal sign, TODAY, and empty parentheses....
Transcript In this lesson we’ll take a look at another common use case for relative references—creating row and column totals in a table. These kinds of totals often use formulas that are identical except for the cell references. Let’s take a look. Here we have a table that shows the number of widgets sold over a 12-month period. There are six different kinds of widgets, so the table has six rows of data....
Explanation In the example shown, we want to mark or “flag” records where the color is red OR green. In other words, we want to check the color in column B, and then leave a marker (x) if we find the word “red” or “green”. In D6, the formula is: =IF(OR(B6="red",B6="green"),"x","") This is an example of nesting – the OR function is nested inside the IF function. Working from the inside out, the logical test is created with the OR function:...
Explanation The goal is to return the first non-blank value in each row from columns B:E, moving left to right. One way to solve this problem is with a series of nested IF statements. Since all cells are contiguous (connected) another way to get the first value is with the XLOOKUP function. Both approaches are explained below. Nested IF solution In the worksheet shown, the formula in cell G5 is:...
Transcript Sometimes, you might need to write a formula that uses the IF function to test for this OR that, or this AND that. There are two special functions, AND and OR, that make this easy to do. Let’s take a look. In this first worksheet, we have a list of employees. Let’s assume that you need to group these employees into two groups. Group A includes employees in Sales and Marketing and group B includes employees in Fulfillment, Support, or Engineering....
Explanation In this example, the goal is to look up the correct shipping cost for an item based on the shipping service selected and the item’s weight. At the core, this is an approximate match lookup based on weight. The challenge is that we also need to filter by service. This means we must apply criteria in two steps: (1) match based on Service and (2) match based on Weight. The screen below shows the basic idea:...
Purpose Return value Syntax =INTERCEPT(known_ys,known_xs) known_ys - An array or range of numeric data points (dependent values). known_xs - An array or range of numeric data points (independent values). Using the INTERCEPT function The INTERCEPT function returns the point at which a line will intersect the y-axis based on known x and y values. The intercept point is based on a regression line plotted with known x and y values. A regression line is a line that best fits that known data points....
Purpose Return value Syntax =MINVERSE(array) array - A square array of numbers only. Using the MINVERSE function The MINVERSE function returns the inverse matrix of a given array. The product of a matrix and its inverse is the identity matrix , a n × n square matrix with ones on the main diagonal and zeros in every other position. The MINVERSE function takes just one argument, array , which should be a square matrix, with an equal number of rows and columns....
Explanation In this example, the goal is to return a number, 1-12, for any month name of the year. For example, given the string “January” we want to return 1, “February” should return 2, and so on. If we had a valid Excel date , we could use a number format for this task, but because we are starting with a text string , we need another way. The approach we take in this example is to create a date fragment that can be correctly interpreted by Excel as a valid date....
About This Shortcut In Windows, this shortcut now displays the Name Manager dialog, where you can click the New button, to create a new name . There is no actual shortcut to create a single new name, except through the ribbon: Alt M + MD On a Mac running Excel 365 , the shortcuts fn + Control + F3 and fn + Command + F3 both open the Name Manager. In older Mac versions, fn + Control + F3 displays the Define Name dialog box, which offers to name a range based on the current selection....
Purpose Return value Syntax =OR(logical1,[logical2],...) logical1 - The first condition or logical value to evaluate. logical2 - [optional] The second condition or logical value to evaluate. Using the OR function The OR function is one of Excel’s logical functions. It is designed to test multiple conditions simultaneously and return TRUE if any condition is TRUE. If all conditions are FALSE, the OR function returns FALSE. The OR function is often combined with other functions like AND, NOT, and IF to construct more complex logical tests....
To apply conditional formatting to a pivot table, create a new conditional formatting rule and pay particular attention to the “apply rule to” settings as described below. In the example shown, there are two rules applied. The green shows the top 5 values using a rule like this: Details Pivot tables are dynamic and change frequently when data is updated. If you created conditional formatting rules based on “selected cells” only, you may find that the conditional formatting is lost or not applied to all data when the pivot table is changed, or when data is refreshed....
