Transcript In this video, we’ll look at several different ways you can create charts in Excel. In later versions of Excel, starting with Excel 2013, the best option for creating a new chart is usually the Recommended Charts button on the Insert tab of the ribbon. Just select some data then go to Insert > Recommended Charts. Excel will open the Insert Chart window and list recommended charts, each with a preview....
Transcript In this video, we’ll look at a few ways to filter out data you don’t want to see in a chart. Here we have a simple summary of sales by quarter in 4 regions, with totals for each month, and quarterly totals as well. Now, if I select any cell in the data, and insert a chart, Excel includes the totals, which you probably don’t want. So let’s go over some ways to exclude this data....
Transcript In this video we’ll introduce the idea of a dynamic range and show you why you might want to use one. Let’s take a look. In this first worksheet, we have a list of ten properties set up in a normal way. If we check the formulas that summarize this data to the right, you can see that each formula refers to a standard range of cells. So, what happens if I add some more properties to this list?...
Explanation In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is: =IF(ISBLANK(C5),"",COUNTA($C$5:C5)) The IF function first checks if cell C5 has a value with the ISBLANK function : ISBLANK(C5) // TRUE if empty, FALSE if not If C5 is empty, ISBLANK returns TRUE and the IF function returns an empty string ("") as the result....
An area chart is a primary Excel chart type, with data series plotted using lines with a filled area below. Area charts are a good way to show change over time with one data series. They offer a simple presentation that is easy to interpret at a glance. Pros Simple presentation; easy to read and create Good for showing trends over periods of time Can handle positive and negative values Cons Don’t usually work well for multiple data series Line can imply more data than actually available (compared to bar or column chart) A stacked area chart is a primary Excel chart type that shows data series plotted with filled areas stacked, one on top of the other....
Explanation In this example, the goal is to calculate a monthly average for the amounts shown in column C using the dates in column B. The article below explains two approaches. One approach is based on the AVERAGEIFS function , which is designed to calculate averages using multiple criteria. The second approach is based on the FILTER function and the AVERAGE function . For convenience only, both solutions use the named ranges amount (C5:C16) and date (B5:B16)....
Explanation The example on this page shows a simple array formula. Working from the inside out, the expression: C5:C12-D5:D12 Results in an array containing seven values: {17;19;32;25;12;26;29;22} Each number in the array is the result of subtracting the “low” from the “high” in each of the seven rows of data. This array is returned to the MAX function: =MAX({17;19;32;25;12;26;29;22}) And MAX returns the maximum value in the array, which is 32....
Explanation In this example, the goal is to count values in three non-contiguous ranges with criteria. To be included in the count, values must be greater than 50. The COUNTIF counts the number of cells in a range that meet the given criteria. However, COUNTIF does not perform counts across different ranges. If you try to use COUNTIF with multiple ranges separated by commas, or in an array constant , you’ll get an error....
Introduction Number formats control how numbers are displayed in Excel. The key benefit of number formats is that they change how a number looks without changing any data. They are a great way to save time in Excel because they perform a huge amount of formatting automatically. As a bonus, they make worksheets look more consistent and professional. Video: What is a number format What can you do with custom number formats?...
Purpose Return value Syntax =EXPONDIST(x,lambda,cumulative) x - The value at which to evaluate the distribution (must be ≥ 0). lambda - The rate parameter of the distribution (must be > 0). cumulative - A logical value that determines the form of the function. If TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function. Using the EXPONDIST function The EXPONDIST function calculates values for the exponential distribution, which is a continuous probability distribution commonly used to model the time between events in a Poisson process....
Explanation In this example, the goal is to find the closest match to a target value entered in cell E5. Although it may not look like it, this is essentially a look-up problem. The easiest way to solve this problem is with the XLOOKUP function . However in older versions of Excel without the XLOOKUP function, you can use an INDEX and MATCH formula . Both approaches are explained below....
Purpose Return value Syntax =GAMMA.DIST(x,alpha,beta,cumulative) x - The value at which to evaluate the distribution. alpha - The shape parameter of the distribution. beta - The scale parameter of the distribution. cumulative - A logical value that determines the form of the function. If TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function. Using the GAMMA.DIST function The GAMMA.DIST function calculates values for the gamma distribution, which is a continuous probability distribution commonly used in statistical analysis....
Purpose Return value Syntax =GEOMEAN(number1,[number2],...) number1 - First value or reference. number2 - [optional] Second value or reference. Using the GEOMEAN function The Excel GEOMEAN function calculates the geometric mean . Geometric mean is the average of a set of products — technically, the nth root of n numbers. The general formula for the geometric mean of n numbers is the nth root of their product. The equation looks like this:...
Explanation In this example, the goal is to extract the first name from a list of names in “Last, First” format as seen in column B. There are several ways to approach this problem. In the current version of Excel, the easiest solution is to use the TEXTAFTER function. In older versions of Excel, it can be solved with a more complex formula based on the RIGHT, LEN, and FIND functions....
Explanation In this example, the goal is to retrieve the close price on July 1, 2021 for each symbol shown in column B. This can be done with the STOCKHISTORY function , whose main purpose is to retrieve historical information for a financial instrument over time. In many configurations, STOCKHISTORY returns an array of values that spill onto the worksheet into multiple cells. However, in this case, we want only a single result for each symbol....
Explanation In this example, the goal is to get a normal path to the current workbook, without a sheet name, and without the square brackets ("[ ]") that normally enclose the workbook name. This is a pretty simple problem in the latest version of Excel, which provides the TEXTBEFORE function . In older versions of Excel, you can use a more complicated formula based on the LEFT and FIND functions. Both options use the CELL function to get a full path to the current workbook....
Explanation The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates. For example, in the screenshot shown, the formula in D6 is: =NETWORKDAYS(B6,C6) // returns 5 This formula returns 5 since there are 5 working days between December 23 and December 27, and no holidays have been provided. Note that NETWORKDAYS includes both the start and end dates in the calculation if they are workdays....
About This Shortcut This shortcut will group selected pivot table items. If you want to group a field automatically by date or number, you only need to select one item in the field before using this shortcut. About This Shortcut This shortcut will ungroup selected pivot table items. If you want to ungroup a field, you only need to select one item in the field before using this shortcut.
Purpose Return value Syntax =GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship]) row_fields - The values for grouping. values - The values to aggregate. function - The calculation to run when aggregating. field_headers - [optional] 0 = No, 1 = Yes, don’t show, 2 = No, generate, 3 = Yes, show. total_depth - [optional] Totals and subtotals. 0 = No, 1 = Grand Totals, 2 = Both, -1 = Grand Totals at top, -2 = Both at top....
Explanation In this example, the goal is to highlight rows in the data shown when the date is a specific day of week. The target day of week is a variable selected with a dropdown menu in cell F5, which contains abbreviated day names. This problem can be easily solved by applying conditional formatting with a formula based on the TEXT function. The dropdown menu is implemented with data validation ....
