MrExcel 2021
eBook - ePub

MrExcel 2021

Unmasking Excel

  1. 256 pages
  2. English
  3. ePUB (mobile friendly)
  4. Available on iOS & Android
eBook - ePub

MrExcel 2021

Unmasking Excel

About this book

MrExcel 2021 is designed to make the reader far more efficient in their use of Microsoft Excel. 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, 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.

Tools to learn more effectively

Saving Books

Saving Books

Keyword Search

Keyword Search

Annotating Text

Annotating Text

Listen to it instead

Listen to it instead

Information

Publisher
Holy Macro! Books
Year
2021
Print ISBN
9781615470631
eBook ISBN
9781615471577
Edition
5
Topic
Computer Science
Subtopic
Desktop Applications
Index
Computer Science
#1 Ask Excel's A.I. a Question About Your Data
A new Natural Language Query feature started rolling out to Microsoft 365 in late 2019. The feature uses artificial intelligence to answer questions about your data.
The feature can be found near the right side of the Home tab. The icon is a blue lightning bolt. The label for the icon has changed between Insights, Ideas, Data Analysis, and Data Ideas.
Note: Microsoft loves to do A|B testing with feature names. As I write this book, they are conducting one such test. One of my computers is calling the feature Data Analysis and the other is calling it Data Ideas. Microsoft might call it Analyze Data or Clippy 2.0. Any name will operate the same. By the time you are reading this, they will have settled on a name. In any case, look for the blue lightning bolt that is second from the right side of the Home tab.
It is the #1 tip in this book because of this problem: (1) The feature has the potential to help millions of people, and (2) it is hidden where no one will find it.
Anyone can perform advanced data analysis by simply asking questions by typing a sentence. Your data set can be up to 250,000 cells. Select one cell in your data. Use the blue lightning bolt icon on the right side of the Home tab.
The Ideas icon is relatively new, near the right side of the Home tab.
A box says to Ask A Question About Your Data and it gives you a few sample questions.
Type a question such as "Top 3 Products by 'Sales' where Category is "Bikes"". Excel restates your question and shows you a thumbnail of the report.
If this is the correct analysis, you can use the +Insert Pivot Table icon to insert the results into a new worksheet in your workbook.
Sometimes the feature will give you a chart when you want a table. Try adding "as table" to the end of your sentence.
The "Is this helpful?" link in the lower right is not being used. The original idea was to use Machine Learning to suggest better reports in the future. But the reality is that Microsoft is taking privacy very seriously and they can't learn without retaining your data.
New for 2021 is the "Which Fields Interest You the Most?". This can be used to tell Excel that they should never offer to sum fields such as Year, Part Number, or Cost Center. You can choose to Sum or Average numeric fields. Or you can uncheck the field to make sure it is not in any of the suggestions.
Even before you type a question, Excel will offer you 5-10 suggested reports and a link to load up to 30 more reports. If you aren't sure what you are looking for, it is sometimes interesting to read through these suggested reports.
My one complaint about the feature is shown in the following chart. Ideas was able to find some outliers in this data and offers to create a chart with those points called out in orange. For this chart to work correctly, Excel would have to support conditional formatting in charts and it does not. That means that the pivot chart will always call out these three points, even if the underlying data changes and new outliers emerge. You would have to re-run Ideas and hope that a similar result is offered.
This tile from Ideas shows that for Product Gadget, the Revenue has outliers on three specific dates.
#2 Double-Click the Fill Handle to Copy a Formula
You have thousands of rows of data. You’ve added a new formula in the top row of your data set, something like =PROPER(A2&" "&B2), as shown here. You need to copy the formula down to all of the rows of your data set.
Many people will grab the Fill Handle and start to drag down. But as you drag down, Excel starts going faster and faster. There is a 200-microsecond pause at the last row of data. 200 microseconds is long enough for you to notice the pause but not long enough for you to react and let go of the mouse button. Before you know it, you’ve dragged the...

