23 Useful Google Sheets Tips and Tricks

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.

Source



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.

  1. Within Sheets, click Insert menu

  2. Click “Forms

  3. Create any set of questions and parameters

  4. 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.

  1. Type your URL into a cell (with “http://” or “https://” in front of it)