Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
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 →