MrExcel 2021: Unmasking Excel
- 3h 59m
- Bill Jelen
- Tickling Keys
- 2021
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.
About the Author
Bill Jelen is the host of www.MrExcel.com, a Microsoft MVP, and the author of 60 books about Excel. He lives in Merritt Island, Florida.
In this Book
-
#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
-
#6 Total the Visible Rows
-
#7 Save Filter & Sorting in Sheet View
-
#8 The Fill Handle Does Know 1, 2, 3…
-
#9 Fast Worksheet Copy
-
#10 Use Default Settings for All Future Workbooks
-
#11 Recover Unsaved Workbooks
-
#12 Simultaneously Edit a Workbook in Microsoft 365
-
#13 New Threaded Comments Allow Conversations
-
#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 People to Add Interest to Your Worksheet
-
#25 Make an Image Semi-Transparent
-
#26 Save Any Object as an Image
-
#27 Set Up Your Data for Data Analysis
-
#28 Sort East, Central, and West Using a Custom List
-
#29 Sort Left to Right
-
#30 Sort Subtotals
-
#31 Sort and Filter by Color or Icon
-
#32 Consolidate Quarterly Worksheets
-
#33 Create Your First Pivot Table
-
#34 Create a Year-over-Year Report in a Pivot Table
-
#35 Change the Calculation in a Pivot Table
-
#36 Find the True Top Five in a Pivot Table
-
#37 Specify Defaults for All Future Pivot Tables
-
#38 Make Pivot Tables Expandable Using Ctrl+T
-
#39 Replicate a Pivot Table for Each Rep
-
#40 Use a Pivot Table to Compare Lists
-
#41 Build Dashboards with Sparklines and Slicers
-
#42 See Why GETPIVOTDATA Might Not Be Entirely Evil
-
#43 Eliminate VLOOKUP or XLOOKUP with the Data Model
-
#44 Compare Budget Versus Actual via Power Pivot
-
#45 Slicers for Pivot Tables From Two Data Sets
-
#46 Use F4 for Absolute Reference or Repeating Commands
-
#47 Quickly Convert Formulas to Values
-
#48 See All Formulas at Once
-
#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
-
#53 Turn Data Sideways with a Formula
-
#54 Handle Multiple Conditions in IF
-
#55 Troubleshoot VLOOKUP
-
#56 Use a Wildcard in VLOOKUP
-
#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
-
#62 Suppress Errors with IFERROR
-
#63 Handle Plural Conditions with SUMIFS
-
#64 Geography, Exchange Rate & Stock Data Types in Excel
-
#65 Get Historical Stock History from STOCKHISTORY
-
#66 More Data Types from Wolfram Alpha
-
#67 Getting Historical Weather For a City
-
#68 Create Your Own Data Types Using Power Query
-
#69 IF Based on Installed Language in a Bilingual Worksheet
-
#70 Dynamic Arrays Can Spill
-
#71 Sorting with a Formula
-
#72 Filter with a Formula
-
#73 Formula for Unique or Distinct
-
#74 Other Functions Can Now Accept Arrays as Arguments
-
#75 One Hit Wonders with UNIQUE
-
#76 SEQUENCE inside of other Functions such as IPMT
-
#77 Replace a Pivot Table with 3 Dynamic Arrays
-
#78 Dependent Validation using Dynamic Arrays
-
#79 Complex Validation Using a Formula
-
#80 Use A2:INDEX() as a Non-Volatile OFFSET
-
#81 Subscribe to Microsoft 365 for Monthly Features
-
#82 Performance Improvements in 2020 for Microsoft 365
-
#83 Unhide Multiple Worksheets
-
#84 Write Your Data with the Action Pen
-
#85 Many Task Panes Now Collapse into a Tab Strip
-
#86 How to Provide Usable Feedback to the Excel Team
-
#87 Date Tricks in Excel
-
#88 Use the LET Function to Re-Use Variables in a Formula
-
#89 Store Complex Formula Logic in LAMBDA function
-
#90 Find Largest Value That Meets One or More Criteria
-
#91 Less CSV Nagging and Better AutoComplete
-
#92 Speed Up VLOOKUP
-
#93 Protect All Formula Cells
-
#94 Back into an Answer by Using Goal Seek
-
#95 Do 60 What-If Analyses with a Sensitivity Analysis
-
#96 Find Optimal Solutions with Solver
-
#97 Improve Your Macro Recording
-
#98 Clean Data with Power Query
-
#99 Use Fuzzy Match in Power Query
-
#100 Render Excel Data on an iPad Dashboard Using Power BI
-
#101 Build a Pivot Table on a Map Using 3D Maps
-
#102 The Forecast Sheet Can Handle Some Seasonality
-
#103 Perform Sentiment Analysis in Excel
-
#104 Build Org Charts with the Visio Data Visualizer in Excel
-
#105 Fill in a Flash
-
#106 Format as a Façade
-
#107 Show All Open Workbooks in the Windows Taskbar
-
#108 Surveys & Forms in Excel
-
#109 Use the Windows Magnifier
-
#110 Word for Excellers
-
#111 Avoid Whiplash with Speak Cells
-
#112 Customize the Quick Access Toolbar
-
#113 Create Your Own QAT Routines Using VBA Macros
-
#114 Favorite Keyboard Shortcuts
-
#115 Ctrl+Click to Unselect Cells
-
#116 Collapse the Search Box
-
#117 More Excel Tips
-
#118 Excel Stories
-
#119 Excel Function Quick Reference
SHOW MORE
FREE ACCESS
