27 Useful Google Sheets Tips to Boost Your Productivity

Google Sheets is an incredibly powerful spreadsheet tool with numerous fascinating features that many users may not have fully explored. Whether you are just starting out or are familiar with Google Sheets, this article will elevate your skills to a whole new level.

The 27 tips below are just the beginning; Google Sheets holds much more than you might imagine. Without further ado, let’s explore the first set of tips.

Save Time with Google Sheets Shortcuts

Create a New Sheet Directly from the Address Bar

Next time you need to create a new spreadsheet, you don’t have to open the main Google Sheets page and click the new sheet button or commands there as instructed for beginners. Instead, simply type “sheet.new” directly into your browser’s address bar. As long as you are logged into your Google account, this action will create a new spreadsheet for you, no matter where you are on the website. (You can also type “sheet.new” or “Spreadsheet.new” if you prefer.)

Use Shortcuts to Insert Date and Time

Google Sheets has numerous shortcuts, but the most notable is a collection of commands for quickly inserting the current date or time anywhere you want.

  • Press Ctrl or Cmd along with the semicolon key to add the date.
  • Ctrl or Cmd along with Shift and the semicolon key to add the time.
  • Ctrl or Cmd along with Alt, Shift, and the semicolon key to add both date and time together.

Utilize Shortcuts for Quick Formatting

Google Sheets’ quick formatting shortcuts are also worth remembering. With these shortcuts, you can format any cell or selected range of cells the way you want without having to search through menus.

  • 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 scientific notation

Create Your Own Custom Shortcuts

You can even create your personalized shortcuts in Sheets to perform a variety of complex custom actions with a single command.

Open the Tools menu and select “Macro,” then choose “Record macro.” If you want the shortcut to always operate on the same specific cells, select “Use absolute reference”; otherwise, choose “Use relative reference.” Then, perform any actions you want to record.

You could do something like set a specific format for the cell content (e.g., white text in bold with Open Sans font and a dark gray background) or manipulate data in a more data-related way, such as copying the content of a cell, then deleting that cell, and pasting the content to the left of another cell. When you finish, click the “Save” button in the macro recorder, and you’ll be able to name your new shortcut and assign it to any available key combination to trigger in the future.

Useful Tips with Data in Google Sheets

The IMPORTRANGE function directly displays data

To display data directly from one spreadsheet into another, copy the full URL of the sheet containing the data and paste it into the IMPORTRANGE function, using the following format (with your own URL, sheet, and cell range):

code=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDEf", "Sheet1!D1:D15")

Then, simply paste that function into the sheet where you want the data to appear. You’ll need to hover over the cell and click a button to allow the two sheets to connect, and then all the data from your source sheet will magically appear. The IMPORTHTML function fetches data from the web

Sheets can also retrieve data from any public web page, as long as the page has a correctly formatted table. The secret lies in the IMPORTHTML command; use it with any URL you need and a number that indicates which table on the page you want to import (“1” for the first table, “2” for the second table, etc.):

code=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_sandwiches", "table", 1)

And just like that, all the information will appear in your spreadsheet.

The IMPORTFEED function for importing data from an RSS feed

A similar command allows you to import recent entries from a website’s RSS feed into any spreadsheet. All you need to do is enter the IMPORTFEED command along with the URL of the feed you want— for example, if you want to see all the stories about Science on my Google Sheet, you can enter:

