MrExcel Excel Videos

Say that you have to do 12 columns of VLOOKUP Carefully use a single dollar sign before the column of the lookup value Carefully use four dollar signs for the lookup table You are still hard-coding the third column argument. One common solution is to add a row of helper cells with the column number. Another less-efficient solution is to use COLUMN(B2) inside the VLOOKUP formula. But, doing 12 VLOOKUP for each row is very inefficient Instead, add a helper column with a heading of WHERE and do a single Match. The MATCH takes as long as the VLOOKUP for January. You can then use 12 INDEX functions. These are incredibly fast compared to VLOOKUP. The INDEX will point to a single column of answers with $ before the rows. The INDEX will point...

VLOOKUP solves many problems Common VLOOKUP problems: If VLOOKUP starts out working, but #N/A becomes more prominent: forgot $ in lookup table A few #N/A: items missing from the table None of the VLOOKUP work: check for trailing spaces Remove trailing spaces with TRIM Numbers and numbers stored as text Select both columns and use Alt+DEF Episode includes a joke that both accountants and IT people find funny, but for different reasons

There are a secret class of formulas called Array Formulas. An array formula can do thousands of intermediate calculations. They often require you to press Ctrl+Shift+Enter, but not always. The best book on array formulas is Mike Girvin's Ctrl+Shift+Enter. INDIRECT lets you use concatenation to build something that looks like a cell reference. Dates are nicely formatted but are stored as a number of days since January 1 1900. Concatenating two dates will point to a range of rows in Excel. Asking for the ROW(INDIRECT(Date1:Date2)) will "pop out" an array of many consecutive numbers Using the WEEKDAY function to figure out if a date is Friday. How many Fridays occur in this July? To watch a formula calculate in slow motion, use the...

Three ways to handle multiple conditions in an IF function. Episode recap: The simplest IF function is =IF(Logical Test,Formula if True, Formula if False) But what to you do if you have to test two conditions? Many people will do =IF(Test 1, IF(Test 2, Formula if True, False), False) This gets unwieldy if 3, 5, 17 conditions! Instead, use =IF(AND(t1,t2,t3,t4),Formula if True, Formula if False) If you like AND, consider OR, NOT for other situations NAND can be done with NOT(AND()) NOR can be done with NOT(OR()) Be careful when using XOR as the results are not what you expect

Yesterday in podcast 2023 I used NETWORKDAYS and NETWORKDAYS.INTL There is a similar pair of functions WORKDAY and WORKDAY.INTL This function takes a start date, then a number of days, weekend type and holidays and calculates the end date. For example, calculate when a 30 work-day probation period might end. But a more common use might be to build an employee schedule or employee calendar Put the first start date. Then add 1 workday using WORKDAY or WORKDAY.INTL. Drag that formula down to build the schedule.

Calculate work days between two dates excluding weekends and holidays. This episode also handles 6-day workweeks, countries where the weekend is Friday and Saturday, plus businesses like barber shops or farm markets that have operating hours on Monday, Thursday, Friday, Saturday. Episode Recap: Date math in Excel: Subtract earlier date from later date + 1 To ignore weekends, use NETWORKDAYS function To not count holidays, use the 3rd argument in NETWORKDAYS For non-standard weekends, use NETWORKDAYS.INTL Secret 7-binary digit code for work weeks that are not consecutive days Alt+ESF for Paste Special Formulas

How to discover which Excel calculation function to use. This episode shows how to use the PMT function to calculate a loan payment and how the Function Arguments helps with tricky arguments like dividing the interest rate by 12. Episode recap: There are over 400 calculation functions in Excel. If you don't know what function to use, click the fx button. This button is on the Formulas tab and also to the left of the formula bar. Type what you are trying to do and Search. The results will show you the functions related to your search term. When you click OK, you are taken to the Function Arguments dialog. This dialog provides help for each argument. Bold arguments are required. Once the bold arguments are completed, you will see the...

How to highlight all of the formula cells in a spreadsheet Select all cells using the icon northwest of A1 Home, Find & Select, Formulas or, Find & Select, Go To Special, Formulas Once you have the formulas selected, use Cell Styles or a Fill Color Look for things that should be formulas that are not! Once you have the formulas selected, you can use Trace Precedents Or, if you turn of Edit Directly in Cells, Double-Click Once you have multiple cells selected, press Enter to move to the next cell

F2 puts a formula in edit mode, Esc will exit edit mode Faster way to see all formulas: Ctrl+` Press again to toggle back