Excel Productivity Tips

Introduction

• Purpose: Introduce lesser-known Excel tools to improve workflow.
• Favorite Tool: The speaker’s favorite is tool number three.
• Example File: Available via link in video description.

Grouping Columns and Rows

• Common Usage: Hiding columns to increase workspace.
• Grouping:
  • Select columns/rows, go to the Data tab, click Group.
  • Allows quick hide/unhide of columns/rows.
  • Multiple and nested groups possible (up to 8 groups).
  • Collapsible buttons for quick expand/collapse.
  • Ungrouping: Select columns/rows and click Ungroup.
• Homework: Practice grouping rows.

Paste Special Tools

• Paste Special Dialog Box: Contains many useful but often overlooked tools.
• Examples:
  1. Converting Negative to Positive:
     • Formula: Type -1 in a cell, copy it, select values, Paste Special, Multiply.
  2. Converting Values to Percentages:
     • Formula: Type 1% in a cell, copy it, select values, Paste Special, Multiply.
  3. Copying Only Values (Skip Blanks):
     • Select values, Ctrl + C, select destination, Paste Special, Skip Blanks.
• Homework: Explore other Paste Special options.

Storing Multiple Items in Clipboard

• Standard Clipboard: Cleared after each copy/paste action.
• Enhanced Clipboard:
  • Home tab -> Clipboard group -> Open Clipboard icon.
  • Stores up to 24 items, can be from any app.
  • Uses: Quick pasting of multiple items without re-copying.
  • Management: Delete items individually or clear all.
  • Options: Open automatically with Ctrl + C (pressed twice).
• Windows Clipboard:
  • Open with Win + V.
  • Stores copied items, supports pinning.
  • Contains symbols, emojis, kaomojis, GIFs.

Hiding Sheets

• Standard Hiding: Right-click -> Hide (can be easily unhidden).
• Protecting Workbook Structure:
  • Review tab -> Protect Workbook, add password.
  • Prevents hide/unhide.
• Very Hidden Sheets:
  • Developer tab -> Visual Basic editor -> Properties -> Change visibility to "Very Hidden".
  • Unhide by reversing above steps.

Data Interpretation Tips

• Year-on-Year Percentage Change: Use symbols to indicate changes.
• Custom Number Format:
  • Insert symbols via Insert tab -> Symbol.
  • Copy symbols, apply custom number format with symbols for positive/negative values.
• Conditional Formatting:
  • Use to color code variances (green for positive, purple for negative for accessibility).
  • Use icon sets for up/down indicators (edit rules for accuracy).
• Custom Format in Charts: Symbols can be used in chart labels (not applicable with conditional formatting).

Conclusion

• Additional Resources: Comprehensive guide and cheat sheet available for custom number formats.
• Next Steps: Video on formulas in conditional formatting recommended for further learning.