Excel 2013 Tips and Tricks

Introduction

• Welcome
• Using the exercise files

1. Top Ten Shortcuts

• Enter data or formulas in non-adjacent cells simultaneously
• Convert formulas to values with a simple drag
• Copy data or formulas down a column instantly
• Adjust all or selected column widths or row heights in a flash
• Instantly display all worksheet formulas
• Create charts instantly with either of these keystroke shortcuts
• Display a print preview instantly
• Instantly enter today's date or time
• Undo, Redo, and Repeat with keystrokes
• Zoom in and out quickly

2. Ribbon and Quick Access Toolbar Tips

• Expand and collapse the Ribbon and full-screen 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

5. Selection Shortcuts

• Select an entire row, column, or worksheet
• Select noncontiguous ranges and visible cells only
• Select the current region of cells and move around region corners

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
• Enhanced editing tools

7. Drag-and-Drop Techniques

• Accelerate copy-and-move tasks
• Accelerate worksheet copying and moving
• Drag and insert with the Shift key
• Display Paste Special options instantly
• Accelerate Copy, Move, and 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
• Realign 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 date with alignment tools
• Add a color background to every fifth 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 and remove a strikethrough or a border with keystroke shortcuts

11. Database Techniques

• Sort data with single buttons
• Clean up spaces quickly
• Identify and eliminate duplicates
• Split column data into multiple columns with Flash Fill
• Join data from multiple columns into one column with Flash Fill
• Ensure unique entries with data validation rules
• Force typed dates to be only weekdays 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 and use Shift and Ctrl keys
• Create linked dynamic and static images

13. Ten Tiny Tips

• Become more proficient with these short tips

Conclusion

• Next steps