YouTube viewer asks: How to generate numbers 1 to 100000?
There are many ways to do this in Excel. I will show you two methods in this short video.
Category: Power Pivot
Power Query Insert Blank Row After Each Group – 2372
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 Continue reading “Power Query Insert Blank Row After Each Group – 2372”
Excel Count Super Bowl Squares By Name – 2371
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 Continue reading “Excel Count Super Bowl Squares By Name – 2371”
New Excel Feature Annoying Half Of All Excellers #shorts
Why is there a thick dark box around the formula bar in Excel?
Or, have you never noticed it?
It is part of the F6 Loop.
Excel Formula To Extract Country From Phone Number – 2370
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
Excel Holidays Between Two Dates – 2369
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 Continue reading “Excel Holidays Between Two Dates – 2369”
Excel How To Create A PDF Of Just One Page – #shorts
Mary Ellen asks: It is easy to print one page of an Excel print range. But how do you send just one page to a PDF? The answer is there, but hidden.
Excel Hold Alt+1 To Repeatedly Run 1st QAT Icon Perhaps Increase Decimal 2368
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
Excel You CAN Change The Font Size In The Formula Bar – Shorts
Why is the text in the formula bar so small? If you are on a Zoom call or doing a presentation, it is impossible for the audience to see. There is an amazing trick for making the text in the Excel formula bar HUGE. Try this out.
Hide Excel Pivot Table Rows Using Sets And Then Edit The MDX – 2367
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 Continue reading “Hide Excel Pivot Table Rows Using Sets And Then Edit The MDX – 2367”
Excel Suggest The Next Awesome Excel Idea – 2366
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: https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472
Vote for my idea to use the Custom Lists when renaming copied worksheets, vote here: https://feedbackportal.microsoft.com/feedback/idea/13963988-a0a0-ed11-a81b-000d3a0450e3
If you post a great idea at the Feedback forum, let me know the title in a YouTube comment and I will vote Continue reading “Excel Suggest The Next Awesome Excel Idea – 2366”
Excel Reverse Wildcard Search – 2365
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