MrExcel 2022: Boosting Excel, 6th Edition

Length: 298 pages
Edition: 6
Language: English
Publisher: Holy Macro! Books
Publication Date: 2022-09-01
ISBN-10: 1615470778
ISBN-13: 9781615470778
Sales Rank: #717730 (See Top 100 Books)

Inspiring Excel tips designed to save 100 hours per year.

Originally designed for Bill Jelen’s live Power Excel seminars, the target audience already uses Excel 40 hours a week. These tips are the “aha” tips that uncover secret methods in Excel. The book covers general Excel functions, pivot tables, and formulas such as VLOOKUP and the new XLOOKUP. It introduces elements of modern Excel such as the Power Pivot Data Model and cleaning data with Power Query. Updated annually, this edition for 2022 adds information on collaboration features, LET and LAMBDA functions, amazing new data types, dynamic array formulas, and more.

Foreword
Sample File Downloads
Double-Click the Fill Handle to Copy a Formula
#1 Break Apart Data
#2 Convert Text Numbers to Numbers Quickly
#3 Filter by Selection
    Bonus Tip:  Filter by Selection for Numbers Over/Under
#4 Total the Visible Rows
#5 The Fill Handle Does Know 1, 2, 3…
    Bonus Tip: Fill Jan, Feb, ..., Dec, Total
#6 Fast Worksheet Copy
    Bonus Tip: Put the Worksheet Name in a Cell
    Bonus Tip: Add a Total Row and a Total Column with One AutoSum
    Bonus Tip: Power Up the Status Bar Statistics
    Bonus Tip: Change All Sheets with Group Mode
    Bonus Tip: Create a SUM That Spears Through All Worksheets
    Bonus Tip: Use INDIRECT for a Different Summary Report
#7 Use Default Settings for All Future Workbooks
    Bonus Tip: Changes to Book Template  are Cumulative
    Bonus Tip: Replace the Comma Style in Book.xltx
#8 Recover Unsaved Workbooks
#9 Search While Using File Open
    Bonus Tip: Pin an Item to the Top of the Recent Files
#10 Show Changes From Last 60 Days
    Bonus Tip: Roll Back to a Previous Version of the Workbook
#11 Simultaneously Edit a Workbook in Microsoft 365
    Bonus Tip: Avoiding the Veto
    Bonus Tip: AutoSave is Necessary, But Turn it Off When Not Co-Authoring
    Bonus Tip: Undo an AutoSave
#12 Save Filter & Sorting in Sheet View
#13 New Threaded Comments Allow Conversations
    Bonus Tip: Old Style Comments Are Available as Notes
    Bonus Tip: Add a Tooltip to a Cell with Validation
    Bonus Tip: Data Validation Partial Matching Added in 2022
#14 Create Perfect One-Click Charts
#15 Paste New Data on a Chart
#16 Create Interactive Charts
#17 Show Two Different Orders of Magnitude on a Chart
#18 Create Waterfall Charts
#19 Create Funnel Charts
#20 Create Filled Map Charts in Microsoft 365
#21 Create a Bell Curve
#22 Plotting Employees on a Bell Curve
#23 Add Meaning to Reports Using Data Visualizations
#24 Use  People to Add Interest to Your Worksheet
    Bonus Tip: Add Text to the Sign Held by a  Person
    Bonus Tip: Use a Formula to Toggle Pictures
#25 Make an Image Semi-Transparent
#26 Save Any Object as an Image
#27 Set Up Your Data for Data Analysis
    Bonus Tip: Use Accounting Underline to Avoid Tiny Blank Columns
    Bonus Tip: Use Alt+Enter to Control Word Wrap
    Bonus Tip: Someone went crazy and used Alt+Enter Too Much
#28 Sort East, Central, and West Using a Custom List
#29 Sort Left to Right
#30 Sort Subtotals
    Bonus Tip: Fill in a Text Field on the Subtotal Rows
    Bonus Tip: An Easier Way to Fill in a Text Field on Subtotal Rows
    Bonus Tip: Format the Subtotal Rows
    Bonus Tip: Copy the Subtotal Rows
