Download Excel File: https://excelisfun.net/files/EMT1783.xlsx Solution File: https://excelisfun.net/files/EMT1783Solution.xlsx
Learn how to combine multiple tables in an Excel Workbook File with two methods: VSTACK Worksheet Array Function and Power Query Excel.CurrentWorkbook Function.
Topics:
1. (00:00) Introduction
2. (01:00) Combine Tables Continue reading “Combine All Tables in Excel Workbook: VSTACK or Excel.CurrentWorkbook Function? EMT 1783”
Download Excel File: https://excelisfun.net/files/EMT1782.xlsx
These two functions were released in March 16, 2022 and are in Beta at that time in Microsoft 365 Excel.
Learn about the 14 new functions in Microsoft 365 Excel.
Topics:
1. (00:00) Introduction
2. (00:31) TEXTSPLIT
3. (02:55) Continue reading “14 New Excel Worksheet Functions That Will Blow Your Mind! Excel Magic Trick 1782”
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.
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”
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”
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”
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”
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”