MrExcel 23 The Greatest Excel Tips of All Time

Excel tips to skyrocket white-collar productivity.

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 2021 adds information on LET and LAMBDA functions, amazing new data types, dynamic array formulas, and more.

Bill Jelen is the host of www.MrExcel.com, a Microsoft MVP, and the author of 68 books about Excel. He lives in Merritt Island, Florida.

1 - Fast Formula Copy 2 - Break Apart Data 3 - Convert text #'s 4 - Filter by Selection 5 - Total Visible Rows 6 - The Fill Handle 1, 2, 3… 7 - Fast Worksheet Copy 8 - Defaults Future WB 9 - Recover Unsaved Workbooks 10 - Search in File, Open 11 - Show Changes 60 Days 12 - Collab 13 - Sheet View 14 - Threaded Comments 15 - Perfect 1-Click Charts 16 - Paste New Data on a Chart 17 - Create Interactive Charts 18 - Chart 2 Orders of Magnitude 19 - Waterfall Charts 20 - Funnel Charts 21 - Map Charts 22 - Bell Curve 23 - Plot People on a Bell Curve 24 - Data Visualizations 25 - Cutout People 26 - Semi-Transparent Image 27 - Save any object as image 28 - List Format 29 - Sort Using a Custom List 30 - Sort Left to Right 31 - Sort Subtotals 32 - Sort & Filter by Color / Icon 33 - Consolidate Qtr Worksheets 34 - Create Your First Pivot Table 35 - PT Year-over-Year 36 - Change PT Calculation 37 - True Top 5 in a PT 38 - Defaults for All Future PT 39 - PT Expandable Using Ctrl+T 40 - Replicate a PT for Each Rep 41 - Use a PT to Compare Lists 42 - Sparklines and Slicers 43 - GETPIVOTDATA use 44 - Ask a Question 45 - Data Model to Eliminate XLOOKUP 46 - Budg v. Act'l via Power Pivot 47 - PT Slicers from 2 Data Sets 48 - F4 for $A$1 or Repeat 49 - Formulas to Values 50 - See All Formulas 51 - Audit With Inquire 52 - New Fx by Using fx 53 - Fx Args Nested Fx 54 - Nonstandard Work Weeks 55 - Sideways with a Formula 56 - Multiple Conditions IF 57 - Troubleshoot VLOOKUP 58 - Wildcard in Lookup 59 - 12 Benefits of XLOOKUP 60 - Preview Remove Duplicates 61 - Nested IFs v. Lookup 62 - Suppress Errors with IFERROR 63 - Plural Conditions w/ SUMIFS 64 - Geo, XE & Stock Data Types 65 - STOCKHISTORY 66 - Wolfram Alpha 67 - Historical Weather for City 68 - Create Data Types using PQ 69 - Which language in Bilingual 70 - Dynamic Arrays Can Spill 71 - Sorting with a Formula 72 - Filter with a Formula 73 - Formula for Unique or Distinct 74 - Fx Can Accept Arrays 75 - 1 Hit Wonders with UNIQUE 76 - SEQUENCE inside IPMT 77 - Random #'s in Excel 78 - PT with 3 DA 79 - Dependent Validation DA 80 - Complex Validation Formula 81 - Stack Arrays 82 - Drop, Take, Choose from Array 83 - Reshaping Arrays 84 - UNIQUE of Rectangular Range 85 - Shuffle & Deal Cards 86 - A2:INDEX() non-Volatile OFFSET 87 - Microsoft 365 features 88 - Perf Improvements 89 - Unhide Multiple Worksheets 90 - Action Pen 91 - Take Panes Collapse 92 - Feedback to the Excel Team 93 - Date Tricks 94 - LET 95 - LAMBDA 96 - Lamda helper functions 97 - MAXIFS 98 - TEXTBEFORE or AFTER 99 - TEXTSPLIT 100 - Less CSV Nagging+Autocomplete 101 - Protect All Formula Cells 102 - Goal Seek 103 - Do 60 What-If Analyses 104 - Solver 105 - Interpolate 106 - Improve Macro Recording 107 - Clean Data Power Query 108 - Fuzzy Match in PQ 109 - PT on a Map 3D Maps 110 - Forecast Seasonality 111 - Sentiment Analysis 112 - Visio Flowcharts 113 - Fill in a Flash 114 - Format as a Faç ade 115 - Taskbar all open workbooks 116 - Paste to Other Computer 117 - Surveys & Forms in Excel 118 - Windows Magnifier 119 - Scrolling Tricks 120 - Word for Excellers 121 - Speak Cells 122 - Customize QAT 123 - QAT VBA Macros 124 - Fav Keyb Shortcuts 125 - Ctrl+Click to Unselect 126 - Collapse the Search Box 127 - More Excel Tips 128 - Excel Stories 129 - Quick Fx Reference