دانلود کتاب MrExcel 2022: Boosting Excel

فهرست مطالب :

Front Cover
Foreword
Sample File Downloads
Edge Index
#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
Bonus Tip: Filter by Selection for Numbers Over/Under
#5 Total the Visible Rows
#6 The Fill Handle Does Know 1, 2, 3…
Bonus Tip: Fill Jan, Feb, ..., Dec, Total
Bonus Tip: Fill 1 to 100,000 in a Flash
#7 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
#8 Use Default Settings for All Future Workbooks
Bonus Tip: Changes to Book Template are Cumulative
Bonus Tip: Replace the Comma Style in Book.xltx
#9 Recover Unsaved Workbooks
#10 Search While Using File Open
Bonus Tip: Pin an Item to the Top of the Recent Files
#11 Show Changes From Last 60 Days
Bonus Tip: Roll Back to a Previous Version of the Workbook
#12 Simultaneously Edit a Workbook in Microsoft 365
Bonus Tip: Avoiding the Veto
Bonus Tip: AutoSave is Necessary, But Turn it Off When Not Co-Authoring
Bonus Tip: Undo an AutoSave
#13 Save Filter & Sorting in Sheet View
#14 New Threaded Comments Allow Conversations
Bonus Tip: Old Style Comments Are Available as Notes
Bonus Tip: Add a Tooltip to a Cell with Validation
Bonus Tip: Data Validation Partial Matching Added in 2022
#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 in Microsoft 365
#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
Bonus Tip: Add Text to the Sign Held by a Person
Bonus Tip: Use a Formula to Toggle Pictures
#26 Make an Image Semi-Transparent
#27 Save Any Object as an Image
#28 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
#29 Sort East, Central, and West Using a Custom List
#30 Sort Left to Right
#31 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
#32 Sort and Filter by Color or Icon
#33 Consolidate Quarterly Worksheets
#34 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: Replace Blank Values Cells With Zero
Bonus Tip: Rearrange Fields Pane
#35 Create a Year-over-Year Report in a Pivot Table
Bonus Tip: Another Way to Calculate Year-Over-Year
#36 Change the Calculation in a Pivot Table
Bonus Tip: Why Do Pivot Tables Count Instead of Sum?
#37 Find the True Top Five in a Pivot Table
#38 Specify Defaults for All Future Pivot Tables
Bonus Tip: Change What Drives You Crazy About Excel
#39 Make Pivot Tables Expandable Using Ctrl+T
Bonus Tip: Use Ctrl+T with VLOOKUP and Charts
#40 Replicate a Pivot Table for Each Rep
#41 Use a Pivot Table to Compare Lists
Bonus Tip: Show Up/Down Markers
Bonus Tip: Compare Two Lists by Using Go To Special
#42 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
#43 See Why GETPIVOTDATA Might Not Be Entirely Evil
#44 Ask Excel\'s A.I. a Question About Your Data
#45 Eliminate VLOOKUP or XLOOKUP with the Data Model
Bonus Tip: Count Distinct
#46 Compare Budget Versus Actual via Power Pivot
Bonus Tip: Portable Formulas
Bonus Tip: Text in the Values of a Pivot Table
#47 Slicers for Pivot Tables From Two Data Sets
#48 Use F4 for Absolute Reference or Repeating Commands
Bonus Tip: Use a Named Range Instead of Absolute References
#49 Quickly Convert Formulas to Values
Bonus Tip: Skip Blanks While Pasting
#50 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
#51 Audit a Worksheet With Spreadsheet Inquire
#52 Discover New Functions by Using fx
#53 Use Function Arguments for Nested Functions
#54 Calculate Nonstandard Work Weeks
Bonus Tip: Use WORKDAY.INTL for a Work Calendar
#55 Turn Data Sideways with a Formula
Bonus Tip: Protect Rows with an Old-Style Array Formula
#56 Handle Multiple Conditions in IF
Bonus Tip: Use Boolean Logic
#57 Troubleshoot VLOOKUP
#58 Use a Wildcard in VLOOKUP or XLOOKUP
Bonus Tip: VLOOKUP to Two Tables
#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 Generating Random Numbers in Excel
#78 Replace a Pivot Table with 3 Dynamic Arrays
Bonus Tip: Replace Ctrl+Shift+Enter with Dynamic Arrays.
#79 Dependent Validation using Dynamic Arrays
#80 Complex Validation Using a Formula
#81 Stack Multiple Arrays
#82 Dropping, Taking, or Choosing from an Array
#83 Reshaping an Array to a Vector and Back
#84 Getting the UNIQUE of a Rectangular Range
#85 Shuffling and Dealing a Deck of Cards
#86 Use A2:INDEX() as a Non-Volatile OFFSET
#87 Subscribe to Microsoft 365 for Monthly Features
#88 Performance Improvements for Microsoft 365
#89 Unhide Multiple Worksheets
#90 Write Your Data with the Action Pen
#91 Many Task Panes Now Collapse into a Tab Strip
#92 How to Provide Usable Feedback to the Excel Team
#93 Date Tricks in Excel
#94 Use the LET Function to Re-Use Variables in a Formula
#95 Store Complex Formula Logic in LAMBDA function
Bonus: Importing LAMBDAs from GitHub
Bonus Tip: Perform a Loop with a Recursive LAMBDA
Bonus Tip: Branching in a LAMBDA
Bonus Tip: Using LAMBDA to return a Picture
#96 New LAMBDA Helper Functions
#97 Find Largest Value That Meets One or More Criteria
Bonus Tip: Concatenate a Range by Using TEXTJOIN
#98 Text Before or After a Specific Delimiter
#99 Split Text into Words Using TEXTSPLIT
#100 Less CSV Nagging and Better AutoComplete
#101 Protect All Formula Cells
#102 Back into an Answer by Using Goal Seek
#103 Do 60 What-If Analyses with a Sensitivity Analysis
Bonus Tip: Create a Data Table from a Blank Cell
#104 Find Optimal Solutions with Solver
#105 Interpolate between a starting and ending number
#106 Improve Your Macro Recording
Bonus Tip: Use TypeScript to Write Macros for Excel Online
#107 Clean Data with Power Query
Bonus Tip: Tame the Sequence of Refresh All
Bonus Tip: Data Profiling in Power Query
#108 Use Fuzzy Match in Power Query
#109 Build a Pivot Table on a Map Using 3D Maps
#110 The Forecast Sheet Can Handle Some Seasonality
#111 Perform Sentiment Analysis in Excel
#112 Build Org Charts with the Visio Data Visualizer in Excel
#113 Fill in a Flash
#114 Format as a Façade
#115 Show All Open Workbooks in the Windows Taskbar
#116 Paste to Another Computer Using Cloud Clipboard
#117 Surveys & Forms in Excel
#118 Use the Windows Magnifier
#119 New Scrolling Tricks
#120 Word for Excellers
Bonus Tip: Merge Shapes
Bonus Tip: Use the Eye Dropper
#121 Avoid Whiplash with Speak Cells
Bonus Tip: Provide Feedback with Sound
Bonus Tip: A Great April Fool’s Day Trick
#122 Customize the Quick Access Toolbar
Bonus Tip: Sometimes, You Don\'t Want the Gallery
Bonus Tip: Show QAT Below the Ribbon
#123 Create Your Own QAT Routines Using VBA Macros
Bonus Tip: Settings in the Excel Options Menu
#124 Favorite Keyboard Shortcuts
#125 Ctrl+Click to Unselect Cells
#126 Collapse the Search Box
#127 More Excel Tips
#128 Excel Stories
#129 Excel Function Quick Reference
Dedication
About the Author
About the Illustrators
Index