Category: Excel

Excel Formula to make First a 1, Second a 2, Third a 3, and so on. 6 Formula Examples. EMT 1797

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1795-1797.xlsx
Buy excelisfun new book at Mr Excel Web Site: https://excel.bookstore.ipgbook.com/microsoft-365-excel–the-only-app-that-matters-products-9781615470709.php
Buy excelisfun new book at Amazon: https://www.amazon.com/Microsoft-365-Excel-Calculations-Analytics/dp/1615470700
Learn how to convert words such as First, Second and Thirds, to numbers such as 1, 2 and 3..
Topics:
1. (00:00) Introduction
2. (00:20) XLOOKUP solution
3. (01:09) XMATCH solution
4. (01:48) XMATCH Spilled Array Formula solution
5. (02:31) VLOOKUP Old School solution
6. (02:59) MATCH Old School solution
7. (03:31) Summary
8. (03:41) Closing, Video Links

Excel Filter By Month: Filter feature or FILTER function? Amazing Dynamic Formula Solution. EMT 1796

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1795-1797.xlsx
Buy excelisfun new book at Mr Excel Web Site: https://excel.bookstore.ipgbook.com/microsoft-365-excel–the-only-app-that-matters-products-9781615470709.php
Buy excelisfun new book at Amazon: https://www.amazon.com/Microsoft-365-Excel-Calculations-Analytics/dp/1615470700
Learn how to filter a data set by month using the Filter feature and the FILTER function. See how to create dynamic Data Valication also..
Topics:
1. (00:00) Introduction
2. (00:17) Filter Feature for a data set with just one year of data
3. (01:20) Filter Feature for a data set with multiple years in in data set
4. (02:00) Create Dynamic Data Valication Dropdown List for Year and Month See the functions: SORT, TEXT, UNIQUE
5. (05:16) Continue reading “Excel Filter By Month: Filter feature or FILTER function? Amazing Dynamic Formula Solution. EMT 1796” →

Excel Text To Columns or Split to Rows: Worksheet Formula or Power Query? Excel Magic Trick 1795

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1795-1797.xlsx
Buy excelisfun new book at Mr Excel Web Site: https://excel.bookstore.ipgbook.com/microsoft-365-excel–the-only-app-that-matters-products-9781615470709.php
Buy excelisfun new book at Amazon: https://www.amazon.com/Microsoft-365-Excel-Calculations-Analytics/dp/1615470700

Learn how to use an Excel formula (TEXTSPLIT or FILTERXML functions) to split text from a cell based on delimiter to a vertical Column. This is Like Text To Columns.

Topics:
1. (00:00) Introduction
2. (00:18) TEXTSPLIT function
3. (01:28) FILTERXML function
4. (01:49) Power Query
5. (02:38) Summary
6. (02:43) Closing, Video Links

MrExcel’s Favorite Excel Tricks & Tips – 2022

Posted on by Annie D

Special ESPN Pricing:
https://www.mrexcel.com/news/special-ocho-deals-on-training-books-to-celebrate-excel-being-on-espn/

The Excel All Star Battle will air on ESPN2 at:
Friday August 5, 2022 at 5 AM Eastern. (2 AM Pacific. 10 AM in London.)
Sunday August 7, 2022 at 9AM Eastern. (6 AM Pacific, Noon in London.)
Monday August 8 (8/8!), 2022 at 11:30 PM Eastern. (8:30 PM Pacific – Prime Time, Baby! And 4:30 AM Tuesday in London).

Bill’s favorite Excel trick: Fast Formula Copy, as well as a lot of other items.

Table of Contents
(0:00) Historical re-enactment
(0:20) Data description
(1:00) Begins typing
(1:38) Adding text is #VALUE error
(1:57) & to Concatenate
(2:10) FORMULATEXT function
(2:36) Adding a space while joining Continue reading “MrExcel’s Favorite Excel Tricks & Tips – 2022” →

Generate Excel Formulas Using Artificial Intelligence – 2496

Posted on by Annie D

ExcelFormulaBot.com is a free A.I. bot that generates the Excel formula for any problem. You type a sentence. It suggests a formula. Free from David Bressler.

Table of Contents
(0:00) This is cool
(0:36) Formula for last word
(1:15) Formula for Prime Numbers
(2:23) Excel All Star Battle on ESPN8 The Ocho

Learn Excel From Mr Excel: SmartRoster Doesn’t Work, Microsoft Blocks Macros

Posted on by Annie D

Episode 2491 – While using SmartRoster software, you try to export a report to Word. The export does not start and you get a message that says, “Microsoft Word Security Notice. Microsoft Office has identified a potential security concern. Microsoft has blocked macros from running because the source of this file is untrusted.”

I recently posted episode 2485 that attempted to allow macros by using the Unblock checkbox in the workbook properties. That method won’t work here. Instead, you can temporarily set up a trusted location. This video shows you how.

Table of Contents
(0:00) Microsoft is blocking macros from running.
(0:53) Continue reading “Learn Excel From Mr Excel: SmartRoster Doesn’t Work, Microsoft Blocks Macros” →

