Category: Excel

Reggae Pioneer Willie Dickson Plays for Spreadsheet Day

Posted on by Annie D

Jamaican Reggae Legend Willie Dickson of Willie Dickson and the Playboys wishes everyone a happy spreadsheet day and then dazzles us with drum rendition of the Rasta Beat.

Background vocals by Dickson’s Russea’s High School schoolmate Llewelyn Grant.

Special performance for spreadsheet day by a legendary drummer and one of the godfathers of Reggae and a legendary drummer.

Funny story: When I first heard of Willie Dickson, I confused him with Chicago blues legend Willie Dixon who has passed away. Our Willie Dickson tells of meeting Willie Dixon on the road in Montreal one time when they were Continue reading “Reggae Pioneer Willie Dickson Plays for Spreadsheet Day” →

Dad & Son Skatepark Sunday Seattle Rad, Oct. 16, 2022. Boneless-Ones Skate Music.

Posted on by Annie D

It’s been 4 months since I have posted a skatepark BMX and Skateboard video. This was rad fun on Sunday, Oct. 16, 2022 at Delridge Skatepark and Jefferson Skatepark with Isaac “Iceman” Girvin and Mike “excelisfun” ‘Magic” Girvin.
This is the best way to get fuel to make Excel videos and write Excel books during the week : )
The Boneless Ones Skate Punk Band Song Back To The Grind pumps the action and creates rad!
Washington BMX / Skate Adventure 17.

Power Query Split To Rows For 2 Similar Columns – 2520

Posted on by Annie D

How to split delimited into rows.
I tried using Power Query.
With Power Query I can do it only for 1 column.
If I select more than 1 column, the Split Column is disabled in the Power Query Editor.
Is there any other solution for splitting delimited into rows for more than one column?

This video offers three solutions.
Solution 1 is a formula such as =TEXTJOIN(“|”,,TEXTSPLIT(C2,”,”)&”-“&TEXTSPLIT(B2,”,”)) and then Power Query.

The 2nd solution is M code written by Suat Ozgur. Get that code from MrExcel:
https://www.mrexcel.com/board/threads/power-query-split-to-rows-for-2-similar-columns-2520.1219302/

The 3rd solution is two intermediate queries in Power Query and then a merge query.

Table of Contents
(0:00) How Continue reading “Power Query Split To Rows For 2 Similar Columns – 2520” →

Show all Formulas in Single Column – Awesome Model Documentation Trick – Excel Magic Trick 1800

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1800.xlsx
Learn how to show all formulas in a column using a single formula.
Topics:
1. (00:00) Introduction
2. (00:17) Show all formulas in a column using a single formula using TOCOL, ADDRESS, ROW, COLUMN and FORMULATEXT functions
3. (03:30) Summary, Closing, Video Links

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

Posted on by Annie D

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.

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

FILTER by 1 or More Lists in the Excel Worksheet: VSTACK or TOCOL? Excel Magic Trick 1799

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1799.xlsx
Learn how to filter records based on a list of values using Excel worksheet formula.
Topics:
1. (00:00) Introduction
2. (00:26) Filter records based on a list of values using FILTER, XMATCH and ISNUMBER functions.
3. (02:22) VSTACK function to Filter records based on one or more lists from any were on worksheet.
4. (03:19) TOCOL function to Filter records based on one or more lists that are listed sequentially.
5. (04:09) Summary, Closing, Video Links

Excel Unique Across Each Individual Row – 2519

Posted on by Annie D

Excel getting the unique values going across a row.
The Remove Duplicates command in Excel always removes rows. What if you want to remove duplicates that appear in the same row? The UNIQUE function offers a By Column option.
Also in this video: Sorting sideways in Excel using the Left-to-Right Sort option.
What happens if you leave off the optional By_Col argument in UNIQUE.
Why UNIQUE returns the original range.

Table of Contents
(0:00) Sort or Unique sideways in Excel
(0:37) UNIQUE by Column
(1:48) Nested arrays not supported
(2:00) Sorting sideways in Excel
(2:38) UNIQUE of rectangular range
(3:33) Why UNIQUE returns original range
(4:15) BYCOL defaults to False
(4:35) UNIQUE Continue reading “Excel Unique Across Each Individual Row – 2519” →

Excel Why Wont This SUMIFS Work Inside Of Let 2518

Posted on by Annie D

Stevenson asks: Why won’t this SUMIFS work inside of a LET function in Excel?
It turns out that he is trying to do a calculation in the first argument of the SUMIFS.
This is against the rules.
If you would have tried it without the LET, Excel would have given you the mysterious error of “There’s a Problem With This Formula”.
When you try to do it inside of LET, the formula parser is not smart enough to refuse the formula, but you end up with a bunch of #VALUE! errors in Excel.
The same limitation applies to SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, SUMIF, AVERAGEIF, Continue reading “Excel Why Wont This SUMIFS Work Inside Of Let 2518” →

Is Excel DATEDIF Gone from Excel? #shorts

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.

This video answers these questions:
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

Logical Tests: Excel Formulas, Conditional Formatting, PivotTables, Power Query, & More- 365 MECS 07

Posted on by Annie D

Download Excel file: https://excelisfun.net/files/07-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/07-M365ExcelClass.pdf
Download csv file for Power Query example: https://excelisfun.net/files/9112021-2022CallData.csv
Course taught by Excel MVP and Highline College Professor, Mike Girvin. Course is Microsoft 365 Excel Complete Story.
Topics in video:
1. (00:00) Introduction
2. (01:00) What is a Logical Test in Excel?
3. (04:30) Examples of six most common types of logical tests with Filter Feature and PivotTable
4. (04:30) Examples of Single Condition, Contains and NOT Logical Tests
5. (08:44) Description of AND Logical Test and OR Logical Test
6. (17:16) Description of a Complex Logical Test
7. (20:09) Comparative Operators
8. (21:09) Comparative Continue reading “Logical Tests: Excel Formulas, Conditional Formatting, PivotTables, Power Query, & More- 365 MECS 07” →