Google Sheets Tips and Tricks

Introduction

Whether you're a casual Google Sheets user or a key player in your company, here are 10 incredible tips to enhance your productivity in Google Sheets.

Tips Overview

1. Table Formatting

• Convert Data to Table: Select entire dataset with Cmd/Ctrl + A -> Right click -> Convert to table.
• Rename and Format Table: Rename, change colors, and add columns with specific types like number, currency, or date.

2. Grouping Tasks

• Group Columns: Use the calculator icon to group tasks based on columns like phase or owner.
• Save and Share Views: Save views like 'Owner View' and share URL with colleagues.

3. Filter and Conditional Formatting

• Quick Filter: Right-click on a cell to filter by cell value.
• Conditional Formatting: Right-click and set conditions to highlight cells based on criteria, e.g., color by cell value.

4. Data Cleanup Techniques

• Split Text to Columns: Copy a range and split text based on spaces to separate names.
• Remove Duplicates: Select dataset -> Data cleanup -> Remove duplicates.
• Email and URL Validation: Use IS_EMAIL and IS_URL functions to validate data.

5. File, Event, and People Integration

• Insert Chips: Use the @ symbol to insert files, events, or people that update automatically.
• Data Extraction: Extract file names, event titles, or emails from inserted elements.

6. Organizational Charts

• Insert Organizational Chart: Use two columns to create org charts, customize colors easily.

7. Quick Shortcuts

• New Sheet Shortcut: Type sheets.new in address bar for a new file.
• Navigation and Selection: Use Cmd/Ctrl + Arrow keys for navigation and Shift for selection.
• Percent Formatting Shortcut: Use Option + / to bring up the menu and type 'percent'.

8. Real-Use Example

• Highlight Managers: Use conditional formatting with formula =J4=FALSE to highlight managers who haven't confirmed attendance.

9. Get Link to Range

• Link to Excel Range: Right-click -> More cell actions -> Get link to range for direct navigation by colleagues.

10. Currency Conversion Formula

• Conversion Formula: Use = GoogleFinance("currency:USDHKD") to convert currencies directly in Google Sheets.

Organizational Tips

• Tab Coloring: Bucket related tabs by colors for better organization.
• Divider Tabs: Use empty column tabs for clear indication of no content in the tab.

Further Learning

For those interested in increasing workplace organization, consider the Workspace Academy course for powerful workflows. Explore further Google Docs productivity tips and tricks.