Hi there, this is Dave. Ever feel like you’re stuck in a loop doing the same boring Excel tasks again and again? Well, my friend Victor Chan is back with an updated VBA course to help you break out of that loop and make Excel do that work for you. He’s added a new bonus section with 40 VBA code samples. These are useful “recipes” of code that you can use in your own projects right now....
Transcript In this video, we’ll look at how to work with rows and columns to structure data in an Excel Table. Tables offer special features for working with data. Once you have a table defined, it is much easier to select and rearrange data in rows and columns. There are several ways you can add and remove rows. First, you can use the Insert and Delete buttons on the Home tab of the ribbon....
Purpose Return value Syntax =XMATCH(lookup_value,lookup_array,[match_mode],[search_mode]) lookup_value - The lookup value. lookup_array - The array or range to search. match_mode - [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match, 3 = regex match. search_mode - [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending....
Purpose Return value Syntax =YIELDDISC(sd,md,pr,redemption,[basis]) sd - Settlement date of the security. md - Maturity date of the security. pr - Price of security. redemption - Redemption value per $100 face value. basis - [optional] Day count basis (see below, default =0). Using the YIELDDISC function The Excel YIELDDISC function returns the annual yield for a discounted security (non-interest-bearing), such as a Treasury bill, that is issued at a discount but that matures at face value....
Purpose Return value Syntax =ACCRINT(id,fd,sd,rate,par,freq,[basis],[calc]) id - Issue date of the security. fd - First interest date of security. sd - Settlement date of security. rate - Interest rate of security. par - Par value of security. freq - Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4). basis - [optional] Day count basis (see below, default =0). calc - [optional] Calculation method (see below, default = TRUE)....
Explanation In this example, the goal is to add leading zeros to a given number so that the total number of characters displayed is 5. Sometimes this is referred to as “padding” a number with zeros, because the number of zeros needed is variable. If the original number contains 2 digits, 3 zeros are added. If the original number contains 3 digits, 2 zeros are added, and so on. There are two basic ways to solve this problem: (1) convert the number to text with leading zeros (2) apply a custom number format to display the number with leading zeros....
Purpose Return value Syntax =AGGREGATE(function_num,options,ref1,[ref2],...) function_num - Operation to perform (1-19). options - Values to ignore (0-7). ref1 - First argument. ref2 - [optional] Second argument (k). Using the AGGREGATE function The AGGREGATE function returns the result of an aggregate calculation like AVERAGE, COUNT, MAX, MIN, etc. performed on one or more references. The AGGREGATE function is like an upgraded version of the older SUBTOTAL function , and provides more calculation options, and more control over ignoring specific things....
Explanation In this example, the goal is to create a formula that calculates an average by group, using the group names in column C. The solution shown requires three general steps: Create an Excel Table called data List unique groups with the UNIQUE function Calculate averages with the AVERAGEIFS function Create the Excel Table One of the key features of an Excel Table is its ability to dynamically resize when rows are added or removed....
Explanation In this example, the goal is to convert the text values seen in column B to the numeric values seen in column D. There are several ways to fix this problem in Excel, but this article focuses on a formula-based approach to convert text values to numbers. It also explains how to convert values in place with Paste Special, which does not require a formula. The problem Sometimes Excel will incorrectly evaluate a number as a text value....
Explanation First, a little context. Normally, if you have just a couple things you don’t want to count, you can use COUNTIFS like this: =COUNTIFS(range,"<>apple",range,"<>orange") But this doesn’t scale very well if you have a list of many things, because you’ll have to add an additional range/criteria pair to each thing you don’t want to count. It would be a lot easier to build a list and pass in a reference to this list as part of the criteria....
Purpose Return value Syntax =COUNTIFS(range1,criteria1,[range2],[criteria2],...) range1 - The first range to evaluate. criteria1 - The criteria to use on range1. range2 - [optional] The second range to evaluate. criteria2 - [optional] The criteria to use on range2. Using the COUNTIFS function The COUNTIFS function counts cells in a range when they meet one or more specific conditions. COUNTIFS is one of Excel’s most widely used functions, and you find it in all kinds of spreadsheets that perform conditional counts based on dates, text, or numbers....
Explanation Data validation rules are triggered when a user adds or changes a cell value. The UPPER function changes text values to uppercase, and the EXACT function performs a case-sensitive comparison. The AND function takes multiple arguments (logical conditions) and returns TRUE only when all arguments return TRUE. The first logical condition compares the value input by the user to an uppercase version of the same value: EXACT(C5,UPPER(C5) The second logical condition tests that input to C5 is actually text...
Transcript In this video, we’ll look at a few examples of the kind of errors that Excel will flag on a worksheet and the rules that control these errors. First, to recap, the rules that govern the errors that Excel flags are located at options > formulas > error checking rules Each rule can be disabled individually or, you can turn off background error checking completely. If you turn off background error checking, you’re disabling all the rules that visually flag information on the worksheet in the background....
About This Shortcut After find has been used, this shortcut will search again for the same thing. Note: with Mac Excel 2011, you must close the Find dialog before this will work. About This Shortcut Use this shortcut after find criteria has been set to step “back” and find the previous match (if any). This shortcut allows you to reverse the normal direction of “find next”.
Purpose Return value Syntax =GAMMA(number) number - A decimal number for which you want to calculate the gamma function value. Using the GAMMA function The GAMMA function returns the gamma function value Γ(n) for a given number. The key relationship to understand is that Γ(n) = (n - 1)!, which means Excel’s GAMMA function essentially computes factorials shifted by one position. For example, GAMMA(5) returns 24, which is equivalent to 4!...
Explanation The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas. In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by the RANDARRAY function , a new function in Excel 365. RANDARRAY returns 6 random numbers to INDEX, which then retrieves 6 random values from the named range chars. The results from INDEX are then concatenated together with the TEXTJOIN function....
Explanation An interesting problem in Excel is how to look up information related to the maximum value in a set of data. For example, if you have a dataset of property listings and prices, you might want to find details about the property with the highest price. The best way to solve this problem depends on which version of Excel you use. In Excel 2019 and earlier, the classic solution is to use the MAX function to find the maximum value, then use this value in an INDEX and MATCH formula as the lookup value....
Explanation This example demonstrates how to calculate the nth occurrence of a specific day of the week within a month. For instance, you might need to find the 2nd Tuesday, the 4th Wednesday, or the 1st Friday of any given month. The worksheet is structured with the starting date in column B, the target day of week (dow) as a number in column C, and the occurrence number (n) in column D....
Explanation Following order of operations, Excel first calculates the difference between the values (the actual change in sales) then divides that result by the original, or “old” value to get the decimal value -0.3435: =(D6-C6)/C6 =(49500-75400)/75400 =-25900/75400 =0.0688 Note: you must format the result using Percentage number format to display the final result as 7% Explanation In this example, the goal is to determine the percentage discount for each item shown in the table, given an original price and a sale price....
Explanation In this example, the goal is to get the workbook path without the workbook name . For example, given a workbook called fruits.xlsx saved to: C:\examples\fruits.xlsx We want the path only like this: C:\examples\ TEXTBEFORE solution In a modern version of Excel (Excel 2021 or later) the simplest way to solve this problem is to use the TEXTBEFORE function like this: =TEXTBEFORE(CELL("filename",A1),"[") TEXTBEFORE is designed to return all text before a given delimiter....
