Never bothered learning how to use the advanced formulas and Google Sheet functions in spreadsheet? Fear not, learn how to create dropdown menus, checkboxes, and even org charts without having to memorize a single thing! 🤓
Watch it in action
Tip #1 - Create Filtered View
Don’t want anyone messing with your filters when collaborating on the same document? Here’s what you can do:
Go to the filter icon in the menu tab > click the downward arrow
Select “Create new filter view”
Give your filter a name and choose what you want to filter out
ONLY you will be able to see this filtered view so this won’t affect your colleagues.
Pro Tip: If you want to share the filtered view with a colleague, send them the URL with the filtered view enabled so they are brought to it directly
Take it a step further by filtering your columns by color:
Highlight the respective column > go to Format > Conditional formatting
Select Format cells if “is empty” > select a color > click Done
Go back to the filter icon > select All to view all your filtered areas
Click on the dropdown arrow next to a filter > select Filter by color > Fill Color > choose a color
This is useful for when you need to see which tasks you have completed at a glance!
Tip #2 - Link to Custom Range
If you need someone else to input data into Sheets:
Highlight the column/row you need their input for > right click > select “Get link to this range”
Send the copied link to them
Once they open the link, it will take them directly to the highlighted row or column!
Tip #3 - Protect & Lock Ranges
For all you control freaks, no one will mess with your files ever again.
Highlight the columns/rows you want to lock > go to Data in the menu bar
Select “Protected sheets and ranges”
Under the Range tab > click “Set permissions” > uncheck the emails of the other collaborators > click Done
And if you came across this tip too late and the damage is already done? Read on.
Tip #4 - Show Edit History
To show the changes that have been made:
Right click on the cell > click “Show edit history”
This shows you every single edit that has been made…ever.
Tip #5 - Create an Org Chart in Google Sheets
Save tons of time by creating org charts directly in Sheets instead of Powerpoint or Slides!
For example, if you already have the “Report” and “Manager” columns inputted into sheets:
Highlight the two columns > go the Insert in the menu bar > select Chart
Under Chart type, select the Organizational chart
Under the Setup tab, enable “Use row 2 as headers”
Under the Customize tab, you can choose to change the node colors
Pro Tip: Copy the chart from Sheets and paste it directly into Google Slides (make sure to “Link to spreadsheet”) for the option to update the chart automatically within the slides
Tip #6 - Turn Off Notifications
To turn off annoying notifications every time someone edits a shared document:
Go to notification history icon in the top right corner > click Notifications > select “Only yours”
Now you’ll only be notified if someone mentions you in a comment!
Tip #7 - Top Formatting Tips
Enable alternating colors for long lists to make it easier to read:
Go to Format > Alternating colors > pick a color you like
Group and color-code tabs by their functions:
Right click on a tab > select “Change color”
Quickly enable a yes/no or checkbox option:
Highlight the column > go to Insert > select “Checkbox”
Tip #8 - Copy Tab Quickly
To copy an entire spreadsheet over to a new one without messing up the formatting:
Right click the original tab at the bottom of the page
Select “Copy to” > Existing spreadsheet
Copy the URL of the spreadsheet you want to copy to > paste it and press Select
Now you should have a copy of the spreadsheet in the other document with all the formatting perfectly intact.
Tip #9 - Remove Duplicates
Instead of manually removing duplicates within your sheets:
Highlight the table > go to Data > Remove duplicates
You can choose to select the columns you want to remove duplicates for
Tip #10 - Convert Currencies
You can use the Google Finance function for currency exchange calculations.
For example, if you want to quickly see how much a product will cost from USD to HKD:
Within the cell, type in “=E5*GOOGLEFINANCE(“CURRENCY:USDHKD”)
Super handy if you work with people all over the world!
For more tips on creating sexy spreadsheets…
Check out these 10 best practices for shared spreadsheets and copy a free template here!