Table of contents

  1. About the Author
  2. About the Illustrators
  3. Foreword
  4. Sample File Downloads
  5. #1 Ask Excel's A.I. a Question About Your Data
  6. #2 Double-Click the Fill Handle to Copy a Formula
  7. #3 Break Apart Data
  8. #4 Convert Text Numbers to Numbers Quickly
  9. #5 Filter by Selection
  10. #6 Total the Visible Rows
  11. #7 Save Filter & Sorting in Sheet View
  12. #8 The Fill Handle Does Know 1, 2, 3…
  13. #9 Fast Worksheet Copy
  14. #10 Use Default Settings for All Future Workbooks
  15. #11 Recover Unsaved Workbooks
  16. #12 Simultaneously Edit a Workbook in Microsoft 365
  17. #13 New Threaded Comments Allow Conversations
  18. #14 Create Perfect One-Click Charts
  19. #15 Paste New Data on a Chart
  20. #16 Create Interactive Charts
  21. #17 Show Two Different Orders of Magnitude on a Chart
  22. #18 Create Waterfall Charts
  23. #19 Create Funnel Charts
  24. #20 Create Filled Map Charts in Microsoft 365
  25. #21 Create a Bell Curve
  26. #22 Plotting Employees on a Bell Curve
  27. #23 Add Meaning to Reports Using Data Visualizations
  28. #24 Use People to Add Interest to Your Worksheet
  29. #25 Make an Image Semi-Transparent
  30. #26 Save Any Object as an Image
  31. #27 Set Up Your Data for Data Analysis
  32. #28 Sort East, Central, and West Using a Custom List
  33. #29 Sort Left to Right
  34. #30 Sort Subtotals
  35. #31 Sort and Filter by Color or Icon
  36. #32 Consolidate Quarterly Worksheets
  37. #33 Create Your First Pivot Table
  38. #34 Create a Year-over-Year Report in a Pivot Table
  39. #35 Change the Calculation in a Pivot Table
  40. #36 Find the True Top Five in a Pivot Table
  41. #37 Specify Defaults for All Future Pivot Tables
  42. #38 Make Pivot Tables Expandable Using Ctrl+T
  43. #39 Replicate a Pivot Table for Each Rep
  44. #40 Use a Pivot Table to Compare Lists
  45. #41 Build Dashboards with Sparklines and Slicers
  46. #42 See Why GETPIVOTDATA Might Not Be Entirely Evil
  47. #43 Eliminate VLOOKUP or XLOOKUP with the Data Model
  48. #44 Compare Budget Versus Actual via Power Pivot
  49. #45 Slicers for Pivot Tables From Two Data Sets
  50. #46 Use F4 for Absolute Reference or Repeating Commands
  51. #47 Quickly Convert Formulas to Values
  52. #48 See All Formulas at Once
  53. #49 Audit a Worksheet With Spreadsheet Inquire
  54. #50 Discover New Functions by Using fx
  55. #51 Use Function Arguments for Nested Functions
  56. #52 Calculate Nonstandard Work Weeks
  57. #53 Turn Data Sideways with a Formula
  58. #54 Handle Multiple Conditions in IF
  59. #55 Troubleshoot VLOOKUP
  60. #56 Use a Wildcard in VLOOKUP
  61. #57 Replace Columns of VLOOKUP with a Single MATCH
  62. #58 Lookup to the Left with INDEX/MATCH
  63. #59 Twelve Benefits of XLOOKUP
  64. #60 Preview What Remove Duplicates Will Remove
  65. #61 Replace Nested IFs with a Lookup Table
  66. #62 Suppress Errors with IFERROR
  67. #63 Handle Plural Conditions with SUMIFS
  68. #64 Geography, Exchange Rate & Stock Data Types in Excel
  69. #65 Get Historical Stock History from STOCKHISTORY
  70. #66 More Data Types from Wolfram Alpha
  71. #67 Getting Historical Weather For a City
  72. #68 Create Your Own Data Types Using Power Query
  73. #69 IF Based on Installed Language in a Bilingual Worksheet
  74. #70 Dynamic Arrays Can Spill
  75. #71 Sorting with a Formula
  76. #72 Filter with a Formula
  77. #73 Formula for Unique or Distinct
  78. #74 Other Functions Can Now Accept Arrays as Arguments
  79. #75 One Hit Wonders with UNIQUE
  80. #76 SEQUENCE inside of other Functions such as IPMT
  81. #77 Replace a Pivot Table with 3 Dynamic Arrays
  82. #78 Dependent Validation using Dynamic Arrays
  83. #79 Complex Validation Using a Formula
  84. #80 Use A2:INDEX() as a Non-Volatile OFFSET
  85. #81 Subscribe to Microsoft 365 for Monthly Features
  86. #82 Performance Improvements in 2020 for Microsoft 365
  87. #83 Unhide Multiple Worksheets
  88. #84 Write Your Data with the Action Pen
  89. #85 Many Task Panes Now Collapse into a Tab Strip
  90. #86 How to Provide Usable Feedback to the Excel Team
  91. #87 Date Tricks in Excel
  92. #88 Use the LET Function to Re-Use Variables in a Formula
  93. #89 Store Complex Formula Logic in LAMBDA function
  94. #90 Find Largest Value That Meets One or More Criteria
  95. #91 Less CSV Nagging and Better AutoComplete
  96. #92 Speed Up VLOOKUP
  97. #93 Protect All Formula Cells
  98. #94 Back into an Answer by Using Goal Seek
  99. #95 Do 60 What-If Analyses with a Sensitivity Analysis
  100. #96 Find Optimal Solutions with Solver
  101. #97 Improve Your Macro Recording
  102. #98 Clean Data with Power Query
  103. #99 Use Fuzzy Match in Power Query
  104. #100 Render Excel Data on an iPad Dashboard Using Power BI
  105. #101 Build a Pivot Table on a Map Using 3D Maps
  106. #102 The Forecast Sheet Can Handle Some Seasonality
  107. #103 Perform Sentiment Analysis in Excel
  108. #104 Build Org Charts with the Visio Data Visualizer in Excel
  109. #105 Fill in a Flash
  110. #106 Format as a Façade
  111. #107 Show All Open Workbooks in the Windows Taskbar
  112. #108 Surveys & Forms in Excel
  113. #109 Use the Windows Magnifier
  114. #110 Word for Excellers
  115. #111 Avoid Whiplash with Speak Cells
  116. #112 Customize the Quick Access Toolbar
  117. #113 Create Your Own QAT Routines Using VBA Macros
  118. #114 Favorite Keyboard Shortcuts
  119. #115 Ctrl+Click to Unselect Cells
  120. #116 Collapse the Search Box
  121. #117 More Excel Tips
  122. #118 Excel Stories
  123. #119 Excel Function Quick Reference
  124. #120 Index

