Excel 2016: 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