For a very long time, Excel introduced new functions at a leisurely pace. Every few years, a handful of new functions would appear, most aimed at technical and edge-case problems. Most users greeted these new functions with a yawn, if they noticed at all. All that changed in 2019 when Microsoft’s Excel team kicked things into high gear and suddenly began introducing brand-new functions at a furious pace. You might not know it, but Excel now has nearly 50 new functions!...
Explanation In the worksheet shown, column B contains 12 dates. The goal is to calculate the next working day after each date, taking into account weekends (Saturday and Sunday) and the holidays listed in column F. In other words, the formula should automatically skip weekends and any dates defined as non-working days. WORKDAY function The WORKDAY function takes a date and returns the next working day n days in the future or past....
Explanation In this example, the goal is to test if a numeric value is a whole number. There are several ways to go about this. One of the easiest ways is to use the MOD function with a divisor of 1. Any whole number divided by 1 will result in a remainder of zero: =MOD(5,1)=0 // whole numbers return zero Any decimal number will have a remainder equal to the decimal portion of the number:...
Purpose Return value Syntax =ODDLYIELD(sd,md,ld,rate,pr,redem,freq,[basis]) sd - Settlement date of the security. md - Maturity date of the security. ld - Last interest date of security. rate - Interest rate of security. pr - Price per $100 face value. redem - Redemption value per $100 face value. freq - Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4). basis - [optional] Day count basis (see below, default =0)....
Explanation Note: this example assumes that fuel is added to capacity at each gas stop, in order to calculate miles per gallon (MPG) based on the miles driven and fuel used since the last stop. In addition, this example keeps all data in an Excel Table called “data” to illustrate how Tables can make some formulas easier to maintain. The formula in E5 subtracts the mileage in the row above from mileage in the current row to calculate distance (miles driven) since the last gas stop:...
To extract a list of unique values from a data set, you can use a pivot table. In the example shown, the color field has been added as a row field. The resulting pivot table (in column D) is a one-column list of unique color values. Data The data in this pivot tables comes from the Excel Table in column B. Excel Tables are dynamic and will automatically expand and contract as values are added or removed....
In the example shown, a pivot table is used to show the month-over-month variance in sales for each month of a given year. The variance is displayed both as an absolute value and also as a percentage. The year is selected by using a global filter. Source data The source data contains three fields: Date, Sales, and Color converted to an Excel Table . Below are the first 10 rows of data:...
Purpose Return value Syntax =PROPER(text) text - The text that should be converted to proper case. Using the PROPER function The PROPER function capitalizes each word in a given text string. PROPER function takes just one argument, text , which can be a text value or cell reference. PROPER first lowercases any uppercase letters, then capitalizes each word in the provided text string. Numbers, punctuation, and spaces are not affected. PROPER will convert numbers to text with number formatting removed....
Transcript In this video, we’ll look at how to select chart elements. After you’ve created a chart, you have several options for selecting and customizing individual chart elements. First, you can use the Chart Elements menu on the Format tab of the ribbon. After you select a chart, this menu will contain a list of the main chart elements. You can use this menu to navigate to the chart title, the plot area, the data series, and so on....
Transcript In this video, we’ll look at shortcuts for editing cells. First, the keyboard shortcut for editing a cell is F2 on Windows, and Control + U on a Mac. With Excel’s default settings, this will put your cursor directly in the cell, ready to edit. You can also double-click a cell to edit. If you want to always edit cells in the formula bar, even when you use a keyboard shortcut to edit a cell, you’ll need to change a setting....
Transcript In this video, we’ll look at the shortcuts you can use for find and replace in Excel. To find something in Excel, you can use Control + F on Windows and Command + F on a Mac. Control F also works on a Mac. Once you’ve found something, you can press Return or Enter to “find again”. If you hold down the shift key, you can move through matches in the opposite direction....
Explanation The formula in this example converts amounts in USD to other currencies using currency codes and a simple lookup table. The available currencies and exact conversion rates can be adjusted by editing the values in the table on the right. The core of this formula is the VLOOKUP function, configured like this: =VLOOKUP(D5,xtable,2,0) The inputs to VLOOKUP are given as follows: lookup_value - the currency code in D5 table_array - the currency conversion table in G5:H10, given as the named range xtable col_index_num - 2, because we want to return values from column H range_lookup - 0, because we want VLOOKUP to perform an exact match For more information on VLOOKUP, see this detailed overview ....
Explanation In this example, the goal is to sum the first n matching values in a set of data. Specifically, we want to sum the first 3 values for both Red and Blue, based on the order they appear in the table. There are 12 values total; 6 entries each for Red and Blue. All data is in Excel Table named data in the range B5:C16. Example formula In the example shown, the formula in cell G5, copied down, is:...
Purpose Return value Syntax =TBILLPRICE(settlement,maturity,discount) settlement - Settlement date of the security. maturity - Maturity date of the security. discount - The discount rate for the security. Using the TBILLPRICE function The TBILLPRICE function returns the price per $100 face value for a Treasury bill, based on a settlement date, a maturity date, and a discount. In the example shown, the settlement date is 5-Feb-2019, the maturity date is 1-Feb-2020, and the discount is 2....
Explanation In the example shown, the goal is to enter a valid time based on days, hours, and minutes, then display the result as total hours. The key is to understand that time in Excel is just a number. 1 day = 24 hours , and 1 hour = 0.0412 (1/24). That means 12 hours = 0.5, 6 hours = 0.25, and so on. Because time is just a number, you can add time to days and display the result using a custom number format, or with your own formula, as explained below....
Explanation In this example, the goal is to extract unique values from three separate ranges at the same time: range1 (C5:C16), range2 (D5:D15), and range3 (F5:F13). At one time, this was a difficult problem, since UNIQUE is programmed to accept only one array and there is no obvious way to provide another range. However, with the introduction of the VSTACK function , the solution is straightforward. UNIQUE function The UNIQUE function makes it very easy to extract unique values from a range....
Explanation This example shows how to use the VLOOKUP function to calculate the total shipping cost for an item in one formula, where the cost per kilogram (kg) varies according to weight. This requires an “approximate match” since in most cases the actual weight will not appear in the shipping cost table. For convenience, the range E5:F9 is named cost_table . This is an “approximate-match” lookup problem because it is not likely that a given weight will be found in the cost table....
Explanation In this example, the goal is to perform a case-sensitive lookup on Color with VLOOKUP. In other words, a lookup value of “RED” must return a different result from a lookup value of “Red”. This presents several challenges. First, Excel is not case-sensitive by default, and there is no built-in setting to make VLOOKUP case-sensitive. For example, if we try a standard VLOOKUP formula in exact match mode, we get the wrong result:...
Explanation In this example, the goal is to look up the correct price of the product number entered in cell F4 using the product codes in column B. This problem is trickier than it looks. Each product code begins with 3 uppercase letters and ends with 2 or 3 uppercase letters. In the middle of the product code is a number between 2 and 4 digits. This is the number we want to use for a lookup value....
Transcript In this video we’ll look at three basic array formula examples. The latest version of Excel ships with new functions like UNIQUE , SORT, FILTER and so on that make certain array formulas easy. But you can still build traditional array formulas as well, and they can solve some tricky problems. In this first example, we have high and low temperatures for seven days. We want to calculate the biggest change on any given day....
