Michele has 46000 rows of data. Each ID has several records in a row. Many records are marked as “Changed”. When there is a change, Michele needs to check the record marked, plus the matching IDs just above and below.
In this video, I try Find All and 2 Advanced Filters. I bet you have something better.
A cool new feature from the MVP Summit: I previously mentioned how Show Changes debuted 9 months ago. Somewhere in the last nine months, the obscure Version History feature (found under File, Info) has gotten much better.
Save your document Continue reading “Excel Version History Improvements Thanks To Show Changes – 2479”
Way back in episode 2326, I spent 8+ minutes showing how to use LET to get the second to last word from a cell. Today, it still requires TEXTBEFORE, TEXTAFTER, and TRIM but can be done in 45 seconds.
Why does the new TEXTSPLIT function in Excel return #N/A errors at the right side? When does a double space create a blank cell? Solve both problems using optional arguments for Ignore_empty and Pad_With.
You have a bunch of sentences in Excel.
How do you find the last word in each cell using a formula?
The new TEXTAFTER function solves the problem.
How to get the Unique list of items appearing in a rectangular range. This solution uses the new TOCOL function released on March 16, 2022 to Microsoft 365 Office Insiders.
Today, a deeper dive into the new Excel functions TOCOL and TOROW.
Table of Contents
(0:00) Ignore errors with TOCOL
(0:46) Non-contiguous ranges with TOCOL
(0:56) 3-D Ranges with TOCOL
(1:26) Using TAKE and HSTACK
(2:01) VSTACK for Headings
Fourteen new functions in Excel: TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, HSTACK, CHOOSECOLS, CHOOSEROWS, DROP, TAKE, EXPAND, TOCOL, TOROW, WRAPCOLS, WRAPROWS.
Links to Help topic for each:
TEXTSPLIT: https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7
TEXTBEFORE: https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29
TEXTAFTER: https://support.microsoft.com/en-us/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4
VSTACK: https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c?ns=xlwaenduser&version=16&ui=en-us&rs=en-us&ad=us
HSTACK: Continue reading “Excel 14 Amazing New Functions in Excel – 2469”
Antony in Toronto brings us two more Excel AutoSum Oddities.
This was part of my hour-long session on Excel Oddities, Curiousities and Mysterious Wonders for the Vancouver Modern Excel Users Group. Watch the entire hour here:
https://youtu.be/H7rSetB7IRo?t=277
Doug from Boston needs to calculate an age in Excel.
Two easy formulas today: YEARFRAC and DATEDIF.
Today’s question from Ken: I’ve lost a slicer. I know it is somewhere on the worksheet, but how can I find it?
Thanks to Lou Pham, we have an answer. Use Home, Find & Select, Selection Pane. Select the object. Press Continue reading “F2 To Bring Selected Object Back Into View in Excel – 2471”
Gagan has sales for three years. For each month in those three years, what is the first date that had a sale? For some reason, Gagan needs to do this without a helper column.
My first solution is a pivot table Continue reading “Excel – Find The Date Of The First Sale In Each Month – 2470”