MrExcel 2021 Unmasking Excel

Can you afford to not keep up with Excel?

MrExcel 2021 Unmasking Excel

5th Edition of MrExcel XL
updated with LAMBDA
and other new Office 365 features

Author: Bill Jelen, MrExcel

Click here to read more and purchase

USD 11.99
Shop with Full Confidence
Partner Product

✔ 100% Secure Shopping
✔ No Complex Software or Subscriptions
✔ Secure Digital Delivery with E-Junkie
✔ No Expensive Licenses

✔ 257 Pages
✔ ISBN: 978-1-61547-071-6
✔ PDF ISBN: 978-1-61547-157-7
✔ Instant Download


All of MrExcel’s favorite tips!

257 pages of Excel tips and tricks authored by Bill Jelen.

The updated fifth edition of the book formerly known as MrExcel XL, MrExcel LIVe, MrExcel XL, MrExcel 2020. Updated with 40+ pages of new features, including Custom Data Types, LET, LAMBDA and much more.


Description

This is a 5th edition of MrExcel XL.

Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.

Updates in 2020 included 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.


Table of Contents

  1. Ask Excel’s A.I. a Question About Your Data
  2. Double-Click the Fill Handle to Copy a Formula
  3. Break Apart Data
  4. Convert Text Numbers to Numbers Quickly
  5. Filter by Selection
    • Bonus Tip: Filter by Selection for Numbers Over/Under
  6. Total the Visible Rows
  7. Save Filter & Sorting in Sheet View
  8. 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
  9. 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
  10. Use Default Settings for All Future Workbooks
    • Bonus Tip: Changes to Book Template are Cumulative
    • Bonus Tip: Replace the Comma Style in Book.xltx
  11. Recover Unsaved Workbooks
  12. Simultaneously Edit a Workbook in Microsoft 365
    • Bonus Tip: AutoSave is Necessary, But Turn it Off When Not Co-Authoring
    • Bonus Tip: Undo an AutoSave
  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
  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 Cutout People to Add Interest to Your Worksheet
    • Bonus Tip: Add Text to the Sign Held by a Cutout 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: 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. Eliminate VLOOKUP or XLOOKUP with the Data Model
    • Bonus Tip: Count Distinct
  44. Compare Budget Versus Actual via Power Pivot
    • Bonus Tip: Portable Formulas
    • Bonus Tip: Text in the Values of a Pivot Table
  45. Slicers for Pivot Tables From Two Data Sets
  46. Use F4 for Absolute Reference or Repeating Commands
    • Bonus Tip: Use a Named Range Instead of Absolute References
  47. Quickly Convert Formulas to Values
    • Bonus Tip: Skip Blanks While Pasting
  48. 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
  49. Audit a Worksheet With Spreadsheet Inquire
  50. Discover New Functions by Using fx
  51. Use Function Arguments for Nested Functions
  52. Calculate Nonstandard Work Weeks
    • Bonus Tip: Use WORKDAY.INTL for a Work Calendar
  53. Turn Data Sideways with a Formula
    • Bonus Tip: Protect Rows with an Array Formula
  54. Handle Multiple Conditions in IF
    • Bonus Tip: Use Boolean Logic
  55. Troubleshoot VLOOKUP
  56. Use a Wildcard in VLOOKUP
    • Bonus Tip: VLOOKUP to Two Tables
  57. Replace Columns of VLOOKUP with a Single MATCH
  58. Lookup to the Left with INDEX/MATCH
  59. Twelve Benefits of XLOOKUP
  60. Preview What Remove Duplicates Will Remove
  61. Replace Nested IFs with a Lookup Table
    • Bonus Tip: Match the Parentheses
  62. Suppress Errors with IFERROR
  63. Handle Plural Conditions with SUMIFS
  64. Geography, Exchange Rate & Stock Data Types in Excel
    • Bonus Tip: Use Data, Refresh All to Update Stock Data
  65. Get Historical Stock History from STOCKHISTORY
  66. More Data Types from Wolfram Alpha
    • Bonus Tip: The Data Card is now Expandable and Supports Arrays
  67. Getting Historical Weather For a City
  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. Replace a Pivot Table with 3 Dynamic Arrays
    • Bonus Tip: Replace Ctrl+Shift+Enter with Dynamic Arrays.
  77. Dependent Validation using Dynamic Arrays
  78. Complex Validation Using a Formula
  79. Use A2:INDEX() as a Non-Volatile OFFSET
  80. Subscribe to Microsoft 365 for Monthly Features
  81. Performance Improvements in 2020 for Microsoft 365
  82. Unhide Multiple Worksheets
  83. Write Your Data with the Action Pen
  84. Many Task Panes Now Collapse into a Tab Strip
  85. How to Provide Usable Feedback to the Excel Team
  86. Date Tricks in Excel
  87. Use the LET Function to Re-Use Variables in a Formula
  88. Store Complex Formula Logic in LAMBDA function
    • Bonus Tip: Perform a Loop with a Recursive LAMBDA
    • Bonus Tip: Branching in a LAMBDA
    • Bonus Tip: Using LAMBDA to return a Picture
  89. Find Largest Value That Meets One or More Criteria
    • Bonus Tip: Concatenate a Range by Using TEXTJOIN
  90. Less CSV Nagging and Better AutoComplete
  91. Speed Up VLOOKUP
  92. Protect All Formula Cells
  93. Back into an Answer by Using Goal Seek
  94. Do 60 What-If Analyses with a Sensitivity Analysis
    • Bonus Tip: Create a Data Table from a Blank Cell
  95. Find Optimal Solutions with Solver
  96. Improve Your Macro Recording
    • Bonus Tip: Use TypeScript to Write Macros for Excel Online
  97. Clean Data with Power Query
    • Bonus Tip: Tame the Sequence of Refresh All
    • Bonus Tip: Data Profiling in Power Query
  98. Use Fuzzy Match in Power Query
  99. Render Excel Data on an iPad Dashboard Using Power BI
  100. Build a Pivot Table on a Map Using 3D Maps
  101. The Forecast Sheet Can Handle Some Seasonality
  102. Perform Sentiment Analysis in Excel
  103. Build Org Charts with the Visio Data Visualizer in Excel
  104. Fill in a Flash
  105. Format as a Façade
  106. Show All Open Workbooks in the Windows Taskbar
  107. Surveys & Forms in Excel
  108. Use the Windows Magnifier
  109. Word for Excellers
    • Bonus Tip: Merge Shapes
    • Bonus Tip: Use the Eye Dropper
  110. Avoid Whiplash with Speak Cells
    • Bonus Tip: Provide Feedback with Sound
    • Bonus Tip: A Great April Fool’s Day Trick
  111. Customize the Quick Access Toolbar
    • Bonus Tip: Sometimes, You Don’t Want the Gallery
    • Bonus Tip: Show QAT Below the Ribbon
  112. Create Your Own QAT Routines Using VBA Macros
    • Bonus Tip: Settings in the Excel Options Menu
  113. Favorite Keyboard Shortcuts
  114. Ctrl+Click to Unselect Cells
  115. Collapse the Search Box
  116. More Excel Tips
  117. Excel Stories
  118. Excel Function Quick Reference
  119. Index

