Excel 2016: Tips and Tricks

Get more done in Excel 2016. Excel expert Dennis Taylor helps Excel users take their spreadsheet skills to the next level with this collection of tips and tricks.

Introduction

• Welcome
• Using the exercise files

1. Top 10 Shortcuts

• Enter data or formulas in nonadjacent cells simultaneously
• Copy data or formulas down a column instantly
• Instantly enter today's date or time
• Convert formulas to values with a simple drag
• Adjust all or selected column widths or row heights in a flash
• Display all worksheet formulas instantly
• Create charts instantly with either of these keystroke shortcuts
• Display Print Preview instantly
• Undo, redo, and repeat with keystrokes
• Zoom in and out quickly

2. Ribbon and Quick Access Toolbar Tips

• Expand/collapse ribbon and fullscreen views
• Access ribbon commands with Alt key sequences
• Adjust the Quick Access Toolbar

3. Display Shortcuts

• Create split screens and frozen titles in a flash
• Restore missing labels and hide repeating labels
• Customize the display of status bar totals

4. Navigation Shortcuts

• Navigate between workbooks efficiently
• Navigate between worksheets efficiently
• Navigate within worksheets
• Open, close, save, and create new workbooks with keystrokes

5. Selection Shortcuts

• Select entire row, column, region, or worksheet
• Select noncontiguous ranges and visible cells only

6. Data Entry and Editing Shortcuts

• Accelerate data entry
• Enter dates and date series efficiently
• Enter times and time series efficiently
• Use custom lists for rapid data entry and list-based sorting
• Enhance editing tools

7. Drag and Drop Techniques

• Accelerate copy and move tasks
• Accelerate worksheet copying and moving
• Drag and insert cells with the Shift key
• Display Paste Special options instantly
• Accelerate Copy/Move/Paste actions with the right mouse button

8. Formula Shortcuts

• Create formulas rapidly
• Select all cells that depend on the active cell
• Select all cells that can affect the active cell
• Use AutoSum shortcuts
• Use rounding shortcuts
• Generate random numbers
• Count the number of unique entries
• Perform calculations without formulas

9. Operational Shortcuts

• Insert, delete, hide, and unhide columns and rows rapidly
• Re-align imported text
• Select and manipulate blank cells
• Collapse and expand detail quickly with outlining shortcuts
• Create double-spaced or triple-spaced printouts quickly

10. Formatting Shortcuts

• Use keystroke shortcuts for frequently needed numeric formats
• Accentuate data with alignment tools
• Add a color background to every 5th row in a range
• Use conditional formatting based on comparison criteria
• Use conditional formatting to highlight formula cells only
• Create custom formats: Dot leaders and more
• Use special formats for times over 24 hours
• Add/remove strikethrough and borders with keystroke shortcuts
• Quickly format phone and SS numbers

11. Data Management Techniques

• Sort data with single buttons
• Clean up spaces quickly with the TRIM function
• Identify and eliminate duplicates
• Split column data using Text to Columns and Flash Fill
• Join data with the TEXTJOIN function and Flash Fill
• Ensure unique entries with data validation rules
• Force typed dates to be weekdays only with data validation rules
• Display unique items from large lists

12. Charting and Visual Object Tips

• Manipulate chart placement with dragging techniques
• Create chart titles from cell content
• Create and manipulate shapes with the Shift, Ctrl, and Alt keys
• Create linked dynamic and linked static images

13. Ten Tiny Tips

• Become a more proficient Excel user with these short tips

Conclusion

• Goodbye