Google Sheets Tips and Tricks

Google Sheets Tips and Tricks for Spreadsheet Productivity

Videos

From Twitter

Spreadsheet Files

  • Name files and folders with descriptive file names. Your future self will thank you when a Google Drive search result doesn’t list 12 spreadsheets with the name ‘Finance Info’.
  • Not sure of the Drive location of the Google Sheets document you have open? Click the folder next to the document name. In the folder selection box, click the square icon with the arrow. This will open the document's folder in a new tab.
  • If you're in a spreadsheet and want to delete it, click on "File" then "Move to Trash". Now you don't have to find and delete it in Drive.
  • Starting to get close to your Google Drive quota? Use this link to find your large files: drive.google.com/#quota
  • Press SHIFT + S in a Google Drive folder to create a new spreadsheet there
  • When bookmarking a Google Sheets spreadsheet in your browser, pay attention to the sheet you are on. When you click the bookmark, it will take you back to that sheet. You can use this to create bookmarks to different sheets in the same document.
  • Click the ⭐ next to the name of the spreadsheet to add it to the "Starred" folder in Google Drive. Easy way to keep track of your most important spreadsheets.
  • Save the current version of your Google Sheets document as a "Named Version" to easily refer to or make a copy of later on.
      • File > Version History > Name current version

Spreadsheet Editing and Navigation

  • Pressing CTRL+ENTER while editing a cell will insert a line break in the cell so you can have multi-line cells.
  • Double-clicking on a selection’s handle (bottom right corner) will auto-fill down to the same cell level as the bottom of the left adjacent column.
  • If you're not sure under which menu an item located, Use the keyboard shortcut [ALT + /] to search the menus.
  • Right click on a cell and choose “Get link to this cell”. This will copy the cell’s address to the clipboard. Create a link elsewhere with this address. You now have the start of a table of contents for your Google Sheets document.
  • Open up more room to type/edit in the formula bar by dragging the bottom of the formula bar downward. Or you can hide the formula bar by unchecking "Formula Bar" in the View menu.
  • You can zoom Sheets using the browser shortcuts (CTRL +/-). This will zoom all the open Sheets browser tabs and the menu bar. -OR- Use the zoom entry in the 'View' menu to change the zoom of only the current spreadsheet and not the menu bar.
  • Use CTRL+f to open the find interface. Type search characters and click enter until the cell you are looking for is highlighted. Press ESC and the find dialog will go away and the selected cell will be the last one it highlighted.
  • Need a font size not in the default list? Click on the number at the top and edit the size directly.

Functions

  • Use NOW() to see the last time the spreadsheet was edited. Just put '=NOW()' in an out of the way cell and it will update to the latest date/time anytime the spreadsheet changes.
  • Save links in Google Sheets using the HYPERLINK() function.
      • Open a hyperlink in the currently selected cell using the keyboard shortcut ALT + ENTER (if multiple cells are selected, all links will open in separate tabs - COOL)
  • Discover formulas by typing = and then the start of a function name to find all names starting with the same letters.
  • Sheets has over 400 functions. To find one, click the function button (hiding in plain sight) to show a list of functions sorted by category.
  • Track the price of Bitcoin with Google Sheets. Enter the following into a cell:
      • =GOOGLEFINANCE("CURRENCY:BTCUSD")
  • If you need to calculate the number of days, months, or years between two dates, use the DATEDIF() function.
  • Use the ISEMAIL() function to checks whether a value is a valid email address.

Ranges

  • When selecting cells, you can hold the CTRL key and choose non-adjacent cells.
  • Use the '$' in front of the column and/or row to lock the formula so it does not change.
  • Highlight a range and press CTRL+ENTER to fill down through the range.

Keyboard Shortcuts

  • If you format a cell, add a row or perform any repeatable action and need to redo it multiple times, press CTRL+Y or F4 to repeat the action in a new location(s). This only works with the last action performed.
  • CTRL+/ will show a searchable list of all the keyboard shortcuts
  • CTRL+\ will remove the formatting from the selected range.
  • CTRL+` will show all the formulas in the cells. Press again to show results view.
  • F4 - Toggle between absolute and relative references when in a formula.
  • F2 or CTRL+ENTER to edit the current cell
  • Enter in a cell the current date/time
    • Current Time: [ CTRL + SHIFT + ; ] - '9:26:16 PM'
    • Current Date: [ CTRL + ; ] - '11/8/2019'
    • Current Date/Time: [ CTRL + SHIFT + ALT + ; ] - '11/8/2019 21:27:03'

Formatting

  • If you create custom colors in a Google Sheets document, you can copy cells that use the custom colors into a different document and they will be available on the custom palette there as well.
  • If a font size other than the defaults are needed, click at the top and manually edit the font size.
  • Keep leading 0's when entering numbers by typing a single quote (') before the number.
  • The text in a cell can have multiple types of formatting. Highlight any part of text in a cell and format only that selection.
  • When importing data into a sheet, you can tell what kind of data Google Sheets thinks it is by the default alignment.
      • Numbers will align to the right.
      • Bools will align in the center.
      • Text will align to the left.
  • Apply border to selection - ALT+SHIFT+7 | Remove border from selection - ALT+SHIFT+6

Drop Down Lists

  • When using a range of values for your drop down list, make the range larger to include blank cells where you can add values later. If there is a blank value in the range, it will not be included in the drop down list.

Documents

  • You can search for a sheet right in the Chrome Omnibox. Just start typing the name and it will start to autocomplete just like a web address. Then just arrow down to the entry to open it up.
  • To create a new spreadsheet fast, type 'sheet.new' or 'sheets.new' or 'spreadsheet.new' in the address bar. Click the folder next to the name to change it's location from the root folder.

Limits to Google Sheets

  • The current limit on the number of cells you can have in a Google Sheets spreadsheet file is 5 million. Use them wisely.
  • Google Sheets documents don't count against your Google Drive quota.

Apps Script

  • If you're not sure how to do something with your Google Sheets document in Apps Script, create a macro that does it, then view the Apps Script code behind it.

Collaboration

  • When creating comments, use a '+' to notify a user of the comment (multiple users can be notified). Optionally, the comment can be assigned a user.
  • Replace "edit" in the URL with "copy" to force the user to make a copy when sharing the link. Be sure the document is shared to "Anyone with the link" or "Public on the web" (same with the next 3 tips)
  • Replace "edit" in the URL with "preview" to show a read-only version of the spreadsheet without the overhead of the editor.
  • Replace "edit" in the URL with "export?format=pdf" to send a pdf version to the document.
  • Replace "edit" in the URL with "/template/preview" allowing the user to see a preview before making a copy.

Fun Tips

  • Paste your favorite emojis into a Google Sheets document and copy from there whenever you need one. Find an emoji on Emojipedia. You can even use emojis in the filename.
  • If you are using Windows 10 and want to insert an emoji or two into your Google Sheets spreadsheet, use the keyboard shortcut [WIN + .] to activate the emoji picker.
  • Add an image to a cell with the IMAGE function. Because the image is IN the cell, it will be sorted with the other data in the row.
  • Provide feedback to Google right from sheets. Click 'Report a Problem' in the help menu and fill out some feedback and optionally include a screenshot.