Category: Power Pivot

Mac Excel Power Query Three Missing Connector Workarounds – Episode 2597

Posted on by Annie D

Great news: Power Query debuted for the Mac in early 2023.
Bad news: It is missing four important connectors:
• From Table/Range – Fixed in this video
• From Web – Fixed in this video
• From Folder – Fixed in this video
• From PDF – no solution yet.

Thanks to Suat Ozgur – our resident Mac expert at MrExcel, we have solutions for three of those connectors.

Table of Contents
(0:00) Problem Statement
(1:17) From Table or Range
(2:44) How to edit query
(3:33) New icons in Applied Steps
(3:59) From Web
(5:17) From Folder
(9:45) No solution for From PDF yet
(10:27) Thanks to Excel Team
(11:15) Subfolders granted access
(11:24) Avoiding VBA

Excel Plot A Bell Curve in Excel – Episode 2596

Posted on by Annie D

Microsoft 365 Excel Tutorial – Plot a Bell Curve with 2 Formulas in Excel.

Download the finished spreadsheet from: https://www.mrexcel.com/youtube/QW3tbhe6Gks/

If you don’t have the SEQUENCE function, use this older video: https://www.youtube.com/watch?v=_PqnDYMO3lw

Search terms:
making a bell curve in excel
making a bell curve in excel from data
how excellent is thy name by lecreia campbell
how to make a bell curve in excel with data
how to find the shaded area of bell curve excel
how to make excel histogram a bell
how to create a bell curve in excel
how to make a bell curve in excel
how to create bell curve in excel
how Continue reading “Excel Plot A Bell Curve in Excel – Episode 2596” →

Excel Stacked And Clustered Chart in Excel – Epsiode 2595

Posted on by Annie D

Microsoft Excel Tutorial – Creating Cluster Stack Chart in Excel.

Jeff from Akron asks how to create a column chart where two series are stacked, but a third series is clustered. Episode 2595 shows you how.

To download the workbook: https://www.mrexcel.com/youtube/c7dPCe2AIEQ/

This video requires Excel 2013 or newer. There is an older video for Excel 2007 or Excel 2010: https://youtu.be/j8kMp5IC6oE

For other variations:
Leila has a video where each column has a Total (say 2000) and a second column showing a subset (say 500). That video is here: https://youtu.be/IwWh3UOTymE

Jon Peltier sells a charting utility where there Continue reading “Excel Stacked And Clustered Chart in Excel – Epsiode 2595” →

Excel VLOOKUP To Return Many Columns Now Easier With XLOOKUP – Episode 2594

Posted on by Annie D

Microsoft Excel Tutorial: Returning many columns from a lookup.
If you do not have the XLOOKUP function, then use the techniques from episode 1123: https://www.youtube.com/watch?v=T_L2XAC0kq8

In the past, if you needed to return 4 quarters or 12 months or 52 weeks from a VLOOKUP, it required some trickery or helper cells. Today, the new XLOOKUP function makes it simple to return many columns from a lookup in Excel.

How to apply vlookup in excel to get multiple columns values.
How to return 12 months from a VLOOKUP in Excel.
How to return multiple columns from a VLOOKUP in Excel.
How to return 52 Continue reading “Excel VLOOKUP To Return Many Columns Now Easier With XLOOKUP – Episode 2594” →

Excel Cell Deleted But Not Logged To Show Changes – Epsiode 2593

Posted on by Annie D

Microsoft Excel Tutorial

Someone deleted some cells in Excel Online.
Using the Version History, it happened between 11:01 and 11:07 AM.
But Show Changes is not showing who changed it?

How could this be happening?
One way is to overwhelm Show Changes by making 3000 changes with VBA.
Another way might be changes by Power Automate Flows.
Do you have another way?

Excel Searching During File Open 2592

Posted on by Annie D

Microsoft Excel Tutorial.
When you go to File, Open, there is a Search box at the top. This search box often surprises me by offering files that are years old. I always wondered how it is working. My friends on the Excel team pointed me to Theo Lorrain-Hale, a Project Manager who worked on the search box.
In this episode, I share tips for where the search box is actually searching.

Excel Add A Calculation To Each Pivot Table Subtotal Row Epsiode 2591

