MrExcel Excel Videos

Excel getting the unique values going across a row. The Remove Duplicates command in Excel always removes rows. What if you want to remove duplicates that appear in the same row? The UNIQUE function offers a By Column option. Also in this video: Sorting sideways in Excel using the Left-to-Right Sort option. What happens if you leave off the optional By_Col argument in UNIQUE. Why UNIQUE returns the original range. Table of Contents (0:00) Sort or Unique sideways in Excel (0:37) UNIQUE by Column (1:48) Nested arrays not supported (2:00) Sorting sideways in Excel (2:38) UNIQUE of rectangular range (3:33) Why UNIQUE returns original range (4:15) BYCOL defaults to False (4:35) UNIQUE of rectangular range (4:55) Wrap-up This video answers...

Stevenson asks: Why won't this SUMIFS work inside of a LET function in Excel? It turns out that he is trying to do a calculation in the first argument of the SUMIFS. This is against the rules. If you would have tried it without the LET, Excel would have given you the mysterious error of "There's a Problem With This Formula". When you try to do it inside of LET, the formula parser is not smart enough to refuse the formula, but you end up with a bunch of #VALUE! errors in Excel. The same limitation applies to SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, SUMIF, AVERAGEIF, DSUM, DCOUNT, DAVERAGE and so on. The workaround is to switch to a FILTER function inside of the LET or simply a FILTER function inside of a SUM. Table of Contents...

LC has 4000 rows of Excel data. After every two rows, he wants to insert a blank row. How can he quickly insert 2000 blank rows in the data? My solution today involves a formula to create alternating cells with text and numbers. Then Home, Find & Select, Go To Special. Select all Formula cells that result in Text. This selects every other row. You can then Insert Sheet Rows and you are done in less than 60 seconds.

How to calculate the current streak in a habit tracker. Amreen is building a habit tracker in Excel. For each goal, he wants to report the current streak. I show how to use XMATCH to solve this problem. Table of Contents() (0:00) The goal (0:40) XMATCH formula for last 1 (1:08) Last zero (1:29) No streak? (1:45) How long is streak? (2:30) Episode 735 for longest streak (3:05) Converting to checkmarks (4:32) Colors in Excel number format

Can Excel VBA return a dynamic array from a user defined function to the grid? Can you repeat each cell in column A based on the number in column B? Check out the amazing answers from Smozgur and XLLambda here: https://www.mrexcel.com/board/threads/repeatbynumber.1216836/ Table of Contents (0:00) Two titles (1:11) Using VBA (2:05) How many cells to return (2:50) Building the array (3:22) Return array to grid (4:00) Using Hash array operator

Keith wants to use the Excel FILTER function to get all names where two conditions are true: Status is A and the Project does not start with 22-05. This video shows how to combine 2 conditions in an Excel FILTER formula.

Excel IF Contains Excel IF cell contains text Excel IF contains excel like wildcard excel wildcard excel like wildcard if How can you test which cells in Excel contain text? Can you use an IF statement in Excel to find which cells contain certain text? This video looks at COUNTIF, FIND, SEARCH, LOWER, SIGN, ISNUMBER, IFERROR functions. Table of Contents (0:00) Question (0:22) Check for equality (0:41) Wildcards fail in IF (1:05) Using COUNTIF with wildcard (1:39) Using FIND in Excel (2:23) Adding SIGN, IFERROR, LOWER (2:59) Using SEARCH in Excel (3:25) Using ISNUMBER with SEARCH (4:22) Excel IF contains solution (4:44) Finding Contains using Filter (5:11) Nancy Faust

Table of Contents (0:00) Excel shows formula not result (0:04) Cell formatted as Text (0:23) in Show Formulas mode

5 Excel topics in 55 seconds. In this video, I answer five common questions about duplicates in Excel. Table of Contents (0:00) Welcome (0:02) Excel Highlight Duplicates (0:12) Excel Remove Duplicates (0:20) Why Excel highlighting duplicates (0:25) Excel Remove Both Duplicates (0:36) Highlight duplicates but not first