#31 Sort and Filter by Color or Icon
#32 Consolidate Quarterly Worksheets
#33 Create Your First Pivot Table
    Bonus Tip: Rearrange fields in a pivot table
    Bonus Tip: Format a Pivot Table
    Bonus Tip: Format One Cell in a Pivot Table
    Bonus Tip: Fill in the Blanks in the Annoying Outline View
    Bonus Tip: Replace Blank Values Cells With Zero
    Bonus Tip: Rearrange Fields Pane
#34 Create a Year-over-Year Report in a Pivot Table
    Bonus Tip: Another Way to Calculate Year-Over-Year
#35 Change the Calculation in a Pivot Table
    Bonus Tip: Why Do Pivot Tables Count Instead of Sum?
#36 Find the True Top Five in a Pivot Table
#37 Specify Defaults for All Future Pivot Tables
    Bonus Tip: Change What Drives You Crazy About Excel
#38 Make Pivot Tables Expandable Using Ctrl+T
    Bonus Tip: Use Ctrl+T with VLOOKUP and Charts
#39 Replicate a Pivot Table for Each Rep
#40 Use a Pivot Table to Compare Lists
    Bonus Tip: Show Up/Down Markers
    Bonus Tip: Compare Two Lists by Using Go To Special
#41 Build Dashboards with Sparklines and Slicers
    Bonus Tip: Line Up Dashboard Sections with Different Column Widths
    Bonus Tip: Use Picture Lookup
    Bonus Tip: Report Slicer Selections in a Title
#42 See Why GETPIVOTDATA Might Not Be Entirely Evil
#43 Ask Excel's A.I. a Question About Your Data
#44 Eliminate VLOOKUP or XLOOKUP with the Data Model
    Bonus Tip: Count Distinct
#45 Compare Budget Versus Actual via Power Pivot
    Bonus Tip: Portable Formulas
    Bonus Tip: Text in the Values of a Pivot Table
#46 Slicers for Pivot Tables From Two Data Sets
#47 Use F4 for Absolute Reference or Repeating Commands
    Bonus Tip: Use a Named Range Instead of Absolute References
#48 Quickly Convert Formulas to Values
    Bonus Tip: Skip Blanks While Pasting
#49 See All Formulas at Once
    Bonus Tip: Highlight All Formula Cells
    Bonus Tip: Trace Precedents to See What Cells Flow into a Formula
    Bonus Tip: See Which Cells Depend on the Current Cell
#50 Audit a Worksheet With Spreadsheet Inquire
#51 Discover New Functions by Using fx 
#52 Use Function Arguments for Nested Functions
#53 Calculate Nonstandard Work Weeks
    Bonus Tip: Use WORKDAY.INTL for a Work Calendar
#54 Turn Data Sideways with a Formula
    Bonus Tip: Protect Rows with an Old-Style Array Formula
#55 Handle Multiple Conditions in IF
    Bonus Tip: Use Boolean Logic
#56 Troubleshoot VLOOKUP
#57 Use a Wildcard in VLOOKUP or XLOOKUP
    Bonus Tip: VLOOKUP to Two Tables
#58 Twelve Benefits of XLOOKUP
#59 Preview What Remove Duplicates Will Remove
#60 Replace Nested IFs with a Lookup Table
    Bonus Tip: Match the Parentheses
#61 Suppress Errors with IFERROR
#62 Handle Plural Conditions with SUMIFS
#63 Geography, Exchange Rate & Stock Data Types in Excel
    Bonus Tip: Use Data, Refresh All to Update Stock Data
#64 Get Historical Stock History from STOCKHISTORY
#65 More Data Types from Wolfram Alpha
    Bonus Tip: The Data Card is now Expandable and Supports Arrays
#66 Getting Historical Weather For a City
#67 Create Your Own Data Types Using Power Query
#68 IF Based on Installed Language in a Bilingual Worksheet
#69 Dynamic Arrays Can Spill
#70 Sorting with a Formula
#71 Filter with a Formula
    Bonus Tip: Understanding Array Constants
