Category: Excel

Power Query Insert Blank Row After Each Group – 2372

Posted on by Annie D

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

Posted on by Annie D

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” →

Busn 218 – Advanced Excel – Winter 2023 Quarter Introductory Video

Posted on by Annie D

Busn 218, Advanced Excel class taught by Excel MVP Mike Girvin at Highline College.
This is the intro video for matriculating students at Highline College in winter quarter, 2023.
To watch at YouTube here is the playlist:
https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW

Excel Statistical Analysis for Business – Busn 210 – Winter 2023 Quarter Introductory Video

Posted on by Annie D

Busn 210, Excel Statistics for Business class taught by Excel MVP Mike Girvin at Highline College.
This is the intro video for matriculating students at Highline College in winter quarter, 2023.
To watch at YouTube here is the playlist: https://www.youtube.com/playlist?list=PLrRPvpgDmw0m3oqpp1XcPuaxyM4Bpi0dN

Excel Holidays Between Two Dates – 2369

Posted on by Annie D

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” →

My Poem – An Ode to Excel

Posted on by Bb

Written by Magic AI and I

Oh Excel, you’re a sight to behold
Your powers so vast, your secrets untold
Your formulas and charts, they never grow old
Your uses are vast, your features unfold

Your beauty’s unmatched, your power’s divine
Your data so precise, and figures so fine
From pivot tables to macros, you make life sublime
A spreadsheet maven, your use is so prime

Oh Excel, you make the mundane sublime
Your features so strong, all those bold lines
From tracking expenses to uncovering goldmines
You make the mundane task feel more like a sweet rhyme

Your power and ease, it’s hard to describe
Your talents so numerous, they stretch as far as the eye can see
You make the task of data entry so much less of a chore
So, Excel, my dear partner and friend, I thank you for making my work life, oh so much more

Excel Hold Alt+1 To Repeatedly Run 1st QAT Icon Perhaps Increase Decimal 2368

Posted on by Annie D

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

Hide Excel Pivot Table Rows Using Sets And Then Edit The MDX – 2367

Posted on by Annie D

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” →