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.
Category: Excel
Excel UNIQUE Rectangular Range Using TOCOL – 2475
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.
HSTACK and VSTACK Functions: Create Cross Tabulated Report With Total Rows. Excel Magic Trick 1781
Download Excel File: https://excelisfun.net/files/EMT1781.xlsm
These two functions were released in March 16, 2022 and are in Beta at that time.
Learn about the new HSTACK and VSTACK functions to Create Single Cell Cross Tabulated Report with Total Rows.
Topics:
1. (00:00) Continue reading “HSTACK and VSTACK Functions: Create Cross Tabulated Report With Total Rows. Excel Magic Trick 1781”
Excel TOCOL Handles 3D References And Ignores Errors – 2474
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
Excel 14 Amazing New Functions in Excel – 2469
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”
HSTACK and VSTACK Functions to Create Single Cell Reports With Total Row. Excel Magic Trick 1780
Download Excel File: https://excelisfun.net/files/EMT1780.xlsm
These two functions were released in March 16, 2022 and are in Beta at that time.
Learn about the new HSTACK and VSTACK functions to Create Single Cell Reports With Total Row.
Topics:
1. (00:00) Introduction
2. (00:10) Continue reading “HSTACK and VSTACK Functions to Create Single Cell Reports With Total Row. Excel Magic Trick 1780”
3 Unbelievable Excel Reporting Tricks for Monthly Employee Sales By Department. EMT 1779
Download Excel File: https://excelisfun.net/files/EMT1779.xlsx Solution file here: https://excelisfun.net/files/EMT1779Solution.xlsx
Learn how to create a Monthly Employee Sales By Department report three different ways. Each with their own spectacular use.
Three methods for building report:
1. (00:00) Introduction
2. Continue reading “3 Unbelievable Excel Reporting Tricks for Monthly Employee Sales By Department. EMT 1779”
Excel Statistical Analysis 25: Import Excel data: Build PivotTable Discrete Probability Distribution
Download Excel File: https://excelisfun.net/files/Ch05-ESA.xlsm Download data import file: https://excelisfun.net/files/MonclaireGolf.xlsx
Learn about how to import Excel Data with Power Query and load the table directly to the PivotTable, then build a Discrete Probability Distribution and Continue reading “Excel Statistical Analysis 25: Import Excel data: Build PivotTable Discrete Probability Distribution”
Excel Statistical Analysis 24: Build Discrete Probability Distributions with Worksheet Formulas
Download Excel File: https://excelisfun.net/files/Ch05-ESA.xlsm
Learn how to build Discrete Probability Distributions, Expected Value and Standard Deviation with Worksheet Formulas.
Topics:
1. (00:00) Introduction
2. (00:20) Look at Data Sets from Video #6 and PivotTable Method to create Probability Distributions
3. (00:46) Example Continue reading “Excel Statistical Analysis 24: Build Discrete Probability Distributions with Worksheet Formulas”
Excel Statistical Analysis 23: Discrete Probability Distributions: Terms and Definitions
Download Excel File: https://excelisfun.net/files/Ch05-ESA.xlsm PDF notes file: https://excelisfun.net/files/Ch05-ESA.pdf
Learn about:
Topics:
1. (00:00) Introduction.
2. (00:40) Define: Random Variable.
3. (01:40) Define: Discrete and Continuous Random Variables.
4. (03:55) Define: Probability Distribution and Discrete Probability Distribution.
5. (04:36) Three ways to Continue reading “Excel Statistical Analysis 23: Discrete Probability Distributions: Terms and Definitions”
Power Query: Extract Records For Invoices 1 to 30 Days Past Due – EMT 1778
Download Excel File: https://excelisfun.net/files/EMT1776.xlsx
Learn extract invoice that are past due 1 to 30 days using Power Query..
Learn how to.
1. (00:00) Introduction
2. (00:26) Bring Defined Name Parameter into Power Query with number of days.
3. (01:15) In Power Query, Continue reading “Power Query: Extract Records For Invoices 1 to 30 Days Past Due – EMT 1778”
COUNTIFS & SUMIFS functions: When you CAN and CANNOT Spill Results. LAMBDA too. EMT 1777
Download Excel File: https://excelisfun.net/files/EMT1777.xlsx
Learn about the situations where you can spill results from SUMIFS, COUNTIFS, MINIFS, MAXIFS and AVERGAEIFS.
Learn how to.
1. (00:00) Introduction
2. (00:50) COUNTIFS function example that CANNOT Spill
3. (01:44) COUNTIFS function example that CAN Spill
4. Continue reading “COUNTIFS & SUMIFS functions: When you CAN and CANNOT Spill Results. LAMBDA too. EMT 1777”