Frequently asked questions

Yes, you can cancel anytime from the Subscription tab in your account settings on the Perlego website. Your subscription will stay active until the end of your current billing period. Learn how to cancel your subscription
No, books cannot be downloaded as external files, such as PDFs, for use outside of Perlego. However, you can download books within the Perlego app for offline reading on mobile or tablet. Learn how to download books offline
Perlego offers two plans: Essential and Complete
  • Essential is ideal for learners and professionals who enjoy exploring a wide range of subjects. Access the Essential Library with 800,000+ trusted titles and best-sellers across business, personal growth, and the humanities. Includes unlimited reading time and Standard Read Aloud voice.
  • Complete: Perfect for advanced learners and researchers needing full, unrestricted access. Unlock 1.4M+ books across hundreds of subjects, including academic and specialized titles. The Complete Plan also includes advanced features like Premium Read Aloud and Research Assistant.
Both plans are available with monthly, semester, or annual billing cycles.
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 990+ topics, we’ve got you covered! Learn about our mission
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more about Read Aloud
Yes! You can use the Perlego app on both iOS and Android devices to read anytime, anywhere — even offline. Perfect for commutes or when you’re on the go.
Please note we cannot support devices running on iOS 13 and Android 7 or earlier. Learn more about using the app
Yes, you can access MrExcel 2021 by Bill Jelen in PDF and/or ePUB format, as well as other popular books in Computer Science & Desktop Applications. We have over one million books available in our catalogue for you to explore.