Category: Power Pivot

World Cup Group Stage Tiebreaker Odds – 2531

Posted on by Annie D

The World Cup is now in group stage. Each group has four teams. The top two teams advance to the knockout stage. This video explains the 7 tiebreaker steps, and uses Microsoft Excel to analyze the 729 permutations that can happen in any one group. How likely is it that the tiebreaker will need to be used?

Table of Contents

(0:00) World Cup Group Stage Scoring
(1:00) Tiebreaker rules
(1:50) Odds of a tiebreaker
(2:20) Excel Model for one group
(2:50) TEXTSPLIT function
(3:22) Excel Trace Dependents
(3:45) Scoring & Sorting
(4:10) Detecting a Tie
(4:41) Why 729 permutations
(4:53) 28% tiebreaker chance
(5:46) Six draws leads to 12 points
(6:04) 18 points Continue reading “World Cup Group Stage Tiebreaker Odds – 2531” →

Group Dates In Pivot Table When Blanks – 2530

Posted on by Annie D

Lasma wants her pivot table to group daily dates up to months and years. But she has some empty rows and the Group Field is greyed out.

In this video:
Surprise! Microsoft 365 now allows blanks in the date field and you can still group.
Solution 1: Fill blank date fields with 2199 December 31
Solution 2: Add your own Year and Month fields to the data set
And, a warning from Debra Dalgleish at Contextures: if you mix dates and text, the Group Field will still be greyed out.

Table of Contents
(0:00) Pivot Group Dates with Blanks
(0:15) Fix from Excel Team
(1:10) Select empty date cells
(2:09) Continue reading “Group Dates In Pivot Table When Blanks – 2530” →

Excel Max Minus Min In A Pivot Table – 2529

Posted on by Annie D

Kelly wants to display a Delta in a Pivot Table. Unfortunately, Calculated Fields in a Pivot Table look at each individual row, so the MAX(B2)-MIN(B2) will always be zero.
In this video, a DAX Measure solves the problem. But to unlock DAX, you need to choose Add This Data To The Data Model.

Table of Contents
(0:00) Need Max minus Min in pivot table
(0:31) MIN and MAX are easy
(0:51) Calculated Field fails
(1:51) Add to Data Model
(2:51) New Measure
(4:25) Wrap-up

Excel Too Many Fields In Pivot Table Field List To Move Around Easily #shorts

Posted on by Annie D

Colleen calls with a question today. She has a pivot table with too many fields in the Values area. She can’t easily resequence them. Is there a way to see more fields in the Pivot Table Fields pane?

Table of Contents
(0:00) Areas Section Only
(0:12) Undock & Resize Field List
(0:22) Drag line between Fields & Areas
(0:29) Re-dock pivot table fields pane
(0:39) Dragging field headings in pivot table
(0:50) Rearrange by typing

Excel Remove Line At Bottom Of Cell 2527

Posted on by Annie D

K has an line at the bottom of one cell. How can we remove it?
Other questions here:
Why does AutoFit Row Height not work in Excel?
Why can’t I delete this border in Excel?
Deleting drawing objects in Excel?
Borders using Conditional Formatting in Excel?
How to remove page breaks in Excel?
Paste Picture Link in Excel?
Why is there one extra line of whitespace at the bottom of my cell?
How do I manually change row height in Excel?

Thanks to YouTubers: DropMeSort, Finnur Torfi Gunnarsson, Rico S., and Vesa Ruusunen.
Thanks to Debra Dalgleish at Contextures.

Table of Contents
(0:00) Remove the Line at the bottom of a cell
(0:19) Use Continue reading “Excel Remove Line At Bottom Of Cell 2527” →

Excel Analyzing Year Over Year Changes When Drinking Magic Mind – 2526

Posted on by Annie D

My productivity hack: https://www.magicmind.co/mrexcel Use my
code MREXCEL20 for 20% off #magicmind

Today, we take a look at analyzing YouTube stats year over year. Along the way, you will see:
Unpivoting KPI columns in Excel Power Query
Creating a pivot table in Excel
Using the SEQUENCE function
XLOOKUP in Excel
Charting in Zebra BI for Office

#productivitytips #productivityhacks #productivityatwork
#adhdmanagement #mondaymotivation #lifehack #inspiration #lifetips

Table of Contents
(0:00) Welcome
(0:10) Magic Mind Challenge
(0:35) Szilvia, Rob, and Matcha
(0:51) Magic Mind Ingredients
(1:49) How does it taste
(2:35) Analyze YouTube Stats
(3:10) Power Query
(3:40) Unpivot KPI columns
(4:10) Pivot Table
(4:55) SEQUENCE function
(5:40) XLOOKUP function
(6:22) Adjust formula by dragging
(6:41) Slicer Shape
(7:09) Pivot Table Lock Column Continue reading “Excel Analyzing Year Over Year Changes When Drinking Magic Mind – 2526” →

Create Psychedelic Spreadsheet Art With Night Creator Studio (Number 1)

Posted on by Annie D

These new Artificial Intelligence Art Creators are the buzz lately. You can type a few sentences and it will generate art for you. I am going to see if I can manage to generate an artistic Excel spreadsheet.
How to use Night Creator Studio to generate artwork.
Type a description, a style, and artistic influences.
Choose dimensions, aspect ratio.

Easy Variance Charts in Excel With Zebra BI For Office – 2525

Posted on by Annie D

Your Excel charts should always show a variance. Maybe a variance from plan or a variance from last year. A new Excel (and PowerPoint) tool from Zebra BI makes this very easy.

Links from the video:
IBCS Standards: https://www.ibcs.com/ibcs-standards-1-2/
About Zebra BI: https://bit.ly/3D0hipe
The free templates from Zebra BI: https://bit.ly/3zeLiws

Table of Contents
(0:00) Life-changing
(0:29) Excel Chart
(0:58) ICB Standards
(1:29) Some ICBS rules
(2:18) Zebra BI for Office
(3:14) Creating chart in Zebra BI
(3:52) Interpreting Chart
(5:14) Second chart
(6:06) Formatting
(7:01) Free templates
(7:15) Wrap-up

MrExcel Favorite Excel Spreadsheet – Arranging Player Piano Rolls

Posted on by Annie D

A look back to the early days of Tickling Keys, Inc. – when our main product was rock and roll player piano rolls. This is my favorite spreadsheet. What is yours? #ExcelAtEverything

Thanks to video footage from David Shivers at the Salem (Ohio) Historical Society.

Excel Random Date or Random Date and Time – 2524

Posted on by Annie D

How to generate random dates in Excel.
How to generate random date and time in Excel.

Table of Contents
(0:00) Welcome
(0:34) Random Date in Excel
(2:38) Random Time in Excel
(3:01) RandomTime During the Workday in Excel
(3:58) Random Time to nearest quarter hour
(4:45) Random Date /Time in Excel
(5:36) Random Weekday in 2025 in Excel
(6:44) Random Weekday No Holidays in Excel
(7:45) Randomly Choose a Monday or Thursday in Excel
(9:19) Randomly Choose particular weekdays in Excel
(10:35) Wrap-Up