Category: Business

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

Three Ways To Avoid The Common Split By Delimiters Trap

Posted on by Annie D

Split by Delimiters is a valuable function in Power Query that enables users to split a single column of data into multiple columns based on specific delimiters or separators. This function is especially useful for data cleaning, as it allows users to quickly separate and organize data that may be improperly formatted or combined.

However, if not used correctly, Split by Delimiters can lead to inaccurate results that can undermine the quality of your data. In this video, Brian shares three quick and easy fixes to avoid the common Split by Delimiters trap.

*****Video Details*****
00:00 Introduction
00:47 Data set
01:14 Split rows by Continue reading “Three Ways To Avoid The Common Split By Delimiters Trap” →

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

Finding Similar Matches With Euclidean Distance Using Python In Power BI

Posted on by Annie D

Euclidean distance is a distance measure commonly used in machine learning to find the closest ideal match between two data points. This measure computes the straight-line distance between two points in a high-dimensional space.

With the integration of Python in Power BI, users can leverage Python’s machine learning capabilities, including the calculation of Euclidean distance, to enhance their data analysis. In this tutorial, Gaelim shows how you can find the similar matches between data points with Euclidean distance using Python.

*****Video Details*****
00:00 Introduction
00:36 Data set
00:54 Overview of example
02:49 Calculating the Euclidean distance
03:45 Python script
07:09 Categorical variables

***** Learning Power BI? *****
FREE Continue reading “Finding Similar Matches With Euclidean Distance Using Python In Power BI” →

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 FILTER Function to Extract Records Based on Items in a List. Excel Magic Trick 1820

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1820.xlsx
Learn how to extract records from a table that match ietms from a list using the FILTER, ISNUMBER and XMATCH functions.
Topics:
1. (00:00) Introduction
2. (00:33) XMATCH
3. (02:15) ISNUMBER
4. (02:35) FILTER
5. (03:28) Conclude

#excel #excelisfun #analytics #microsoftexcel #microsoftmvp, #short #shorts #excelmvp #excelformulas #excelfunctions #excelformulaforjobinterview #excelforbegginers #excelshorts #exceltutorial #exceltips #exceltipstricks #freeclass #freecourse #freeclasses #freeexcelcourse #freeexcel #shortsyoutube #shorts #short #xmatch #FILTER #filterfunction #match #list #comparelists #extract #Table

Find Weekday & Weekend Day Numbers Within Months – Advanced DAX [2023 Update]

Posted on by Annie D

Time intelligence involves analyzing data with a focus on time-related factors. Comparing data from different time periods is an important aspect of time intelligence. “Like-for-like” comparisons involve comparing similar or identical time periods. Standard time intelligence functions do not always make “like-for-like” comparisons possible, so advanced logic and techniques may be necessary to achieve this. In this tutorial, Sam will demonstrate how you can find the weekday and weekend day numbers within months using DAX.

Enterprise DNA Forum Post – https://bit.ly/3Hc2wPk

*****Video Details*****
00:00 Introduction
01:16 Comparing like-for-like
01:59 Why time intelligence calculations won’t work
02:52 Weekday number
03:42 Using RANKX
04:40 Day of month
05:56 Continue reading “Find Weekday & Weekend Day Numbers Within Months – Advanced DAX [2023 Update]” →

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?

How To Use The Dynamic Format Strings Feature – Power BI April 2023 Updates

Posted on by Annie D

One of the latest updates for Power BI in April 2023 introduces a new capability called Dynamic Format Strings, which provides an interactive means for users to control the presentation of data in visuals. In this video, Brian demonstrates how you can setup and utilize of this feature.

Although the current example focuses on modifying the format of a singular visual with Dynamic Format Strings, this technique can be readily extended to multiple visuals on a single page or even to the entire suite of visuals spanning several pages by utilizing synch slicers.

*****Video Details*****
00:00 Introduction
01:00 Setting up a helper table
01:49 Continue reading “How To Use The Dynamic Format Strings Feature – Power BI April 2023 Updates” →

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.

How To Calculate Year To Date Sales For Non-Standard Calendar Tables Using DAX

Posted on by Annie D

Using DAX for time intelligence calculations in Power BI provides several advantages, such as the ability to perform complex calculations, create custom date ranges, and dynamically adjust calculations based on user selections.

However, when using custom calendars in Power BI, it is important to ensure that the calendar is set up correctly and that all time intelligence calculations are based on the correct date column. Additionally, it is essential to consider any differences in the length of periods, such as weeks or months, as this can impact the accuracy of time intelligence calculations.

In this tutorial, Sam is going to Continue reading “How To Calculate Year To Date Sales For Non-Standard Calendar Tables Using DAX” →

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