MrExcel XL: The 40 Greatest Excel Tips of All Time

The 40 essential tips that all Excel users need to know. Crowdsourced by more than 300 contributors who collaborated on choosing the 40 best Excel tips, MrExcel XL provides users with a concise book that can be absorbed in under an hour. Includes a bonus 30 tips, keyboard shortcuts, Excel jokes, Excel cocktails. Anyone who uses Excel will be able to turn to any page and pick up tips that will save them hours of work.

Table of Contents

Part 1 – Bill’s Top 40 Tips
#1 Double-Click the Fill Handle to Copy a Formula
#2 Filter by Selection
#3 The Fill Handle Does Know 1, 2, 3…
#4 Fast Worksheet Copy
#5 Compare Sheets Side by Side with Synchronous Scrolling
#6 Turning Data Sideways with a Formula
#7 Default Settings for All Future Workbooks
#8 Recover Unsaved Workbooks
#9 Perfect One-Click Charts
#10 Paste New Data on a Chart
#11 Sort East, Central, and West Using a Custom List
#12 Sort Left to Right
#13 Sort Subtotals
#14 Easy Year-over-Year Report in a Pivot Table
#15 True Top Five in a Pivot Table
#16 Eliminate Pivot Table Annoyances
#17 Replicate a Pivot Report For Each Rep
#18 Use a Pivot Table to Compare Lists
#19 Custom Chart Labels in Excel 2013
#20 Build Dashboards with Sparklines and Slicers
#21 GETPIVOTDATA Might Not Be Entirely Evil
#22 Eliminate VLOOKUP with the Data Model
#23 Budget Versus Actual via Power Pivot
#24 F4 for Absolute and Repeat Last Command
#25 The Fastest Way to Convert Formulas to Values
#26 See All Formulas at Once
#27 Discover New Functions Using fx
#28 Calculate Nonstandard Work Weeks
#29 Handle Multiple Conditions in IF
#30 Cure Triskaidekaphobia with a Killer Formula
#31 Troubleshooting VLOOKUP
#32 Replace Nested IFs with a Lookup Table
#33 Speed Up VLOOKUP
#34 Protect All Formula Cells
#35 Back into an Answer Using Goal Seek
#36 Do 60 What-If Analyses with a data table
#37 Find Optimal Solutions with Solver
#38 Load a File List into Excel with Power Query
#39 Pivot Table on a Map Using 3D Maps
#40 Avoid Whiplash with Speak Cells
Part 2 – 40 Keyboard Shortcuts
Part 3 – Excel 30 Anniversary Tips
#1 Wingdings and Other Unsung Font Heroes
#2 In-Cell Charting with REPT()
#3 AutoSum on a Matrix
#4 Retrieve the Intersection Value – From Space!
#5 How to Lose Your VBA-V (VBA-Virginity)
#6 Recording Your Very First Macro
#7 Turn Your QAT into Your Personal Laugh Track
#8 VBA For Troublemakers
#9 Absolute, Relative, and Multiplication Tables
#10 From Outline to Flat Table
#11 Goal Seek for Renters and Shoe Lovers
#12 Controlled Lists with Data Validation
#13 The XSzil Guide to Excel Protection
#14 Named Formulas: Yesterday and Tomorrow
#15 Named Formulas: VLOOKUP Without the Sausage
#16 Controlled Formula Lists with Data Validation
#17 Dependent Data Validation Lists
#18 Smile for the Excel Camera!
#19 VLOOKUP – to the Left!
#20 Data Shaping (Unpivot) with Power Query
#21 XSzil Design Tips for Excel for Presenters and Trainers
#22 Fill in a Flash
#23 Suppress Errors with IFERROR
#24 Plural Conditions with SUMIFS
#25 Formatting as Façade
#26 Extract Uniques with a CSE Formula
#27 Consolidate Quarterly Worksheets
#28 A2:INDEX() Is Like a Non-Volatile OFFSET
#29 Sort and Filter by Color or Icon
#30 Word for Excellers
#31 40 Tips for the next MXL Book
The Excel Lover’s Guide To Cocktails
Part 4 – Excel Fun