MrExcel Excel Videos

Karen from Chicago has an Excel worksheet with 200 columns. She wonders if there is a fast way to determine which columns have a filter applied and what filter is applied? In this episode, I use some VBA from Excel MVP Roger Govier. Copy this code or download a workbook from Which Excel Columns are Filtered? Links in the video: 1) AutoFilter VBA properties from Autofilter VBA Operator Parameters 2) The UserVoice request with one vote: Excel for Windows (Desktop Application): Hot (7770 ideas) – Welcome to Excel’s Suggestion Box! 3) Buy the Excel Insights MVP Book: Excel Insights book by Excel MVPs

Cringe-worthy because I realize this obscure fact likely lead to dozens of wrong reports in my career. The problem starts with a question from Derek about why his DSUM formula is not working. It seems to be a bug, but other Excel MVPs say it is working as designed. Then @ExcelisFun makes it even worse by telling me that Advanced Filter has the same design! Watch how both DSUM and Advanced Filter both seemingly return wrong results. Then, a workaround to get exact match in the criteria range. Finally, a plug for the new Excel MVP book available at Excel Insights book by Excel MVP

Rick from New Jersey asks if it is possible to use the new FILTER function to do a Top Five filter. This is one where it sounded easy, but as we dove into what was really needed, it became more complex. Stick around to the 4 minute mark where I am forced back into Ctrl+Shift+Enter land.

This is my entry in the Excel 2019 Excel Hash Competition. Vote for your favorite video here: Microsoft Forms Watch the other entries here: Excel Hash 2019 Covered in this video: Why does the XOR function return "wrong" results with more than two inputs. Comparing XOR to ISODD How to use icons in Excel Linked Picture with a conditional selection of which icon to use.

Historically, using VLOOKUP to look for a value to the left is VLOOKUP's Kryptonite. While VLOOKUP is awesome, there is not an easy way to look to the left. While this episode briefly gives a nod to trickster methods like CHOOSE and INDEX/MATCH, the topic today is how easy it is to XLOOKUP to the left.

Back in episode 2297, Evan asked how to VLOOKUP between two lists where one had DOE, JANE and the other had Jane Doe. This video solves it with Power Query's new Fuzzy Match. Thanks to @XLarium @Daniel Dion and @Bart Titulaer for their suggestions that made this solution twice as quick as before.

A tough question from the MrExcel Message Board. There are 1500 rows showing products in a delimited string. Someone might have had "Cherry, Apple" yesterday and today they have "Apple, Banana, Cherry". Can you use a VBA macro to subtract Cherry Apple from Apple Banana Cherry and end up with just Banana? Today, I end up solving this with Excel Power Query, Split by Delimiter To Rows, and then a Right Anti-Join. The best part: you can refresh the query tomorrow.

We've migrated the 1.4 Million posts in the MrExcel Message Board to a new board software. One thing that is different: the steps to post your spreadsheet to the board. This video will take a look at the free XL2BB Excel add-in that you can use.

Sweeps URL: Microsoft Forms Evan has two lists of names. One is Firstname Lastname and the other is LAST, FIRST. He would like to do a VLOOKUP between these. My solution today involves running both lists through Power Query and then doing a Full Outer Join while merging the two lists.