MrExcel Excel Videos

Back in episode 2359, I needed to add a blank row after every group of names. Although I was already in Power Query, I went out to Excel to add the blank rows. Several people commented with code to add a blank row after each group in Power Query. Thanks to @BillSzysz1 @radosawpoprawskiyourfriend769 @GeertDelmulle Rico S and Suat Ozgur for guiding me on how to add one line of M code to my query. I am at that stage in my Power Query journey that I prefer to do everything in the Power Query editor. Once I switch over to the advanced editor, I try to clean up the step names. And then I add in one line of M code before heading back to the Power Query Editor to finish the task. Table of Contents (0:00) Question: Insert blank row after each...

A question from @chwang06 : Asks how to count Super Bowl Squares by the name in the squares. In this video, I will show how to get a list of players with =UNIQUE(SORT(TOCOL(C3:L12))) and then count how many times each person played using =COUNTIFS(C3:L12,C17#) Table of Contents (0:00) Question: Count Excel Super Bowl Squares by Name (0:35) Make sure your Excel has TOCOL function or use Excel Online (0:55) Excel.New in Excel Online (1:05) Unwinding Excel range to 1 column vector (1:33) SORT and UNIQUE functions (2:00) COUNTIFS with Dynamic Range as second argument (2:30) Many names for # symbol (2:51) Mention video 2385 (3:00) Wrap up with Wally & Nancy

Is there an Excel formula that can return the country name from the telephone number? This video shows you how. To make your life easier, add this worksheet with five named ranges already in it: https://1drv.ms/x/s!As7G72Sl487Jlj1AUGli1Zgx0OgW?e=0Bda0m

After episode 2363, a pair of great questions and a tip. Tip 1: When specifying the Holiday Range in Excel, make the range into a table and then name the range. That way, when you add new holidays at the bottom, the named range will automatically expand. Question 1: How many holidays fall between two dates? Question 2: What is the list of holidays between two dates? This video includes details on NETWORKDAYS.INTL and FILTER functions. You will also see how I take two sub-formulas that were in different cells and combine them into a single formula by scooping the characters out of each formula. Table of Contents (0:00) Welcome (0:17) Binary option for WEEKEND argument in Excel (1:15) Table, Named Range for Holidays (1:37) Named range...

A cool Excel trick from Geert Delmulle - you can increase decimal repeatedly by holding down Alt+1. This works if you move the Increase Decimal icon to one of the first 9 spots on the Quick Access Toolbar. Table of Contents (0:00) Fast Way to Increase Decimal in Excel (0:47) After Alt key, use 1-9 for first 9 QAT icons (1:02) Hold Alt+2 to run 2nd QAT icon repeatedly (2:30) Adding icons to the Excel QAT (2:54) Moving QAT icon to beginning (3:06) Finding icons not on Ribbon for QAT (4:06) Save icon too far left after AutoSave removed

If you need to hide some rows in an Excel pivot table, you can Create a Set Based on Row Items. This is a rarely used feature: First, it is only available if you choose Add This Data To The Data Model. Once you create a set, it is a bit strange to remove rows. And if you add data later, you have to go back in to edit the set. Excel offers to let you edit the MDX, but what is MDX? Thanks to David Jefferson who provided a comment to a YouTube video, he shows how to use .Children to refer to all of the part numbers along a dimension. This is far more flexible. Table of Contents (0:00) What is MDX? (0:37) Pivot table based on Data Model (1:40) Hiding rows using a Set (2:51) MDX Defined (3:28) Editing MDX (4:40) Adding new data & Refresh

Do you love Excel? Do you have a feature that would make Excel easier for you? Write up your idea and post it to the new Feedback Forum for the Excel team. Other people can vote for your idea and many ideas each year are added to Excel. The Feedback portal for Excel is at: Community Vote for my idea to use the Custom Lists when renaming copied worksheets, vote here: Community If you post a great idea at the Feedback forum, let me know the title in a YouTube comment and I will vote for your idea as well. This video shows how to access the Feedback portal and talks about current Excel features that were suggested through the Feedback Portal. Table of Contents (0:00) The Excel team wants your improvement ideas (0:25) Feedback icon...

VLOOKUP, XLOOKUP, and SUMIFS all allow a wildcard in the Lookup_Value. But today, Chris wants wildcards wrapped around the lookup table! After trying XLOOKUP, FILTER, and COUNTIF, I finally end up with TEXTJOIN, IF, and SEARCH. Do you have a better way? Table of Contents (0:00) Use wildcard for the lookup range (0:34) XLOOKUP, FILTER, COUNTIF failed (0:54) SEARCH function on an array (2:11) TEXTJOIN, IFERROR, and SEARCH (3:15) Surprisingly difficult

Markus is looking for Excel help to pick numbers for the Euro Jackpot where you have to choose 5 numbers from 1 to 50. In this video, I share secrets from my late friend Dogtrack Bill. To use the number picker in a free Excel Online, follow this link: https://1drv.ms/x/s!As7G72Sl487JljvjcWPxwg0_pTe1?e=CMGO1E The Euro Jackpot payout for matching 5 numbers with no bonus numbers is a parimutuel system. A fixed percentage of the betting pool is split between all people that matched five numbers. To maximize the payout if you win, choose numbers that are not likely to be chosen by other people. Avoid drawing patterns on the betting card. And, most importantly, stop using dates to choose your lottery numbers. Table of Contents (0:00)...