#72 Formula for Unique or Distinct
    Bonus Tip: Use # "The Spiller" to Refer to All Array Results
#73 Other Functions Can Now Accept Arrays as Arguments
#74 One Hit Wonders with UNIQUE
#75 SEQUENCE inside of other Functions such as IPMT
#76 Generating Random Numbers in Excel
#77 Replace a Pivot Table with 3 Dynamic Arrays
    Bonus Tip: Replace Ctrl+Shift+Enter with Dynamic Arrays.
#78 Dependent Validation using Dynamic Arrays
#79 Complex Validation Using a Formula
#80 Stack Multiple Arrays
#81 Dropping, Taking, or Choosing from an Array
#82 Reshaping an Array to a Vector and Back
#83 Getting the UNIQUE of a Rectangular Range
#84 Shuffling and Dealing a Deck of Cards
#85 Use A2:INDEX() as a Non-Volatile OFFSET
#86 Subscribe to Microsoft 365 for Monthly Features
#87 Performance Improvements for Microsoft 365
#88 Unhide Multiple Worksheets
#89 Write Your Data with the Action Pen
#90 Many Task Panes Now Collapse into a Tab Strip
#91 How to Provide Usable Feedback to the Excel Team
#92 Date Tricks in Excel
#93 Use the LET Function to Re-Use Variables in a Formula
#94 Store Complex Formula Logic in LAMBDA function
    Bonus: Importing LAMBDAs from GitHub 
    Bonus Tip: Perform a Loop with a Recursive LAMBDA
    Bonus Tip: Branching in a LAMBDA
    Bonus Tip: Using LAMBDA to return a Picture
#95 New LAMBDA Helper Functions
#96 Find Largest Value That Meets One or More Criteria
    Bonus Tip: Concatenate a Range by Using TEXTJOIN
#97 Text Before or After a Specific Delimiter
#98 Split Text into Words Using TEXTSPLIT
#99 Less CSV Nagging and Better AutoComplete
#100 Protect All Formula Cells
#101 Back into an Answer by Using Goal Seek
#102 Do 60 What-If Analyses with a Sensitivity Analysis
    Bonus Tip: Create a Data Table from a Blank Cell
#103 Find Optimal Solutions with Solver
#104 Interpolate between a starting and ending number
#105 Improve Your Macro Recording
    Bonus Tip: Use TypeScript to Write Macros for Excel Online
#106 Clean Data with Power Query
    Bonus Tip: Tame the Sequence of Refresh All
    Bonus Tip: Data Profiling in Power Query
#107 Use Fuzzy Match in Power Query
#108 Build a Pivot Table on a Map Using 3D Maps
#109 The Forecast Sheet Can Handle Some Seasonality
#110 Perform Sentiment Analysis in Excel
#111 Build Org Charts with the Visio Data Visualizer in Excel
#112 Fill in a Flash
#113 Format as a Façade
#114 Show All Open Workbooks in the Windows Taskbar
#115 Paste to Another Computer Using Cloud Clipboard
#116 Surveys & Forms in Excel
#117 Use the Windows Magnifier
#118 New Scrolling Tricks 
#119 Word for Excellers
    Bonus Tip: Merge Shapes
    Bonus Tip: Use the Eye Dropper
#120 Avoid Whiplash with Speak Cells
    Bonus Tip: Provide Feedback with Sound
    Bonus Tip: A Great April Fool’s Day Trick 
#121 Customize the Quick Access Toolbar
    Bonus Tip: Sometimes, You Don't Want the Gallery
    Bonus Tip: Show QAT Below the Ribbon
#122 Create Your Own QAT Routines Using VBA Macros
    Bonus Tip: Settings in the Excel Options Menu
#123 Favorite Keyboard Shortcuts
#124 Ctrl+Click to Unselect Cells
#125 Collapse the Search Box
#126 More Excel Tips
#127 Excel Stories
#128 Excel Function Quick Reference
Dedication
About the Author
About the Illustrators
Index