Category: Excel

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.

Power Query UnPivot to Convert Cross Tabulated Table into Proper Data Set. 365 MECS Class 14

Posted on by Annie D

Download Zipped Folder with All Files: https://excelisfun.net/files/14-M365ExcelClassVideoFiles.zip
This video teaches how to unpivot a cross tabulated table and convert it to a proper table so you can perform 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:25) What is a Cross Tabulated Table and What are Benefits of Converting to Proper Table?
3. (01:14) UnPivot feature in Power Query.
4. (01:52) Table.UnpivotOtherColumns M Code Function
5. (02:21) Sort with Resulting Table
6. (02:32) PivotTable Continue reading “Power Query UnPivot to Convert Cross Tabulated Table into Proper Data Set. 365 MECS Class 14” →

Excel How to Sum Non Contiguous Cells #shorts

Posted on by Annie D

How to use the AutoSum when you need to sum non-contiguous cells in Excel.

This video was created to answer these search terms:
How To Sum Non-Contiguous Cells In Excel?
How To Sum Specific Cells In Excel Formula
How To Count Sum For A Particular Cell In Excell
How To Count Sum Of Certain Cells In Excel
How To Select Cells In Excel For Sum
How To Sum Certain Cells In Excel
How To Sum Different Cells On Excel
How To Sum Different Columns In Excel
How To Sum Different Rows In Excel
How To Sum Select Cells In Excel
How To Sum Two Cells In Excel

The Excel Status Bar Uses Number Format From Active Cell – 2386

Posted on by Annie D

Did I know this? The Excel Status Bar Statistics pick up the number format of the active cell in the grid. This is a useful feature, and not a bug. But it could freak someone out when you get different answers depending on if you select from bottom to top or top to bottom.

Also in this episode: the four zones of Custom Number Formats.

And, the winner(s) of a free Excel PDF.