Excel: Tips and Tricks

Learn powerful tips, tricks, and shortcuts that can help you take your Excel game to the next level.

Introduction

• Save time in Excel

1. Seven Significant Shortcuts

• Enter data or formulas in nonadjacent cells simultaneously
• Copy data or formulas down a column
• Instantly enter today's date or time
• Convert formulas to values with a simple drag
• Display all worksheet formulas
• Create charts with these keystroke shortcuts
• Zoom in and out quickly

2. Ribbon and Quick Access Toolbar Tips

• Access ribbon commands with Alt key sequences
• Expand and collapse the ribbon and full-screen views

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 and Selection Shortcuts

• Navigate between workbooks and worksheets efficiently
• Navigate within worksheets efficiently
• Open, close, save, and create new workbooks with keystrokes
• Select an entire row, column, region, or worksheet
• Select noncontiguous ranges and visible cells only

5. Data Entry and Editing Shortcuts

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

6. Drag and Drop Techniques

• Accelerate copy and move tasks within cells and worksheets
• Drag and insert cells with the Shift key
• Display the Paste Special options
• Accelerate copy, move, and paste actions with the right mouse button

7. Formula Shortcuts

• Create formulas rapidly
• Select all dependent or precedent cells
• Use AutoSum shortcuts
• Count the number of unique entries
• Use conditional formatting to highlight formula cells
• Perform calculations without formulas

8. Operational Shortcuts

• Insert, delete, hide, and unhide columns and rows
• Realign imported text
• Select and manipulate blank cells
• Collapse and expand detail with group and ungroup buttons
• Create double-spaced or triple-spaced printouts

9. Formatting Shortcuts

• Use keystroke shortcuts for frequently needed numeric formats
• Accentuate data with alignment tools
• Add a color background to every fifth row in a range
• Use conditional formatting based on comparison criteria
• Use special formats for times over 24 hours
• Add and remove strikethrough and borders with keystroke shortcuts
• Display values in thousands or millions without formulas
• Format phone and social security numbers

10. Data Management Techniques

• Clean up spaces with the TRIM function
• Split column data using Text to Columns and Flash Fill
• Join data with the TEXTJOIN function and Flash Fill
• Insure unique entries with data validation rules
• Display unique items from large lists
• Use new rich data types (Office Insiders)

11. Charting and Visual Object Tips

• Manipulate chart placement and sizing 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

12. PivotTable Tips

• Adjust report layouts to allow totals on top or bottom
• Revisit relevant source data with the drill down feature

13. Tiny Tips

• Become a more proficient Excel user with these short tips

Conclusion

• Next steps