MrExcel Publishing
Your One Stop for Excel Tips & Solutions

MrExcel 2020 - Seeing Excel Clearly


January 2020 - by Bill Jelen

MrExcel 2020 - Seeing Excel Clearly

The 4th Edition of MrExcel XL, update with XLOOKUP and other new Office 365 features.

Bill Jelen, MrExcel

category: Excel
covers: Office 365

Product Details
  • Publisher: Holy Macro! Books
  • eBook ISBN: 978-1-61547-251-2

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.

If you own the earlier MrExcel LIVe, more updates include: Geography Data Types, Stock Quotes, Artificial Intelligence, and the new Dynamic Arrays such as SORT, SORTBY, FILTER, and UNIQUE.

This year’s edition will only be issued as an e-book. My plan is to update the e-book two more times during 2020 as significant new features are released. When you buy the e-book from MrExcel.com, you will be able to download these updates.

If you purchased MrExcel LX from the MrExcel store (item 360, 362, or 373), you already have the PDF of MrExcel 2020 added to your account for free. If you love physical books, purchase the print edition of MrExcel LX from the MrExcel store and you can download the 2020 PDF as well.

  1. Ask Excel a Question About Your Data
  2. Double-Click the Fill Handle to Copy a Formula
  3. Break Apart Data
  4. Filter by Selection

    • Bonus Tip: Filter by Selection for Numbers Over/Under
  5. Bonus Tip: Total the Visible Rows
  6. The Fill Handle Does Know 1, 2, 3...

    • Bonus Tip: Fill Jan, Feb, ..., Dec, Total
    • Bonus Tip: Fill 1 to 100,000 in a Flash
  7. 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
  8. Use Default Settings for All Future Workbooks

    • Bonus Tip: Changes to Book Template are Cumulative
    • Bonus Tip: Replace the Comma Style in Book.xltx
  9. Recover Unsaved Workbooks
  10. Simultaneously Edit the Same Workbook in Office 365

    • Bonus Tip: AutoSave is Necessary, But Turn it Off When Not Co-Authoring
    • Bonus Tip: Undo an AutoSave
  11. New Threaded Comments Allow Conversations

    • Bonus Tip: Old Style Comments Are Available as Notes
    • Bonus Tip: Add a Tooltip to a Cell with Validation
  12. Create Perfect One-Click Charts
  13. Paste New Data on a Chart
  14. Create Interactive Charts
  15. Show Two Different Orders of Magnitude on a Chart
  16. Create Waterfall Charts
  17. Create Funnel Charts in Office 365
  18. Create Filled Map Charts in Office 365
  19. Create a Bell Curve
  20. Plotting Employees on a Bell Curve
  21. Add Meaning to Reports Using Data Visualizations
  22. Sort East, Central, and West Using a Custom List
  23. Sort Left to Right
  24. 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
  25. Sort and Filter by Color or Icon
  26. Consolidate Quarterly Worksheets
  27. Get Ideas from Artificial Intelligence
  28. 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
  29. Create a Year-over-Year Report in a Pivot Table

    • Bonus Tip: Another Way to Calculate Year-Over-Year
  30. Change the Calculation in a Pivot Table

    • Bonus Tip: Why Do Pivot Tables Count Instead of Sum?
  31. Find the True Top Five in a Pivot Table
  32. Specify Defaults for All Future Pivot Tables

    • Bonus Tip: Change What Drives You Crazy About Excel
  33. Make Pivot Tables Expandable Using Ctrl+T

    • Bonus Tip: Use Ctrl+T with VLOOKUP and Charts
  34. Replicate a Pivot Table for Each Rep
  35. Use a Pivot Table to Compare Lists

    • Bonus Tip: Show Up/Down Markers
    • Bonus Tip: Compare Two Lists by Using Go To Special
  36. Build Dashboards with Sparklines and Slicers

    • Bonus Tip: Make Your Workbook into a Web App
    • Bonus Tip: Line Up Dashboard Sections with Different Column Widths
    • Bonus Tip: Use Picture Lookup
    • Bonus Tip: Report Slicer Selections in a Title
  37. See Why GETPIVOTDATA Might Not Be Entirely Evil
  38. Eliminate VLOOKUP with the Data Model

    • Bonus Tip: Count Distinct
  39. Compare Budget Versus Actual via Power Pivot

    • Bonus Tip: Portable Formulas
    • Bonus Tip: Text in the Values of a Pivot Table
  40. Slicers for Pivot Tables From Two Data Sets
  41. Use F4 for Absolute Reference or Repeating Commands

    • Bonus Tip: Use a Named Range Instead of Absolute References
  42. Quickly Convert Formulas to Values

    • Bonus Tip: Skip Blanks While Pasting
  43. 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
  44. Audit a Worksheet With Spreadsheet Inquire
  45. Discover New Functions by Using fx
  46. Use Function Arguments for Nested Functions
  47. Calculate Nonstandard Work Weeks

    • Bonus Tip: Use WORKDAY.INTL for a Work Calendar
  48. Turn Data Sideways with a Formula

    • Bonus Tip: Protect Rows with an Array Formula
  49. Handle Multiple Conditions in IF

    • Bonus Tip: Use Boolean Logic
  50. Troubleshoot VLOOKUP
  51. Use a Wildcard in VLOOKUP

    • Bonus Tip: VLOOKUP to Two Tables
  52. Replace Columns of VLOOKUP with a Single MATCH
  53. Use the Fuzzy Lookup Tool from Microsoft Labs
  54. Lookup to the Left with INDEX/MATCH
  55. Twelve Benefits of XLOOKUP
  56. Preview What Remove Duplicates Will Remove
  57. Replace Nested IFs with a Lookup Table

    • Mini Bonus Tip: Match the Parentheses
  58. Suppress Errors with IFERROR
  59. Handle Plural Conditions with SUMIFS
  60. Geography, Exchange Rate & Stock Data Types in Excel

    • Bonus Tip: Use Data, Refresh All to Update Stock Data
  61. Dynamic Arrays Can Spill
  62. Sorting with a Formula
  63. Filter with a Formula

    • Bonus Tip: Understanding Array Constants
  64. Formula for Unique or Distinct

    • Bonus Tip: Use # "The Spiller" to Refer to All Array Results
  65. Other Functions Can Now Accept Arrays as Arguments
  66. One Hit Wonders with UNIQUE
  67. SEQUENCE inside of other Functions such as IPMT
  68. Replace a Pivot Table with 3 Dynamic Arrays

    • Bonus Tip: Replace Ctrl+Shift+Enter with Dynamic Arrays
  69. Dependent Validation using Dynamic Arrays
  70. Complex Validation Using a Formula
  71. Use A2:INDEX() as a Non-Volatile OFFSET
  72. Subscribe to Office 365 for Monthly Features
  73. How to Provide Usable Feedback to the Excel Team
  74. Find Largest Value That Meets One or More Criteria

    • Bonus Tip: Concatenate a Range by Using TEXTJOIN
  75. Less CSV Nagging and Better AutoComplete
  76. Speed Up VLOOKUP
  77. Protect All Formula Cells
  78. Back into an Answer by Using Goal Seek
  79. Do 60 What-If Analyses with a Sensitivity Analysis

    • Bonus Tip: Create a Data Table from a Blank Cell
  80. Find Optimal Solutions with Solver
  81. Improve Your Macro Recording
  82. Clean Data with Power Query
  83. Render Excel Data on an iPad Dashboard Using Power BI
  84. Build a Pivot Table on a Map Using 3D Maps
  85. The Forecast Sheet Can Handle Some Seasonality
  86. Perform Sentiment Analysis in Excel
  87. Fill in a Flash
  88. Format as a Façade
  89. Surveys & Forms in Excel
  90. Use the Windows Magnifier
  91. Word for Excellers

    • Bonus Tip: Merge Shapes
    • Bonus Tip: Use the Eye Dropper
  92. Avoid Whiplash with Speak Cells

    • Bonus Tip: Provide Feedback with Sound
    • Bonus Tip: A Great April Fool’s Day Trick
  93. Customize the Quick Access Toolbar

    • Bonus Tip: Sometimes, You Don't Want the Gallery
    • Bonus Tip: Show QAT Below the Ribbon
  94. Create Your Own QAT Routines Using VBA Macros

    • Bonus Tip: Settings in the Excel Options Menu
  95. Favorite Keyboard Shortcuts
  96. Ctrl+Click to Unselect Cells
  97. Collapse the Search Box
  98. More Excel Tips
  99. Excel Stories

Where to Buy

MrExcel Store

Bill Jelen is the author / co-author of
Excel Insights – A Microsoft MVP Guide to the Best Parts of Excel

There are fewer than 100 Excel MVPs worldwide. 24 of them have contributed to this book. Written, edited, reviewed and printed by Excel MVPs, this is practical Excel passion undiluted, with each MVP highlighting some of their favorite topics.

Hear the Figures

If you are using assistive reading technology (for low-vision, dyslexia, mobility impairments, attention issues, or if you need to read hands free), this e-book is "born accessible", with a complete description attached to each screenshot so you can follow along with the text.

Other Editions

Related Products