Learn the advanced functions, options and shortcuts, and make the most out of your Google sheets with these useful tips and tricks.
Google Sheets is a collaborative, cloud-based tool where you can create, edit and access spreadsheet wherever you are from your phone, tablet or computer even when there’s no connection. For free. It also works seamlessly with Microsoft Excel.
There’s much more to Google Sheets. We’ll be looking on how to use google sheets shortcuts, formulas, features, and advanced tips.
Shortcut Tips
Cleaning Tips
Data Import Tips
Tips with Advanced Features
Collaborating and Sharing Tips
Calculation and Charting Tips
Formatting Tips
Shortcut Tips
1. How to create a new spreadsheet?
Save time in opening Google Sheets directly into your browser's address bar by just typing:
sheet.new
sheets.new
spreadsheet.new
2. How to quickly add current time and/or date?
There is a long list of keyboard shortcuts in Google Sheets. But here is the basic on how you can insert current date and/or time wherever you want.
To add the date: Ctrl + ; (semicolon key)
To add the time: Ctrl + Shift + ; (semicolon key)
To add the date and time together: Ctrl + Shift + Alt + ; (semicolon key)
3. What formatting cells shortcuts are worth remembering?
Ctrl + Shift + 1 : Format as decimal
Ctrl + Shift + 2 : Format as time
Ctrl + Shift + 3 : Format as date
Ctrl + Shift + 4 : Format as currency
Ctrl + Shift + 5 : Format as percentage
Ctrl + Shift + 6 : Format as exponent
4. How to create your own personalised shortcut within Google Sheets?
Use Macros feature. It records a series of steps that you’d like Google Sheets to repeat on command. If you work with a new data set each month and need to manipulate that data in the same sequence each time, then you can record a Macro and have Google do the work for you.
Open the Tools menu, select “Macros”
Select “Record macro”
Select “Use absolute references” if you want the shortcut to always be performed on the same specific cells. Otherwise, select “Use relative references.”
Then perform whatever actions you want to record.
Cleaning Tips
5. How to delete extra spaces before or after data in your spreadsheet?
Google Sheets function TRIM can give you cleaner version of the cell's value, whether you’re looking at numbers or text.
Specific cell value: =TRIM(A2)
Multiple cells at once on range you need: =ArrayFormula(TRIM(A2:A14))
6. How to determine if email addresses are all properly formatted?
Google Sheets can look through email addresses and determine if they are all properly formatted.
Specific cell: ISEMAIL(A3)
Range of cells: =ArrayFormula(ISEMAIL(A2:A50))
Sheets will give you a “TRUE” or “FALSE” answer if email address is valid or not.
7. How to validate URLs in a spreadsheet?
Make sure you don’t have any improper items in your list of URLs with the previous tip but use the function ISURL instead.
8. How to standardised case formatting?
If you have a lots of data with improper or/and random capitalisation, sheets can standardised the case for you.
All of the text uppercase: UPPER(A1)
All of the text lowercase: LOWER(A1)
Capitalise the first letter of each word for a title-case effect: PROPER(A1)
Data Import Tips
9. How to collect data in a survey-style form then compile the results in a spreadsheet?
Google Forms lets you collect data with any set of questions and parameters you want, and then compile the results to Google Sheets.
Within Sheets, click Insert menu
Click “Forms”
Create any set of questions and parameters
When your form is ready, click the “Send” button in the upper-right corner of the page to email it to anyone
As responses come in, they’ll automatically appear in your spreadsheet as their own individual rows.
10. How to import a range of cells from a specified spreadsheet?
Copy the full URL of the sheet with the data and paste it into Sheets’ IMPORTRANGE function, using the following
IMPORTRANGE (URL, sheet number, and cell range in place): =IMPORTRANGE( )
Example:
IMPORTRANGE("https: //docs. google. com/spreadsheets/d/123abCderjvjjdiruiwghdGdikzkjjc/edit", "G Suite!A1:D21")
You’ll have to click a button to allow the two sheets to be connected.
11. How to import data from a table or list within an HTML page?
Pull in data from any publicly available web page with Google Sheet. The page should have a properly formatted table.
IMPORTHTML (URL you need and the number indicating which table on the page you want to import)
Example:
IMPORTHTML("http: //en. wikipedia. org/wiki/Demographics_of_India", "table", 4)
12. How to import an RSS feed?
Import recent entries from a website’s RSS feed into any spreadsheet.
IMPORTFEED (URL of the feed you want)
Example:
IMPORTFEED("http:// news. google. com/?output=atom", "items", FALSE, 10)
Tips with Advanced Features
13. How to create QR codes?
Create QR codes with Google Sheets that’ll pull up whatever URLs you want when they’re scanned.
Type your URL into a cell (with “http://” or “https://” in front of it)
Use =image(“https://chart.googleapis.com/chart?chs=150×150&cht=qr&chl=”&A2) With your own cell number in place of “A2”
The QR code will instantly appear and be available to copy and share.
14. How to identify any language used in a spreadsheet?
Sheets can identify and translate different languages.
DETECTLANGUAGE(text_or_range)
Examples:
=DETECTLANGUAGE(A1)
=DETECTLANGUAGE(“hola”)
Google will give you a two-letter code telling you the language that was used. To translate text from one language into another.
GOOGLETRANSLATE(text, [source_language, target_language])
Examples:
GOOGLETRANSLATE("Hello World","en","es")
GOOGLETRANSLATE(A2)
Collaborating and Sharing Tips
15. How to let anyone with access quickly copy your spreadsheet into their own Google account?
After you have shared a spreadsheet publicly or with a specific people, you can create a custom link to let anyone use it as a template or just wanted to modify it without affecting your original version.
Copy the URL in your browser’s address bar while you’re viewing the spreadsheet.
Replace the word “edit” at the end with “copy”.
Example:
https:// docs .google.com/spreadsheets/d/123ajdDJHFbbghgDnKDjh/copy
They will immediately be prompted to make a copy with a single click.
16. How to give people a direct link to download your data as a PDF?
Copy your spreadsheet’s URL
Replace the word “edit” at the end with “export?format=pdf”
Example:
https: //docs .google. com/spreadsheets/d/123ajdDJgjgmgHFDnKDjh/export?format=pdf
They will instantly be presented with a PDF export as soon as they open the link.
Calculation and Charting Tips
17. How to do fast calculations in any number-oriented spreadsheet?
By highlighting a range of cells, you will see in the lower-right corner of the screen the sum of the numbers you selected.
Click the box and it will show the average, the minimum or maximum, or the total count of numbers involved.
18. How to do different types of data analysis and create complex charts?
Using Google’s artificial intelligence, Sheet can analyse and create complex charts.
Click “Explore” icon in the lower-right corner of the screen
Sheets will pop up a panel of info related to your data
Highlight specific rows in your spreadsheet to change its focus, and you can find options for adjusting it or inserting it directly into your sheet.
Formatting Tips
19. How to hide any row?
Click its number in the gray column at the far left of the screen
Select “Hide row” from the menu that appears
When you want to show the row again, click the black arrows that appeared in its place within that same left-of-screen column.
20. How to apply a color pattern to your rows?
Click Sheets’ Format menu
Look for the “Alternating colors”
It’ll give you a simple set of sharp-looking color options.
21. How to rotate the text in your header row?
Highlight the row.
Click on the "Text Rotation" tool with an icon that shows an angled “A” with an up-pointing arrow beneath it.
Pick from several effects that will appear.
It will set your header text apart and give your spreadsheet polished look.
22. How to insert functional checklist right within a spreadsheet?
Select a series of blank cells
Open the Insert menu
Select “Tick box.”
You can use this to put your to-do items in the next column over and get to check items off as you complete them.
23. How to freeze or unfreeze rows or columns?
To put the data in the same place and you'll able to see it when you scroll, you can freeze rows or columns.
Select a row or column you want to freeze or unfreeze.
At the top, click View > Freeze.
Select how many rows or columns to freeze.
To unfreeze, select a row or column. Then, at the top, click View > Freeze > No rows or No columns.
These are some cool stuffs and tips that Google Sheet can do to make our life with lots of data easier!
Let us know what you think in this new update in our Socials!
Tags:
#GoogleSheets #GoogleForms #Tips #Tricks #Hacks #Formula #Shortcut #HowTo #AdvancedTips #Features #WorkFromAnyWhere #iTEASPOON #GSuiteTips #GSuite #Google #Update #Computer #Work #Organisation #Files
We want to help G Suite users do more with G Suite. We are providing a free resource of updates, quality tips and tricks for the G Suite Business and Education Communities.