MrExcel is a popular online platform and community that provides resources, forums, and tutorials related to Microsoft Excel. Bill Jelen, also known as MrExcel, is a well-known expert in Excel and has authored several books and courses.


Frequently Asked Questions – Partner Products

Does the Refund Apply to Partner Products?

The Terms and Conditions of Sale for Partner Products including refunds is in accordance with the Partners Terms and Conditions. Refer to their website.

While Digital Documents Direct makes every attempt to provided detailed, comprehensive information, you are responsible for reviewing, checking and confirming the information provided is suitable for your use.

Is this complicated Software or Subscriptions?

No. This is not complicated software where you need an ongoing subscription or annual fee. It is a one off payment.

Do I need a Paypal account to purchase?

No, you do not need a Paypal account to purchase our products. You can pay with Paypal or use your credit card.

How will my information be used?

Your information will not be used whatsoever without your permission. In other words, your email address will not be sold or spammed.

Will I get on a spam list?

No. We may add you to our newsletter list. However you can ‘opt out’ at any time. Above all, you will not be spammed.

Will I be the victim of identity theft?

No. Paypal and e-Junkie provide a 100% secure system to perform the transaction.

Will the purchase arrive on time?

Yes. As soon as you have paid for your purchase, you will receive a download link via email. However, please remember to also check your ‘Junk Email’ as if you have not received your link immediately, the email could be in your ‘Junk Email Inbox’.

Should you not receive the download link, please contact us immediately and we will get back to you as soon as we can. Please note, the time zone of your country may effect our response time, however we will respond as soon as we can. Our business is located in Australia.

AEST – Australian Eastern Standard Time.

Will I actually get what I ordered?

Yes. We promise to deliver to you and stand by this promise. Further to this, Paypal guarantees your transaction so you can shop with full confidence.

Will unexpected fine print charges be added to my order without my knowledge?

No. There are no further charges. No monthly subscriptions either. You have the right to use the document to your desires within the terms and conditions of the Partner website. Refer to their website for more information.

Will anyone respond if I have problems after buying?

Yes. However, there may be a delay in our response. Depending on your country timezone, we will respond as soon as we can. Please note, we endeavor to respond to your requests within the shortest possible time.

Please do not panic. e-Junkie are both very reliable document delivery systems and it is unlikely this will occur. However, if it does, we will email you the download link as soon as we can.

The website briefly redirected through e-Junkie. Is this safe?

Yes it is safe. When you click on the Proceed to Pay button, you will be redirected briefly through e-Junkie.com to Paypal.com depending on how you choose to pay. Partner products are delivered electronically so you receive them immediately – 24 Hours a day, 7 days a week.

Scroll to Top