Posted on by Annie D

Microsoft Excel tutorial on pivot tables.
Todd wants to calculate (12/52)*Sales on each subtotal row in a pivot table.
Bill shows how to add a calculated field to a pivot table and then uses some formatting to show the field only on the subtotal rows.

To download the data: https://www.mrexcel.com/youtube/-WqKrzOyCN8/

Table of Contents
(0:00) Formula outside of pivot table
(0:28) Calculated Field in Pivot Table
(1:11) Verify the calculation
(1:30) White Font trick
(1:55) When pivot table changes
(2:35) Wrap-up

Excel – Best Way For Running Totals – Episode 2590

Posted on by Annie D

Microsoft Excel how-to video about Running Totals in Excel.
In one of my Bing Shorts, I showed two different ways to do Running Totals in Excel.
And then, an interesting comment from Carlo in Italy with a running total formula that I’ve never seen before.

To download the data from today, go here: https://www.mrexcel.com/youtube/TnkyqfGGV9g/

I had to bring that formula to Excel to visualize how it was working. It was cool that it used a colon next to INDEX. But is there a simpler way?

In the video, I then compare four different Running Total Formulas. Which are easiest to enter? Which Continue reading “Excel – Best Way For Running Totals – Episode 2590” →

Excel Count By Week – Episode 2589

Posted on by Annie D

Microsoft Excel tutorial on how to count records by week.
This video includes three methods:
Using Group Field in a pivot table to roll up to weeks.
Using a formula of Data minus WEEKNUM(,3) to back the date to Monday.
Using ISOWEEKNUM plus the year along with SORT, UNIQUE, and COUNTIF.

To download the data, visit: https://www.mrexcel.com/youtube/GLkEsvirk9Y/

Table of Contents
(0:00) Count by Week in Excel pivot table
(0:43) Pivot convert dates to weeks
(1:33) Using date minus WEEKDAY(,3)
(2:19) Sort, Unique, COUNTIF
(2:53) Using ISOWEEKNUM in Excel
(3:30) Excel formula for Year and Week Number

Excel Cost Column Is In Cents! Divide All By 100 – #shorts

Posted on by Annie D

Microsoft Excel Tutorial
Can someone please help me???

I have an Excel file that has the cost column in cents so if something is 1.25 it is showing as 125 .

When I change it to currency the numbers change to 125.00….

Anyone knows how to fix the issue faster than one by one. I have over 5,000 items to go through!

In this short video, learn how to multiply an entire column by 0.01 to quickly convert 125 cents to 1 dollar and twenty five cents.

Is Excel DATEDIF Gone from Excel? Quick How-To!

Posted on by Annie D

C. R. asks: Is DATEDIF gone from Excel? It is missing from the latest beta builds. Luckily, it is still there, but you have to know the arguments because Excel won’t provide any guidance.

Let’s see how DATEDIF works, even if the function doesn’t appear to be working in your version of Excel.  DATEDIF is useful for calculating the difference between two dates and can return the measure of time you specify (years, months, days, etc.).

Watch the short video below to see the steps!

This video answers all of these questions related to dated if:
is datedif still in excel
is datedif in excel 2016
why does datedif not work in excel
does datedif still work
can’t find datedif in excel
does datedif still work
datedif how to use
how excel datedif works
how to add datedif function in excel
how to get datedif in excel
how to enable datedif function in excel
what replaced datedif in excel
how to use datedif

For even more info: 

https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c

Excel Enter Random Numbers That Will Not Change Again – #shorts

Posted on by Annie D

In Excel… How does one create a character+number THAT ONLY UPDATES WHEN ENTERED and DOES NOT change with F9 or reloading the spreadsheet?
I want to create sort of an inventory number that I can put on my 3D printer filament boxes to identify them quickly.
But I don’t want the inventory number to ever change after the first creation of the numbers/characters.

This video answers these questions:
Excel random not change?
Excel random keeps changing?
why does random number change in Excel?
Excel random number without changing?
how to stop Excel random number changing?
can i generate random numbers in Excel?
Excel and random number generator?
Excel rand Continue reading “Excel Enter Random Numbers That Will Not Change Again – #shorts” →