Über die Autorin bzw. den Autor

Auszug. © Genehmigter Nachdruck. Alle Rechte vorbehalten.

MrExcel LX - The Holy Grail of Excel Tips

Holy Macro! Books

Contents

Dedication, 
About the Author, 
About the Contributors, 
Foreword, 
#1 Double-Click the Fill Handle to Copy a Formula, 
#2 Break Apart Data, 
#3 Filter by Selection, 
Bonus Tip: Filter by Selection for Numbers Over/Under, 
#4 Bonus Tip: Total the Visible Rows, 
#5 The Fill Handle Does Know 1, 2, 3..., 
Bonus Tip: Fill Jan, Feb, ..., Dec, Total, 
#6 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, 
#7 Use Default Settings for All Future Workbooks, 
Bonus Tip: Changes to Book Template are Cumulative, 
Bonus Tip: Replace the Comma Style in Book.xltx, 
#8 Recover Unsaved Workbooks, 
#9 Simultaneously Edit the Same Workbook in Office 365, 
Bonus Tip: AutoSave is Necessary, But Turn it Off When Not Co-Authoring, 
Bonus Tip: Undo an AutoSave, 
#10 New Threaded Comments Allow Conversations, 
Bonus Tip: Old Style Comments Are Available as Notes, 
Bonus Tip: Add a Tooltip to a Cell with Validation, 
#11 Create Perfect One-Click Charts, 
#12 Paste New Data on a Chart, 
#13 Create Interactive Charts, 
#14 Show Two Different Orders of Magnitude on a Chart, 
#15 Create Waterfall Charts, 
#16 Create Funnel Charts in Office 365, 
#17 Create Filled Map Charts in Office 365, 
#18 Create a Bell Curve, 
#19 Plotting Employees on a Bell Curve, 
#20 Add Meaning to Reports Using Data Visualizations, 
#21 Sort East, Central, and West Using a Custom List, 
#22 Sort Left to Right, 
#23 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, 
#24 Sort and Filter by Color or Icon, 
#25 Consolidate Quarterly Worksheets, 
#26 Get Ideas from Artificial Intelligence, 
#27 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, 
#28 Create a Year-over-Year Report in a Pivot Table, 
Bonus Tip: Another Way to Calculate Year-Over-Year, 
#29 Change the Calculation in a Pivot Table, 
Bonus Tip: Why Do Pivot Tables Count Instead of Sum?, 
#30 Find the True Top Five in a Pivot Table, 
#31 Specify Defaults for All Future Pivot Tables, 
Bonus Tip: Change What Drives You Crazy About Excel, 
#32 Make Pivot Tables Expandable Using Ctrl+T, 
Bonus Tip: Use Ctrl+T with VLOOKUP and Charts, 
#33 Replicate a Pivot Table for Each Rep, 
#34 Use a Pivot Table to Compare Lists, 
Bonus Tip: Show Up/Down Markers, 
Bonus Tip: Compare Two Lists by Using Go To Special, 
#35 Build Dashboards with Sparklines and Slicers, 
Bonus Tip: Make Your Workbook into a Web App, 
Bonus Tip: Line Up Dashboard Sections with Different Column Widths, 
Bonus Tip: Use Picture Lookup, 
Bonus Tip: Report Slicer Selections in a Title, 
#36 See Why GETPIVOTDATA Might Not Be Entirely Evil, 
#37 Eliminate VLOOKUP with the Data Model, 
Bonus Tip: Count Distinct, 
#38 Compare Budget Versus Actual via Power Pivot, 
Bonus Tip: Portable Formulas, 
Bonus Tip: Text in the Values of a Pivot Table, 
#39 Slicers for Pivot Tables From Two Data Sets, 
#40 Use F4 for Absolute Reference or Repeating Commands, 
Bonus Tip: Use a Named Range Instead of Absolute References, 
#41 Quickly Convert Formulas to Values, 
Bonus Tip: Skip Blanks While Pasting, 
#42 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, 
#43 Audit a Worksheet With Spreadsheet Inquire, 
#44 Discover New Functions by Using fx, 
#45 Use Function Arguments for Nested Functions, 
#46 Calculate Nonstandard Work Weeks, 
Bonus Tip: Use WORKDAY.INTL for a Work Calendar, 
#47 Turn Data Sideways with a Formula, 
Bonus Tip: Protect Rows with an Array Formula, 
#48 Handle Multiple Conditions in IF, 
Bonus Tip: Use Boolean Logic, 
#49 Troubleshoot VLOOKUP, 
#50 Use a Wildcard in VLOOKUP, 
Bonus Tip: VLOOKUP to Two Tables, 
#51 Replace Columns of VLOOKUP with a Single MATCH, 
#52 Use the Fuzzy Lookup Tool from Microsoft Labs, 
#53 Lookup to the Left with INDEX/MATCH, 
#54 Preview What Remove Duplicates Will Remove, 
#55 Replace Nested IFs with a Lookup Table, 
Mini Bonus Tip: Match the Parentheses, 
#56 Suppress Errors with IFERROR, 
#57 Handle Plural Conditions with SUMIFS, 
#58 Geography & Stock Data Types in Excel, 
Bonus Tip: Use Data, Refresh All to Update Stock Data, 
#59 Dynamic Arrays Can Spill, 
#60 Sorting with a Formula, 
#61 Filter with a Formula, 
Bonus Tip: Understanding Array Constants, 
#62 Formula for Unique or Distinct, 
Bonus Tip: Use # "The Spiller" to Refer to All Array Results, 
#63 Other Functions Can Now Accept Arrays as Arguments, 
#64 One Hit Wonders with UNIQUE, 
#65 SEQUENCE inside of other Functions such as IPMT, 
#66 Replace a Pivot Table with 3 Dynamic Arrays, 
Bonus Tip: Replace Ctrl+Shift+Enter with Dynamic Arrays., 
#67 Dependent Validation using Dynamic Arrays, 
#68 Complex Validation Using a Formula, 
#69 Use A2:INDEX() as a Non-Volatile OFFSET, 
#70 Subscribe to Office 365 for Monthly Features, 
Bonus Tip: Concatenate a Range by Using TEXTJOIN, 
#72 Less CSV Nagging and Better AutoComplete, 
#73 Speed Up VLOOKUP, 
#74 Protect All Formula Cells, 
#75 Back into an Answer by Using Goal Seek, 
#76 Do 60 What-If Analyses with a Sensitivity Analysis, 
Bonus Tip: Create a Data Table from a Blank Cell, 
#77 Find Optimal Solutions with Solver, 
#78 Improve Your Macro Recording, 
#79 Clean Data with Power Query, 
#80 Render Excel Data on an iPad Dashboard Using Power BI, 
#81 Build a Pivot Table on a Map Using 3D Maps, 
#82 The Forecast Sheet Can Handle Some Seasonality, 
#83 Perform Sentiment Analysis in Excel, 
#84 Fill in a Flash, 
#85 Format as a Façade, 
#86 Word Cloud using Custom Visuals in Excel, 
#87 Surveys & Forms in Excel, 
#88 Use the Windows Magnifier, 
Bonus Tip: Merge Shapes, 
Bonus Tip: Use the Eye Dropper, 
#90 Avoid Whiplash with Speak Cells, 
Bonus Tip: Provide Feedback with Sound, 
Bonus Tip: A Great April Fool's Day Trick, 
#91 Customize the Quick Access Toolbar, 
Bonus Tip: Sometimes, You Don't Want the Gallery, 
Bonus Tip: Show QAT Below the Ribbon, 
#92 Create Your Own QAT Routines Using VBA Macros, 
Bonus Tip: Settings in the Excel Options Menu, 
#93 Favorite Keyboard Shortcuts, 
#94 Ctrl+Click to Unselect Cells, 
#95 More Excel Tips, 
Appendix: Excel Stories, 
Index,