MrExcel Publishing
Your One Stop for Excel Tips & Solutions

MrExcel LIVe


April 2018 - by Bill Jelen

MrExcel LIVe

The 54 Greatest Excel Tips of All Time

A book for people who use Excel 40+ hours per week. Illustrated in full color.

Bill Jelen, MrExcel

category: Excel
covers: Office 365

Product Details
  • Paperback: 178 Pages
  • Publisher: Holy Macro! Books
  • Print ISBN: 978-1-61547-056-3
  • PDF ISBN: 978-1-61547-239-0
  • ePub ISBN: 978-1-61547-362-5
  • Mobi (Kindle) ISBN: 978-1-61547-127-0

I keep a spreadsheet that lists the books I‘ve authored. A few years ago I realized I would soon be writing book #40 and that the =ROMAN(40) is XL. Because of this joke, the MrExcel XL book was born. It became a staple at my live Power Excel seminars. For this updated second edition, I am going with Excel 54 (which will be known as Excel LIVe—LIV because it is the ROMAN(54) and the final e to point out that this is the book I use in my LIVE seminars.

While the first edition filled the extra pages with jokes, cocktails and other mirth, this updated edition includes more Excel tips and techniques. I kept the color printing. And yes, if you were an Indiegogo contributor to put your name in the first book, it will still be in this book.

In addition to the 54 Techniques, this book includes:

  • 54 keyboard shortcuts
  • 52 bonus tips squeezed in between other tips
  • New content including Parsing data, Status bar statistics, INDIRECT, better Comma Style, Interactive charts, combo charts, funnel charts, filled map charts, data visualizations, pivot table defaults, lining up dashboard sections, picture lookup, skip blanks, formula auditing, easier nested functions, wildcard in VLOOKUP, MAXIFS, TEXTJOIN, Co-Authoring, Macro recording tips, clean data with Power Query, Dashboard on an iPad using Power BI, Sentiment Analysis, provide feedback with sound.

The spreadsheet in 2018 has a promising future. Yes, Excel is again facing competition from Google Docs and Tableau. But that competition brings innovation. Look at the amazing new features, like Power Query, Power BI, 3D Maps, and awesome new functions like TEXTJOIN, MAXIFS, and others. It is another golden age for spreadsheet development.

  • 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. Quickly Convert Formulas to Values
      • Bonus Tip: Skip Blanks While Pasting
    28. 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
    29. Discover New Functions by Using fx
      • Bonus Tip: Use Function Arguments for Nested Functions
    30. Calculate Nonstandard Work Weeks
      • Bonus Tip: Use WORKDAY.INTL for a Work Calendar
    31. Turn Data Sideways with a Formula
      • Bonus Tip: Protect Rows with an Array Formula
    32. Handle Multiple Conditions in IF
      • Bonus Tip: Use Boolean Logic
    33. 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
    34. Replace Nested IFs with a Lookup Table
      • Mini Bonus Tip: Match the Parentheses
    35. Suppress Errors with IFERROR
    36. Handle Plural Conditions with SUMIFS
    37. Cure Triskaidekaphobia with a Killer Formula
    38. Extract Unique Values with a CSE Formula
    39. Use A2:INDEX() as a Non-Volatile OFFSET
    40. 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
    41. Speed Up VLOOKUP
    42. Protect All Formula Cells
    43. Back into an Answer by Using Goal Seek
    44. Do 60 What-If Analyses with a Data Table
      • Bonus Tip: Create a Data Table from a Blank Cell
    45. Find Optimal Solutions with Solver
    46. Improve Your Macro Recording
    47. Clean Data with Power Query
    48. Render Excel Data on an iPad Dashboard Using Power BI
    49. Build a Pivot Table on a Map Using 3D Maps
    50. Perform Sentiment Analysis in Excel
    51. Fill in a Flash
    52. Format as a Façade
    53. Word for Excellers
      • Bonus Tip: Merge Shapes
      • Bonus Tip: Use the Eye Dropper
    54. Avoid Whiplash with Speak Cells
      • Bonus Tip: Provide Feedback with Sound
      • Bonus Tip: A Great April Fool’s Day Trick
    55. #55 More Excel Tips
  • Part 2: 54 Keyboard Shortcuts
  • Part 3 - Excel Stories

Where to Buy

MrExcel Store

We will ship you a physical copy of the book, plus you can immediately download the e-book in any of three DRM-Free versions: PDF, ePub, or Mobi.

Print Book from MrExcel »

MrExcel Store

Outside of the USA, purchase the e-book. Again, you can choose from any of 3 DRM-Free editions when you purchase from:

e-Book from MrExcel »

Amazon.com

Print Book from Amazon:

Print Book from Amazon »

Other Editions

Related Products