MrExcel Excel Videos

Learn how to protect formula cells in Excel. Episode Recap: All 16 billion cells on the sheet start out Locked First, unlock all cells. Select all cells using triangle northwest of A1 Ctrl+1 to display Format Cells Go to Protection Tab and uncheck Locked Home, Find & Select, Formulas, Ctrl+1, Locked Review, Protect worksheet Don't bother with a password. Easy to lose. Easy to break. The only person who wins with a password are the Estonians who get $39 Scroll through the Protect Sheet dialog: you can choose to allow sorting, filtering Preventing people from seeing your formulas Choice 1: Locked, and uncheck Selected Locked Cells. Problem: strange to navigate with keys Choice 2: Locked, Hidden, and check Select Locked Cells. Easier to...

VLOOKUP when used with False is a slow function Sorting the data AZ does not speed up the function Sorting by popularity could speed up the function Switching to VLOOKUP with True is faster, but it will report the wrong answer if the item is not found To mitigate the problem, do a VLOOKUP(A2,Table,1,True) to see if the result is A2 first 14000 VLOOKUP(True) and 7000 IF run faster than 7000 VLOOKUP(False)

With a tiered commission, bonus, or discount program, you often have to nest your IF functions The Excel 2003 limit was 7 nested IF statements. You can now nest 32, but I don't think you should ever nest 32 When would you ever use the approximate match version of VLOOKUP? This is the time. Translate the discount program into a lookup table VLOOKUP won't find the answer in most cases. Putting ,True at the end will tell VLOOKUP to find the value just less. This is the only time the VLOOKUP table has to be sorted. Don't want the VLOOKUP table off to the side? Embed it in the formula. F2 to edit the formula. Select the lookup table. Press F9. Enter.

VLOOKUP can not look to the left of the key field! Use MATCH to find where the item is located, then use INDEX to retrieve the lookup value. Or - in real life, rearrange the lookup table This episode features an original letterpress poster from Bobby Rosenstock at justajar.com

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.