Category: Excel

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” →

Introduction to Microsoft 365 Excel & Conventions for Class – 365 MECS 01

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
Topics in video:
1. (00:00) Introduction
2. (00:14) Intro Song
3. (00:22) Download Files and Create Class Folder
4. (01:45) Change File Extensions in Windows Explorer
5. (02:30) Color Coding Conventions for Class
6. (04:46) Rows, Columns, Cells, Worksheets, Sheet Tabs, Workbooks
7. (06:17) Naming Convention for all objects in class
8. (07:38) Tabs in the Excel Ribbon
9. (08:26) Quick Access Toolbar = QAT. Build Custom QAT
10. (11:04) Navigating through Workbook
11. (12:26) Keyboards: Desktop or Laptop?
12. (13:36) Keyboard Shortcuts
13. (16:41) First Formula: SUM function using a keyboard
14. (18:27) Cursors
15. (18:37) Data & Alignment and how Continue reading “Introduction to Microsoft 365 Excel & Conventions for Class – 365 MECS 01” →

Excel Improvements To TEXTBEFORE

Posted on by Annie D

Episode 2492
The Calc team at Microsoft released three changes to new functions in late May 2022. The improvements to TEXTBEFORE and TEXTAFTER give you options for dealing with situations where the delimiter that you are searching for is not found.

Table of Contents
(0:00) Former problem with TEXTBEFORE
(0:37) New syntax
(1:11) Using Match End
(1:31) Using If Not Found
(1:47) Why both?
(2:12) Thanks Excel Team
(2:27) Nancy Faust
(2:34) Like & Subscribe
(2:41) Excel Video Courses

Microsoft 365 Excel Complete Class: free from excelisfun at YouTube – 365 MECS 00

Posted on by Annie D

This is a full and complete class about Microsoft 365 Excel and Power BI Desktop taught by Mike “excelisfun” Girvin. Link to class: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
Mike is an Microsoft Excel MVP and a professor at Highline College. This class will cover topics such as:
1. Introduction
2. Worksheet Formula Models
3. Data Analysis with PivotTable, Power Query, Data Model, Power BI…
4. Date and Time formulas
5. SUMIFS, COUNTIFS & Conditional Calculations
6. Logical Formulas: Comparative Operators, IF, AND, OR and IS functions
7. Lookup functions: XLOOKUP and FILTER, Data Validation
8. Array Formulas
9. LET and LAMBDA functions
10. Formulas to Build Reports
11. Visualizing Data with Conditional Formatting, Continue reading “Microsoft 365 Excel Complete Class: free from excelisfun at YouTube – 365 MECS 00” →