=IMPORTFEED(“https://example.com/categories/all-science/rss”)

If you only want the titles of the stories—and only, for instance, the latest five entries—you can add the following parameters:

=IMPORTFEED(“https://example.com/categories/all-science/rss”, ”items title”, false, 5)

And then if you want to place links to each story in a separate column alongside that column, you can use this:

=IMPORTFEED(“https://example.com/categories/all-science/rss”, ”items URL”, false, 5)

Combine with Google Form to collect data

Google Sheets has a often-overlooked sibling called Google Form that allows you to collect data in a web survey-style form and then consolidate the results in a spreadsheet. You can create a form by finding the “Form” option in the “Insert” menu of Sheets, then using the appearing webpage to create any set of questions and parameters you desire.

Once your form is ready, click the “Send” button in the upper-right corner of the page to send the form via email to anyone, embed the form on a website, or get a manual link to share the form in the way you prefer. When responses are received, they will automatically appear in your spreadsheet as individual rows.

Cleaning Up Spreadsheets

Using TRIM Function to Remove Extra Spaces

If you discover trailing or leading whitespaces in your spreadsheet data, whether it’s numerical or textual, don’t forget Google Sheets’ TRIM function. You can input it into any cell, such as =TRIM(A1), and it will eliminate any leading or trailing spaces, providing you with a cleaner version of the cell’s value.

For applying the function to multiple cells at once, use this format for any range you need:

=ArrayFormula(TRIM(A2:A50))

Utilizing LOWER, UPPER, PROPER Functions for Standardizing Case

If your data has inconsistent capitalization, Google Sheets can standardize it for you with simple functions:

  • UPPER(A1) will convert all text to uppercase.
  • LOWER(A1) will do the same in lowercase.
  • PROPER(A1) will capitalize the first letter of each word for a title-case effect.

Using ISEMAIL to Check Email Addresses

If you have a database of user-submitted email addresses and want Google Sheets to verify their formatting, you can use the ISEMAIL function. Input ISEMAIL(A1) for any cell you need. For a range of cells, use the array formula:

=ArrayFormula(ISEMAIL(A2:A50))

The spreadsheet will provide “TRUE” or “FALSE” for each email address you provide.

Validating URLs with ISURL Function

You can also validate URLs in your spreadsheet to ensure you have appropriate links. Use the ISURL function with ISURL(A1) for any cell, or for a range of cells:

=ArrayFormula(ISURL(A2:A50))

The spreadsheet will provide “TRUE” or “FALSE” for each URL you need to check.

Analyzing and Visualizing Data

Quick View of Sum, Average, etc., of a Range of Cells

You can perform quick calculations in any spreadsheet containing numbers by selecting a range of cells and looking at the bottom right corner of the screen. By default, Google Sheets will display the sum of the selected numbers. Clicking on the info box allows you to request the average, minimum, maximum, or total count of the relevant numbers. Your selection choice will be retained as the new default for any future calculations you perform.

Sparkline Function for Small Charts in a Single Cell

You can create a small chart in a single cell using Sheets’ convenient Sparkline function. Simply input the command =SPARKLINE, like the formula below:

=SPARKLINE(E12:E23, {"charttype", "column"})

The Sparkline function places small data charts into individual cells in your spreadsheet.

You can use SPARKLINE to create the following types of charts:

  • “line” for line charts (default)
  • “bar” for stacked bar charts
  • “column” for column charts
  • “winloss” for a special type of column chart illustrating two possible outcomes: positive and negative (similar to flipping a coin, heads or tails).

Moreover, you can add formatting options like color, calculations, formatting, etc., to make your small chart look more appealing:

=SPARKLINE(A1:A5, {"charttype","column"; "axis", true; "axiscolor", "red"})

Formatting Spreadsheets Professionally

Hide Any Rows

Google Sheets allows you to hide any rows you wish. Click on its number in the gray column on the far left of the screen and select “Hide row” from the appearing menu. When you want to show the row again, click on the black arrows that appear in its position in the left column of the screen.

Make Sheets Stand Out

Do you want your spreadsheet to stand out quickly and effortlessly? Look for the “Alternating colors” option in the Format menu of Google Sheets. This option provides you with a set of simple choices that apply a neat color scheme to your rows without requiring you to think or make much effort.

Rotate Header Text

When editing the layout of your spreadsheet, consider rotating text in your header rows. Highlight the row, then click on the “A” icon with an arrow pointing up and down (next to the link insertion tool, on the left side). Then, you can choose from some eye-catching effects that will make your header text stand out and give your spreadsheet a vibrant, polished look.

Rotating header text and adding alternating colors are easy ways to bring appeal to your spreadsheet.

Copy Cell Formatting

Sheets can copy the entire formatting set of one cell and apply it to another with just a few clicks. First, click on the cell with the formatting you want to copy. Then, click on the paint roller icon – just to the right of the print command, on the left side of the toolbar – and click on the cell where you want to apply the formatting. Everything from font size and color to cell shading style and numbers will be applied.

Create To-Do Lists in Sheets

Create a checklist right within your spreadsheet for yourself or your team. Select a range of empty cells, open the Insert menu, and choose “Checkbox.” Then, you can input your to-do items in the next column and feel satisfaction as you check off items when completed.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top