MrExcel 2024

Table of Contents

1. Double-Click the Fill Handle to Copy a Formula
2. Break Apart Data
3. Convert Text Numbers to Numbers Quickly
4. Filter by Selection
5. Total the Visible Rows
6. The Fill Handle Does Know 1, 2, 3…
7. Fast Worksheet Copy
8. Use Default Settings for All Future Workbooks
9. Recover Unsaved Workbooks
10. Search While Using File Open
11. Show Changes From Last 60 Days
12. Simultaneously Edit a Workbook in Microsoft 365
13. Save Filter & Sorting in Sheet View
14. New Threaded Comments Allow Conversations
15. Create Perfect One-Click Charts
16. Paste New Data on a Chart
17. Create Interactive Charts
18. Show Two Different Orders of Magnitude on a Chart
19. Create Waterfall Charts
20. Create Funnel Charts
21. Create Filled Map Charts
22. Create a Bell Curve
23. Plotting Employees on a Bell Curve
24. Add Meaning to Reports Using Data Visualizations
25. Use People to Add Interest to Your Worksheet
26. Save Any Object as an Image
27. Display Online Pictures In a Cell Using a Formula
28. Place Pictures In Cell From Local Computer
29. Set Up Your Data for Data Analysis
30. Sort East, Central, and West Using a Custom List
31. Sort Left to Right
32. Sort Subtotals
33. Sort and Filter by Color or Icon
34. Consolidate Quarterly Worksheets
35. Create Your First Pivot Table
36. Create a Year-over-Year Report in a Pivot Table
37. Change the Calculation in a Pivot Table
38. Find the True Top Five in a Pivot Table
39. Specify Defaults for All Future Pivot Tables
40. Make Pivot Tables Expandable Using Ctrl+T
41. Replicate a Pivot Table for Each Rep
42. Use a Pivot Table to Compare Lists
43. Build Dashboards with Sparklines and Slicers
44. See Why GETPIVOTDATA Might Not Be Entirely Evil
45. Ask Excel's A.I. a Question About Your Data
46. Using Artificial Intelligence with Copilot for Excel
47. Ask OpenAI Questions from Excel Using Excel Labs
48. Eliminate VLOOKUP or XLOOKUP with the Data Model
49. Pivot Table with Sum and Average Total Rows (MDX)
50. Compare Budget Versus Actual via Power Pivot
51. Slicers for Pivot Tables From Two Data Sets
52. Use F4 for Absolute Reference or Repeating Commands
53. Quickly Convert Formulas to Values
54. See All Formulas at Once
55. Audit a Worksheet With Spreadsheet Inquire
56. Discover New Functions by Using fx
57. Use Function Arguments for Nested Functions
58. Calculate Nonstandard Work Weeks
59. Turn Data Sideways with a Formula
60. Handle Multiple Conditions in IF
61. Troubleshoot VLOOKUP
62. Use a Wildcard in VLOOKUP or XLOOKUP
63. Twelve Benefits of XLOOKUP
64. Preview What Remove Duplicates Will Remove
65. Replace Nested IFs with a Lookup Table
66. Suppress Errors with IFERROR
67. Handle Plural Conditions with SUMIFS
68. Geography, Exchange Rate & Stock Data Types in Excel
69. Get Historical Stock History from STOCKHISTORY
70. Create Your Own Data Types
71. IF Based on Installed Language in a Bilingual Worksheet
72. Dynamic Arrays Can Spill
73. Sorting with a Formula
74. Filter with a Formula
75. Formula for Unique or Distinct
76. Other Functions Can Now Accept Arrays as Arguments
77. One Hit Wonders with UNIQUE
78. SEQUENCE inside of other Functions such as IPMT
79. Generating Random Numbers in Excel
80. Replace a Pivot Table with 3 Dynamic Arrays
81. Create a Summary Table With the GROUPBY Function
82. Moving from GROUPBY to PIVOTBY
83. Calculate Percent of Total with PERCENTOF Function
84. Dependent Validation using Dynamic Arrays
85. Complex Validation Using a Formula
86. Stack Multiple Arrays
87. Dropping, Taking, or Choosing from an Array
88. Reshaping an Array to a Vector and Back
89. Getting the UNIQUE of a Rectangular Range
90. Shuffling and Dealing a Deck of Cards
91. Use A2
92. Number the Visible Rows in a Filtered Data Set
93. Unhide Multiple Worksheets
94. Write Your Data with the Action Pen
95. Many Task Panes Now Collapse into a Tab Strip
96. How to Provide Usable Feedback to the Excel Team
97. Date Tricks in Excel
98. Using Python in Excel
99. Python for K-Means Clustering of Excel Data
100. Use the LET Function to Re-Use Variables in a Formula
101. Store Complex Formula Logic in LAMBDA function
102. Combining Subformulas into a Single Lambda
103. New LAMBDA Helper Functions
104. Find Largest Value That Meets One or More Criteria
105. Text Before or After a Specific Delimiter
106. Split Text into Words Using TEXTSPLIT
107. Less CSV Nagging and Better AutoComplete
108. Protect All Formula Cells
109. Back into an Answer by Using Goal Seek
110. Do 60 What-If Analyses with a Sensitivity Analysis
111. Find Optimal Solutions with Solver
112. Interpolate between a starting and ending number
113. Improve Your Macro Recording
114. Clean Data with Power Query
115. Use Fuzzy Match in Power Query
116. Data From Picture is Not Power Query
117. Build a Pivot Table on a Map Using 3D Maps
118. The Forecast Sheet Can Handle Some Seasonality
119. Perform Sentiment Analysis in Excel
120. Build Org Charts with the Visio Data Visualizer in Excel
121. Fill in a Flash
122. Format as a Façade
123. Show All Open Workbooks in the Windows Taskbar
124. Paste to Another Computer Using Cloud Clipboard
125. Surveys & Forms in Excel
126. Use the Windows Magnifier
127. New Scrolling Tricks
128. Checkboxes
129. Prevent Default Conversions
130. Check Performance in Excel
131. See Intermediate Calculations
132. Stale Value Formatting
133. Sort by Font Width?!
134. Get the Countries from a Column of Phone Numbers
135. Word for Excellers
136. Avoid Whiplash with Speak Cells
137. Temporarily Make Formula Bar Font Size Larger
138. Oddity 1: The Year 2025 is the Square of 45
139. Circle Invalid Data Automatically
140. The Advanced Filter Treats Criteria as "Begins With"
141. Generate All Combinations Using BASE Function
142. Why a Dark Rectangle Around the Formula Bar?
143. Unhide One Specific Column Without Unhiding Others
144. Customize the Quick Access Toolbar
145. Create Your Own QAT Routines Using VBA Macros
146. Favorite Keyboard Shortcuts
147. Ctrl+Click to Unselect Cells
148. Collapse the Search Box
149. More Excel Tips
150. Excel Stories
151. Excel Function Quick Reference