eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615471577 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 04/01/2021 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 256 |
| File size: | 36 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Table of Contents
About the Author vi
About the Illustrators vi
Foreword vi
Sample File Downloads vi
#1 Ask Excel's A.I. a Question About Your Data 1
#2 Double-Click the Fill Handle to Copy a Formula 3
#3 Break Apart Data 5
#4 Convert Text Numbers to Numbers Quickly 8
#5 Filter by Selection 9
Bonus Tip: Filter by Selection for Numbers Over/Under 11
#6 Total the Visible Rows 12
#7 Save Filter & Sorting in Sheet View 13
#8 The Fill Handle Does Know 1, 2, 3 16
Bonus Tip: Fill Jan, Feb, …, Dec, Total 19
Bonus Tip: Fill 1 to 100,000 in a Flash 19
#9 Fast Worksheet Copy 19
Bonus Tip: Put the Worksheet Name in a Cell 20
Bonus Tip: Add a Total Row and a Total Column with One AutoSum 21
Bonus Tip: Power Up the Status Bar Statistics 21
Bonus Tip: Change All Sheets with Group Mode 21
Bonus Tip: Create a SUM That Spears Through All Worksheets 22
Bonus Tip: Use INDIRECT for a Different Summary Report 23
#10 Use Default Settings for All Future Workbooks 24
Bonus Tip: Changes to Book Template are Cumulative 27
Bonus Tip: Replace the Comma Style in Book.xltx 28
#11 Recover Unsaved Workbooks 30
#12 Simultaneously Edit a Workbook in Microsoft 365 32
Bonus Tip: AutoSave is Necessary, But Turn it Off When Not Co-Authoring 34
Bonus Tip: Undo an AutoSave 34
#13 New Threaded Comments Allow Conversations 35
Bonus Tip: Old Style Comments Are Available as Notes 35
Bonus Tip: Add a Tooltip to a Cell with Validation 37
#14 Create Perfect One-Click Charts 38
#15 Paste New Data on a Chart 40
#16 Create Interactive Charts 42
#17 Show Two Different Orders of Magnitude on a Chart 44
#18 Create Waterfall Charts 45
#19 Create Funnel Charts 46
#20 Create Filled Map Charts in Microsoft 365 46
#21 Create a Bell Curve 47
#22 Plotting Employees on a Bell Curve 49
#23 Add Meaning to Reports Using Data Visualizations 51
#24 Use Cutout People to Add Interest to Your Worksheet 54
Bonus Tip: Add Text to the Sign Held by a Cutout Person 56
Bonus Tip: Use a Formula to Toggle Pictures 57
#25 Make an Image Semi-Transparent 59
#26 Save Any Object as an Image 60
#27 Set Up Your Data for Data Analysis 60
Bonus Tip: Use Accounting Underline to Avoid Tiny Blank Columns 60
Bonus Tip: Use Alt+Enter to Control Word Wrap 62
Bonus Tip: Someone went crazy and used Alt+Enter Too Much 63
#28 Sort East, Central, and West Using a Custom List 63
#29 Sort Left to Right 64
#30 Sort Subtotals 65
Bonus Tip: Fill in a Text Field on the Subtotal Rows 68
Bonus Tip: An Easier Way to Fill in a Text Field on Subtotal Rows 68
Bonus Tip: Format the Subtotal Rows 69
Bonus Tip: Copy the Subtotal Rows 69
#31 Sort and Filter by Color or Icon 70
#32 Consolidate Quarterly Worksheets 71
#33 Create Your First Pivot Table 73
Bonus Tip: Rearrange fields in a pivot table 75
Bonus Tip: Format a Pivot Table 76
Bonus Tip: Format One Cell in a Pivot Table 77
Bonus Tip: Fill in the Blanks in the Annoying Outline View 78
Bonus Tip: Rearrange Fields Pane 78
#34 Create a Year-over-Year Report in a Pivot Table 79
Bonus Tip: Another Way to Calculate Year-Over-Year 80
#35 Change the Calculation in a Pivot Table 81
Bonus Tip: Why Do Pivot Tables Count Instead of Sum? 81
#36 Find the True Top Five in a Pivot Table 81
#37 Specify Defaults for All Future Pivot Tables 86
Bonus Tip: Change What Drives You Crazy About Excel 87
#38 Make Pivot Tables Expandable Using Ctrl+T 87
Bonus Tip: Use Ctrl+T with VLOOKUP and Charts 89
#39 Replicate a Pivot Table for Each Rep 90
#40 Use a Pivot Table to Compare Lists 92
Bonus Tip: Show Up/Down Markers 93
Bonus Tip: Compare Two Lists by Using Go To Special 94
#41 Build Dashboards with Sparklines and Slicers 95
Bonus Tip: Line Up Dashboard Sections with Different Column Widths 99
Bonus Tip: Use Picture Lookup 100
Bonus Tip: Report Sheer Selections in a Title 102
#42 See Why GETPIVOTDATA Might Not Be Entirely Evil 103
#43 Eliminate VLOOKUP or XLOOKUP with the Data Model 107
Bonus Tip: Count Distinct 109
#44 Compare Budget Versus Actual via Power Pivot 110
Bonus Tip: Portable Formulas 112
Bonus Tip: Text in the Values of a Pivot Table 113
#45 Slicers for Pivot Tables From Two Data Sets 114
#46 Use F4 for Absolute Reference or Repeating Commands 116
Bonus Tip: Use a Named Range Instead of Absolute References 120
#47 Quickly Convert Formulas to Values 121
Bonus Tip: Skip Blanks While Pasting 123
#48 See All Formulas at Once 124
Bonus Tip: Highlight All Formula Cells 125
Bonus Tip: Trace Precedents to See What Cells Flow into a Formula 125
Bonus Tip: See Which Cells Depend on the Current Cell 126
#49 Audit a Worksheet With Spreadsheet Inquire 126
#50 Discover New Functions by Using fx 127
#51 Use Function Arguments for Nested Functions 129
#52 Calculate Nonstandard Work Weeks 131
Bonus Tip: Use WORKDAYINTL for a Work Calendar 133
#53 Turn Data Sideways with a Formula 134
Bonus Tip: Protect Rows with an Array Formula 136
#54 Handle Multiple Conditions in IF 136
Bonus Tip: Use Boolean Logic 137
#55 Troubleshoot VLOOKUP 138
#56 Use a Wildcard in VLOOKUP 141
Bonus Tip: VLOOKUP to Two Tables 141
#57 Replace Columns of VLOOKUP with a Single MATCH 141
#58 Lookup to the Left with INDEX/MATCH 143
#59 Twelve Benefits of XLOOKUP 145
#60 Preview What Remove Duplicates Will Remove 153
#61 Replace Nested IFs with a Lookup Table 154
Bonus Tip: Match the Parentheses 154
#62 Suppress Errors with IFERROR 156
#63 Handle Plural Conditions with SUMIFS 157
#64 Geography, Exchange Rate & Stock Data Types in Excel 158
Bonus Tip: Use Data, Refresh All to Update Stock Data 162
#65 Get Historical Stock History from STOCKHISTORY 163
#66 More Data Types from Wolfram Alpha 164
Bonus Tip: The Data Card is now Expandable and Supports Arrays 164
#67 Getting Historical Weather For a City 165
#68 Create Your Own Data Types Using Power Query 168
#69 IF Based on Installed Language in a Bilingual Worksheet 173
#70 Dynamic Arrays Can Spill 173
#71 Sorting with a Formula 175
#72 Filter with a Formula 176
Bonus Tip: Understanding Array Constants 178
#73 Formula for Unique or Distinct 179
Bonus Tip: Use # "The Spiller" to Refer to All Array Results 180
#74 Other Functions Can Now Accept Arrays as Arguments 180
#75 One Hit Wonders with UNIQUE 180
#76 SEQUENCE inside of other Functions such as IPMT 181
#77 Replace a Pivot Table with 3 Dynamic Arrays 182
Bonus Tip: Replace Ctrl+Shift+Enter with Dynamic Arrays 182
#78 Dependent Validation using Dynamic Arrays 183
#79 Complex Validation Using a Formula 183
#80 Use A2:INDEX() as a Non-Volatile OFFSET 185
#81 Subscribe to Microsoft 365 for Monthly Features 186
#82 Performance Improvements in 2020 for Microsoft 365 187
#83 Unhide Multiple Worksheets 188
#84 Write Your Data with the Action Pen 188
#85 Many Task Panes Now Collapse into a Tab Strip 189
#86 How to Provide Usable Feedback to the Excel Team 190
#87 Date Tricks in Excel 190
#88 Use the LET Function to Re-Use Variables in a Formula 190
#89 Store Complex Formula Logic in LAMBDA function 192
Bonus Tip: Perform a Loop with a Recursive LAMBDA 194
Bonus Tip: Branching in a LAMBDA 199
Bonus Tip: Using LAMBDA to return a Picture 200
#90 Find Largest Value That Meets One or More Criteria 201
Bonus Tip: Concatenate a Range by Using TEXTJOIN 202
#91 Less CSV Nagging and Better AutoComplete 202
#92 Speed Up VLOOKUP 202
#93 Protect All Formula Cells 202
#94 Back into an Answer by Using Goal Seek 203
#95 Do 60 What-lf Analyses with a Sensitivity Analysis 205
Bonus Tip: Create a Data Table from a Blank Cell 207
#96 Find Optimal Solutions with Solver 208
#97 Improve Your Macro Recording 211
Bonus Tip: Use TypeScript to Write Macros for Excel Online 213
#98 Clean Data with Power Query 213
Bonus Tip: Tame the Sequence of Refresh All 217
Bonus Tip: Data Profiling in Power Query 217
#99 Use Fuzzy Match in Power Query 218
#100 Render Excel Data on an iPad Dashboard Using Power BI 223
#101 Build a Pivot Table on a Map Using 3D Maps 224
#102 The Forecast Sheet Can Handle Some Seasonality 226
#103 Perform Sentiment Analysis in Excel 227
#104 Build Org Charts with the Visio Data Visualizer in Excel 227
#105 Fill in a Flash 229
#106 Format as a Façade 230
#107 Show All Open Workbooks in the Windows Taskbar 232
#108 Surveys & Forms in Excel 233
#109 Use the Windows Magnifier 234
#110 Word for Excellers 235
Bonus Tip: Merge Shapes 237
Bonus Tip: Use the Eye Dropper 237
#111 Avoid Whiplash with Speak Cells 238
Bonus Tip: Provide Feedback with Sound 239
Bonus Tip: A Great April Fool's Day Trick 239
#112 Customize the Quick Access Toolbar 240
Bonus Tip: Sometimes, You Don't Want the Gallery 243
Bonus Tip: Show QAT Below the Ribbon 248
#113 Create Your Own QAT Routines Using VBA Macros 248
Bonus Tip: Settings in the Excel Options Menu 250
#114 Favorite Keyboard Shortcuts 251
#115 Ctrl+Click to Unselect Cells 257
#116 Collapse the Search Box 257
#117 More Excel Tips 258
#118 Excel Stories 259
#119 Excel Function Quick Reference 266
#120 Index 275
About the Author
Bill Jelen is the host ofMrExcel.com and the author of 61 books about Microsoft Excel including Excel Gurus Gone Wild and Excel 2019 Inside Out for Microsoft Press. He has made over 80 guest appearances on TV's The Lab with Leo I Call for Help with Leo Laporte and was voted guest of the year on the Computer America radio show. He writes the Excel column for Strategic Finance magazine. He has produced over 2,300 episodes of his daily video podcast Learn Excel from MrExcel.
About the Illustrators
Cartoonist Bob D'Amico creates custom cartoons for business and more. See cartoonbob.com for more about his work.
George Berlin is all about delight and wonder! He puts a smile on the world's faces with illustration, animation, and interactive projection art. See more at georgeberlin.com.
Walter Moore is famous for his ape cartoons. If you need an illustration of the monkey business at your work, search Bing for Walter Moore Apes.
Bobby Rosenstock is a print maker who specializes in woodcut and letterpress printing. He is owner of the letterpress and design studio in Marietta, Ohio, Just a Jar Design Press - justAjar.com.
Chad Thomas is an illustrator who showcases his artwork on his website, whiterabbitart.com. His colorful and detailed artwork ranges from pet and people portraits to illustrations for children's books.
Foreword
After 17 years on the road, performing 35 Power Excel seminars a year, I had decided during 2019 to retire from the travel circuit. Little did I know that the pandemic would soon cancel all of the 2020 events. As I write this, we are hopeful that the vaccines work and we can finally unmask for 2021. In the meantime, I have booked a few online-only webinars. Check the MrExcel.com homepage for upcoming events.
The book you are reading was the book that I used in those seminars. I would update the book for a new edition of Excel, print 5000 copies and hit the road. For 2020, this book was produced as an e-book only. For 2021, we are back to print. See what is new in each edition on the last page of the book: "What is New in this 2021 Edition" on page 282.
You will see a number of shortlinks in this book in the format mrx.cl/short. The idea is that it will be easier for you to type mrx.cl than a long URL.
Sample File Downloads
The files used in this book are available for download from mrx.cl/2021 bookfiles.
Edge Index
The numbers in the color blocks on the right side of the numbered pages form an edge index in the printed book. The numbers roughly correspond to: 0 is front matter, 1 is favorite techniques, 2 is charting and visualization, 3 is data analysis, 4 is formulas, 5 is tools, and 6 is the index and back matter.