MrExcel Excel Videos

Using Excel Dynamic Arrays to generate a list of prime numbers in Excel. Also in this video: Creating a Custom List of numbers. Quickly converting numbers to text using TRIM instead of TEXT.

Take a look at a few examples of the new SEQUENCE function in Excel. Generate a long sequence of numbers quickly. Generate 52 trailing weeks. Generate a forward-looking calendar in Excel with two formulas.

So - you are ready to start including the new Dynamic Array formulas in your VBA project. Except, they never work. Why is Excel wrapping your formulas in an at @ sign? Argh. It is for backwards compatibility. Today, the insider tip on how to right forwards-compatible code and easily enter array formulas.

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.