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

Index

Dedication About the Author About the Contributors Foreword Introduction Part 1: The Top 54 Tips #1 Double-Click the Fill Handle to Copy a Formula #2 Break Apart Data #3 Filter by Selection

Bonus Tip: Total the Visible Rows

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

#5 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: Create a SUM That Spears Through All Worksheets Bonus Tip: Use INDIRECT for a Different Summary Report

#6 Use Default Settings for All Future Workbooks

Bonus Tip: Understand that Changes are Cumulative Bonus Tip: Replace the Comma Style in Book.xltx

#7 Recover Unsaved Workbooks #8 Create Perfect One-Click Charts #9 Paste New Data on a Chart

Bonus Tip: Create Interactive Charts Bonus Tip: Show Two Different Orders of Magnitude on a Chart

#10 New in Excel 2016: Create Waterfall Charts and More

Bonus Tip: Create Funnel Charts in Office 365 Bonus Tip: Create Filled Map Charts in Office 365

#11 Add Meaning to Reports Using Data Visualizations #12 Sort East, Central, and West Using a Custom List #13 Sort Left to Right

Bonus: Another Way to Move Columns

#14 Sort Subtotals

Bonus Tip: Fill in a Text Field on the Subtotal Rows Bonus Tip: Format the Subtotal Rows Bonus Tip: Copy the Subtotal Rows

#15 Sort and Filter by Color or Icon #16 Consolidate Quarterly Worksheets #17 Create a Year-over-Year Report in a Pivot Table

Bonus Tip: Change the Calculation in a Pivot Table

#18 Find the True Top Five in a Pivot Table #19 Specify Defaults for All Future Pivot Tables

Bonus Tip: Change What Drives You Crazy About Excel Bonus Tip: Make Pivot Tables Expandable Using Ctrl+T Bonus Tip: Use Ctrl+T with VLOOKUP and Charts

#20 Replicate a Pivot Report for Each Rep #21 Use a Pivot Table to Compare Lists

Bonus Tip: Show Up/Down Markers Bonus Tip: Compare Two Lists by Using Go To Special

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

#23 See Why GETPIVOTDATA Might Not Be Entirely Evil #24 Eliminate VLOOKUP with the Data Model

Bonus Tip: Count Distinct

#25 Compare Budget Versus Actual via Power Pivot

Bonus Tip: Portable Formulas

#26 Use F4 for Absolute Reference or Repeating Commands

Bonus Tip: Use a Named Range Instead of Absolute References

#27 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 Bonus Tip: Audit a Worksheet With Spreadsheet Inquire

#28 Discover New Functions by Using fx

Bonus Tip: Use Function Arguments for Nested Functions

#29 Calculate Nonstandard Work Weeks

Bonus Tip: Use WORKDAY.INTL for a Work Calendar

#30 Turn Data Sideways with a Formula

Bonus Tip: Protect Rows with an Array Formula

#31 Handle Multiple Conditions in IF

Bonus Tip: Use Boolean Logic

#32 Troubleshoot VLOOKUP

Bonus Tip: Use a Wildcard in VLOOKUP Bonus Tip: Replace Columns of VLOOKUP with a Single MATCH Bonus Tip: Lookup to the Left with INDEX/MATCH

#33 Replace Nested IFs with a Lookup Table

Mini Bonus Tip: Match the Parentheses

#34 Suppress Errors with IFERROR #35 Handle Plural Conditions with SUMIFS #36 Cure Triskaidekaphobia with a Killer Formula #37 Extract Unique Values with a CSE Formula #38 Use A2:INDEX() as a Non-Volatile OFFSET #39 Subscribe to Office 365 for Monthly Features

Bonus Tip: Find Largest Value That Meets One or More Criteria Bonus Tip: Concatenate a Range by Using TEXTJOIN Bonus Tip: Get Small Changes In Office 365 Bonus Tip: Take Advantage of Co-Authoring in Office 365

#40 Speed Up VLOOKUP #41 Protect All Formula Cells #42 Back into an Answer by Using Goal Seek #43 Do 60 What-If Analyses with a Data Table

Bonus Tip: Create a Data Table from a Blank Cell

#44 Find Optimal Solutions with Solver #45 Improve Your Macro Recording #46 Clean Data with Power Query #47 Render Excel Data on an iPad Dashboard Using Power BI #48 Build a Pivot Table on a Map Using 3D Maps #49 Perform Sentiment Analysis in Excel #50 Fill in a Flash #51 Format as a Façade #52 Word for Excellers

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

#53 Avoid Whiplash with Speak Cells

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

#54 More Excel Tips Part 2: 54 Keyboard Shortcuts Part 3 - Excel Stories

← Prev
Back
Next →

← Prev
Back
Next →