M 365 Excel Worksheet Formulas & Models – Everything You Ever Wanted To Know – 365 MECS 03

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/03-M365ExcelClass.xlsx Download PDF File: https://excelisfun.net/files/03-M365ExcelClass.pdf
Topics in video:
1. (00:00) Introduction
2. (01:12) List of Formula Elements
3. (01:54) Discuss Different Formula Types
4. (05:16) Examples of Modes of Formula Editing
5. (07:35) Examples of the five Calculation Formula Types
6. (08:43) Type #1: Relative and Absolute Cell References in Single Input-Output Formula
7. (12:40) When you must use ROUND function
8. (15:34) Use General Number Format as eraser to remove Number Formatting
9. (16:56) Type #2: Dynamic Spilled Array Formulas
10. (19:35) Spilled Range Operator for Dynamic Spilled Array Formulas
11. (20:07) Type #3: Excel Table Formulas
12. (25:47) Summary about SIOF, DSAF Continue reading “M 365 Excel Worksheet Formulas & Models – Everything You Ever Wanted To Know – 365 MECS 03” →

Prevent Excel From Changing Numbers On Import – 2494

Posted on by Annie D

I love this feature so much, I turned it off!
For decades, Excel has been performing default data conversions:
Remove leading zeroes
Keep first 15 digits of long numbers and convert to scientific notation
Convert digits surrounding an E to scientific notation.

New in Insiders beta, Excel will ask if you want to do all three of those things. The first time, I was happy to click Don’t Convert.
But I think the real solution is to embrace all three options and turn all three of them off! They have been annoying us for so long. Why would you want Microsoft to ask, “Hey, should we Continue reading “Prevent Excel From Changing Numbers On Import – 2494” →

Excel Formulas & Functions, PivotTables, Slicers & Charts – M365 Excel Video 2

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/02-M365ExcelClass.xlsx Download PDF File: https://excelisfun.net/files/02-M365ExcelClass.pdf
Comprehensive introduction to what Excel can do with Worksheet Formulas & Functions, PivotTables, Slicers, Charts and Excel Tables. This is M365 Excel Video 2.
Topics in video:
1. (00:00) Introduction
2. (00:56) Calculations in Excel
3. (02:25) Worksheet formulas definition and elements
4. (03:00) Example of Number Formula
5. (05:07) ROUND function
6. (09:18) How Number Formatting Interacts with Numbers and Formulas
7. (13:22) Example of Text Formula
8. (14:01) TEXTBEFORE function
9. (18:02) FORMULATEXT function
10. (19:02) IFNA function
11. (19:37) Show Nothing with a Formula using Zero Length Text String “”
12. (20:44) Example of Logical Formula using Continue reading “Excel Formulas & Functions, PivotTables, Slicers & Charts – M365 Excel Video 2” →

Validation BUG & More on Excel Shapes & VBA

Posted on by Annie D

Episode 2493
Why is the drop-down arrow not showing up when I create the Excel validation with VBA? It appears after I click Save. But I don’t want to save since these numbers are a temporary scenario and I plan on closing without saving.
Also: Using the Selection Pane in Excel to see all shape names
How Excel names shapes.
Why Excel renames Shapes during workbook copy
How to use VBA to detect which shape was selected.

Table of Contents
(0:00) Bug with Shapes in VBA
(0:31) Excel Validation drop-down arrow missing
(3:05) VBA code to delete shapes
(4:07) How Excel Names Shapes
(4:44) Selection Pane in Excel
(5:47) First Validation arrow Continue reading “Validation BUG & More on Excel Shapes & VBA” →

Excel Formula to Lookup Cell Address Of Max Value: 12 Amazing Examples. Excel Magic Trick 1794

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1794.xlsx
Learn how to lookup cell address to maximum value with 12 different formulas.
Topics:
1. (00:00) Introduction
2. (00:15) Conditional Formatting for Biggest Value.
3. (00:36) Formula #1 with the functions: MAX, ADDRESS, XMATCH, ROW and COLUMN.
4. (01:56) Formula #2 with the functions: MAX, ADDRESS, XMATCH and COLUMN.
5. (02:24) Formula #3 with the functions: XLOOKUP, MAX and CELL.
6. (03:34) Formula #4 with the functions: LET, XLOOKUP, MAX, ADDRESS, ROW and COLUMN.
7. (03:56) Formula #5 with the functions: LET, INDEX, XMATCH, MAX, ADDRESS, ROW and COLUMN.
8. (04:06) Formulas #6-10 with the functions: Old School Formulas that will work Continue reading “Excel Formula to Lookup Cell Address Of Max Value: 12 Amazing Examples. Excel Magic Trick 1794” →

How To Access Excel F Keys on Laptop Computer – Huge List of Function Keys too – 365 MECS 01 Part 2

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/01-M365ExcelClass.xlsx Download PDF File: https://excelisfun.net/files/01-M365ExcelClass.pdf
Learn how to access the F Keys (Function Keys) on a laptop computer. This tip comes from Radoslaw at YouTube. Also see a list of useful F Keys in Excel.
Topics in video:
1. (00:00) MECS Song
2. (00:08) Introduction
3. (00:24) Radoslaw gives helpful tip
4. (00:43) Keyboard toggle to activate or deavtivate F Keys on Laptop: Fx + Esc or Fn + Esc
5. (01:3) List of Functions Keys (F Keys)
6. (01:44) Summary and Closing

Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. Continue reading “How To Access Excel F Keys on Laptop Computer – Huge List of Function Keys too – 365 MECS 01 Part 2” →