MrExcel 2021 by Jelen, Bill -- Read -- Imperial Library of Trantor

Index

About the Author About the Illustrators Foreword Sample File Downloads #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

#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 People to Add Interest to Your Worksheet

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 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

Bonus Tip: Understanding Array Constants

#73 Formula for Unique or Distinct

Bonus Tip: Use # "The Spiller" to Refer to All Array Results

#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

Bonus Tip: Replace Ctrl+Shift+Enter with 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

Bonus Tip: Perform a Loop with a Recursive LAMBDA Bonus Tip: Branching in a LAMBDA Bonus Tip: Using LAMBDA to return a Picture

#90 Find Largest Value That Meets One or More Criteria

Bonus Tip: Concatenate a Range by Using TEXTJOIN

#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

Bonus Tip: Create a Data Table from a Blank Cell

#96 Find Optimal Solutions with Solver #97 Improve Your Macro Recording

Bonus Tip: Use TypeScript to Write Macros for Excel Online

#98 Clean Data with Power Query

Bonus Tip: Tame the Sequence of Refresh All Bonus Tip: Data Profiling in 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

Bonus Tip: Merge Shapes Bonus Tip: Use the Eye Dropper

#111 Avoid Whiplash with Speak Cells

Bonus Tip: Provide Feedback with Sound Bonus Tip: A Great April Fool’s Day Trick

#112 Customize the Quick Access Toolbar

Bonus Tip: Sometimes, You Don't Want the Gallery Bonus Tip: Show QAT Below the Ribbon

#113 Create Your Own QAT Routines Using VBA Macros

Bonus Tip: Settings in the Excel Options Menu

#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 #120 Index

← Prev
Back
Next →

← Prev
Back
Next →