Today, October 17, 2022 is International Spreadsheet Day. Microsoft has asked a lot of creators to post their favorite Excel tips. Here is mine: Double-click the Fill Handle to copy a formula to the bottom of the data.
Category: Power Pivot
Power Query Split To Rows For 2 Similar Columns – 2520
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”
Excel Cost Column Is In Cents! Divide All By 100 – #shorts
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
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”
Excel Unique Across Each Individual Row – 2519
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
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
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
Excel Habit Tracker Count Current Streak – 2517
How to calculate the current streak in a habit tracker.
Amreen is building a habit tracker in Excel. For each goal, he wants to report the current streak.
I show how to use XMATCH to solve this problem.
Table of Contents()
(0:00) The goal
(0:40) XMATCH formula for last 1
(1:08) Last zero
(1:29) No streak?
(1:45) How long is streak?
(2:30) Episode 735 for longest streak
(3:05) Converting to checkmarks
(4:32) Colors in Excel number format
Excel VBA User Defined Function To Generate A Dynamic Array – 2516
Can Excel VBA return a dynamic array from a user defined function to the grid?
Can you repeat each cell in column A based on the number in column B?
Check out the amazing answers from Smozgur and XLLambda here:
https://www.mrexcel.com/board/threads/repeatbynumber.1216836/
Table of Contents
(0:00) Two titles
(1:11) Using VBA
(2:05) How many cells to return
(2:50) Building the array
(3:22) Return array to grid
(4:00) Using Hash array operator
Excel Insert Blank Row After Every Two Data Rows #Shorts
LC has 4000 rows of Excel data. After every two rows, he wants to insert a blank row. How can he quickly insert 2000 blank rows in the data? My solution today involves a formula to create alternating cells with text and numbers. Then Home, Find & Select, Go To Special. Select all Formula cells that result in Text. This selects every other row. You can then Insert Sheet Rows and you are done in less than 60 seconds.
Excel FILTER To 2 conditions with LEFT #Shorts
Keith wants to use the Excel FILTER function to get all names where two conditions are true: Status is A and the Project does not start with 22-05. This video shows how to combine 2 conditions in an Excel FILTER formula.
excel filter function
why excel filter not working
excel can you filter columns
excel can you filter with LEFT
excel can you filter to two conditions
Excel Dynamic Charts Based On Dynamic Arrays – 2515
Brand new in Excel Insiders Beta:
A chart can be based on an entire dynamic array in Excel.
In this first iteration, the chart has to point to the entire array. If you have columns in your array that you don’t want to chart, you should use =CHOOSECOLS to get a smaller subset of the array for charting.