Category: Excel

Using Excel To Calculate Five Number Summary – 2571

Posted on by Annie D

The five-number summary in Excel is Min, first quartile, median, upper quartile and Max. This video shows three ways:
Method 1: Three formulas
Method 2: LET function
Method 3: LAMBDA function

This video answers these Search terms:
does excel already have a 5 number summaryy option
how do you obtain a five number summary in excel
how do you use excel to find 5 number summary
how to create a 5 number summary in excel
how to create a five number summary in excel
how to determine five number summary excel
how to do 5 number summary in excel
how to do a 5 number summary in excel
how to do a five Continue reading “Using Excel To Calculate Five Number Summary – 2571” →

Count Files in Folder Using Excel? Power Query!!! Excel Magic Trick # Short 01

Posted on by Annie D

Learn how to use Power Query in Excel to cynamically count how many files are in a folder and list file names in the worksheet.
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp,

Power Query Group By: Basic to Advanced. 10 Examples. 365 MECS Class 16

Posted on by Annie D

Download Zipped Folder with All Files: https://excelisfun.net/files/16-M365ExcelClassVideoFiles.zip
This video teaches how to use the Group By feature in Power Query. The 10 examples cover many helpful applications of how to use Group By for calculations and Data Analysis.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
This video covers.
1. (00:00) Introduction and video topics
2. (00:32) What does Group By do?
3. (01:07) When to use Group By or other tools such as PivotTables
4. (01:44) Basics of Group By feature
5. (03:41) Table.Group Continue reading “Power Query Group By: Basic to Advanced. 10 Examples. 365 MECS Class 16” →

Excel Random No Repeats – Even Shorter for Microsoft 365 – Episode 2570

Posted on by Annie D

How to generate a random selection in Excel with no repeats.

Choose random names from the Data Set, but do it without repeating a namek. In a revisit to Episode #1471, how will Bill assure that there are no repeats in the Random selection? Follow along with Episode #2570 today to learn the answer and solve the challenge.

This video requires new Excel functions available in Microsoft 365. If you don’t yet have RANDARRAY, SORTBY, or SEQUENCE, then use the old method in video 1471: https://www.youtube.com/watch?v=g8FFgxmE70g

The PowerBall lotto formula shown near the end of the video is:
=TEXTJOIN(“, “,,SORT(TAKE(
SORTBY(SEQUENCE(69),RANDARRAY(69)),5)),”PB=”&RANDBETWEEN(1,26))

Table Continue reading “Excel Random No Repeats – Even Shorter for Microsoft 365 – Episode 2570” →

Excel Random No Repeats – Updated for Microsoft 365 – 2570

Posted on by Annie D

How to generate a random selection in Excel with no repeats.

Choose random names from the Data Set, but do it without repeating a namek. In a revisit to Episode #1471, how will Bill assure that there are no repeats in the Random selection? Follow along with Episode #2570 today to learn the answer and solve the challenge.

This video requires new Excel functions available in Microsoft 365. If you don’t yet have RANDARRAY, SORTBY, or SEQUENCE, then use the old method in video 1471: https://www.youtube.com/watch?v=g8FFgxmE70g

The PowerBall lotto formula shown near the end of the video is:
=TEXTJOIN(“, “,,SORT(INDEX(SORTBY(SEQUENCE(69),RANDARRAY(69)),
SEQUENCE(5))),”PB=”&RANDBETWEEN(1,26))

Table Continue reading “Excel Random No Repeats – Updated for Microsoft 365 – 2570” →

Excel Shortcut To Unhide Columns Not Working – 2569

Posted on by Annie D

One of these four shortcut keys stops working in Windows versions of Excel:
Ctrl+9 to hide rows
Ctrl+Shift+9 to Unhide rows
Ctrl+0 to hide columns
Ctrl+Shift+0 to unhide columns
If the last one is not working for you, it is because the Windows shortcut to switch language is assigned to Ctrl+Shift. This video shows the Control Panel setting to make Ctrl+Shift+0 or Ctrl+Shift+) start unhiding columns again.

7 Types of Joins in Power Query Merge. 9 Examples. 365 MECS Class 15

Posted on by Annie D

Download Zipped Folder with All Files: https://excelisfun.net/files/15-M365ExcelClassVideoFiles.zip
This video teaches how to use the 7 types of Joins when you use Merge in Power Query. Many easy to understand examples.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
This video covers.
1. (00:00) Introduction and video topics
2. (00:19) Defined the 7 types of Merge Joins in Power Query
3. (02:48) Accessing Merge Join in Excel, Power BI Desktop or Dataflows
4. (03:02) Files for Power BI example
5. (03:17) MUST use Queries NOT Excel Continue reading “7 Types of Joins in Power Query Merge. 9 Examples. 365 MECS Class 15” →

Why Is Excel Advanced Filter Using Partial Matching 2388

Posted on by Annie D

Today’s question from Bill.
Did you know that Excel’s Advanced Filter is returning partial matches?
A phrase in the criteria range is treated as Phrase*.
This is an annoying problem.

Excel Help sort of alludes to this problem and suggests a formula in the criteria range of Advanced Filter. But if you try to automate the creation of those formulas in the criteria range, it does not work.

In this video, a convoluted set of steps to automate formula creation with a carat in place of the equals sign, then Replace to change the text into a formula. This allows the Advanced Filter to work.

This Continue reading “Why Is Excel Advanced Filter Using Partial Matching 2388” →

How to SUM Only The Positive Numbers in Excel #shorts

Posted on by Annie D

How to sum only the positive numbers in Excel?
This video shows how to use SUMIF or SUMIFS.

This video matches these search terms:
how to calculate sum of only the positive numbers in excel
how to do a sum of only positive values in excel
how to sum negative and positive total numbers in excel
how to sum positive and negative in excel

Does XLOOKUP Approximate Match Work With Text – 2387

Posted on by Annie D

A question from my Daytona Beach seminar last week:
We know XLOOKUP can find the number just larger or just smaller than a number. But does this also work with Text? How about with Symbols?

In this video, Bill Jelen shows how the approximate match works with XLOOKUP. Unlike MATCH or VLOOKUP, the table does not have to be sorted. You can find a value just smaller or just larger than the search value.

This also works with Text, although it is case insensitive.

And, it works with symbols, but in an unexpected sequence.