Google Sheets is used by all office workers, many keep home accounting, do calculations in Tables, store data in databases. With this tool, you can even keep your Woo Casino login and other data for extra protection. However, you probably had a feeling that the app still keeps a lot of secrets, and you don’t use half of its functionality. Here are the tricks and secrets of Google Sheets that you might not know about.

Quickly Creating a New Table

To quickly open Google Sheets, you can use several ways:

  • Add docs.google.com/spreadsheets to your bookmarks.
  • Install Sheets as an app in your Google Chrome browser through the app store.
  • Type sheet.new, sheets.new, or spreadsheet.new in the address bar of your browser, as you normally would type the site address, and a new table will open.

Not many people know about the last method, but it works for documents and presentations: just type docs.new or slides.new, respectively.

Adding the Date or Time

If you need to put the exact date or time in a cell, you don’t have to type it in numbers, Tables can do everything for you:

  • To insert the date, press Control or Command and hit a semicolon.
  • To insert the time, press Control or Command, Shift, and semicolon.
  • To insert the date and time at the same time, press Control or Command, Alt, Shift, and semicolon.

Many functions in Google Sheets are also available through keyboard shortcuts, so don’t ignore them.

Quickly Formatting Numbers

Depending on the formatting, numbers in cells will behave differently: you can specify percentages, round them up or insert them as text, and work with currencies.

To avoid changing the format through multiple menus, use the combinations on your keyboard:

  • Ctrl + Shift + 1: format as a decimal.
  • Ctrl + Shift + 2: Format as time.
  • Ctrl + Shift + 3: Format as date.
  • Ctrl + Shift + 4: Format as a monetary value.
  • Ctrl + Shift + 5: Format as a percentage value.
  • Ctrl + Shift + 6: Format as an exponent.

Own Macros

Google Sheets has a menu for saving complex combinations of actions and running them with a single shortcut – macros. Select the Extensions – Macros menu and write down your sequence. To make sure the action is always performed for certain cells, select “Use absolute references”. For other cases, the “Use relative links” option will work.

This way you can preserve certain formatting (e.g. fill a cell with gray, apply a 11 point Calibri font, italicize) or do a more complex sequence – copy the content of a cell, erase it and paste the content one cell below. Macros can be managed from the Extensions – Macros – Manage macros menu.

Easy Copying

To copy the contents of one cell to all cells around and its contents appear above, below or to the side, try selecting the original cell, finding the square in the lower right corner of the original cell and, holding it down, pulling in the desired direction. Once you let go, the content will be copied to all cells in the action area.

If you need to continue the logical row, you can do this by filling the first two or three cells and then select all those cells and also pull down on the square.

Keeping the Cells in View

To keep one or more rows always in view, no matter how many columns or rows are in the table, you can “freeze” them. Find an intermediate cell above “1” and to the left of “A,” hover over it and find the spot where the cursor turns into a hand. Clasping it, pull it sideways or upward for as many rows as you want. All cells up to the gray line will stay in place, even if you’ve scrolled the table for as many screens.

Aligning the Entire Table

The cell above “1” and to the left of “A” can also help if you need to apply formatting to the entire table. You can click on it to highlight the table. Double-clicking on the border of any column or row will decrease or increase the size of the cells according to the content. If you just move the borders, the cells on the whole sheet will become the same size as you set them.

Formula Bar

If you are working with a bulky formula and want to see the entire formula, hover your mouse over the bottom border of the row that contains the formula, just above the column headers. By moving it, you can set the height of the row to whatever size you like.

Data From Another Table

If you need to show data in a cell that is in another table, you can use the ImportHTML function. Copy the link to the table from the address bar, add the sheet value and the cell address, and paste it like this:

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1aBcDEfgHiJKLMnOPQRSTuVWXZ”; “Sheet1!D1:D15”)

You will need to provide permission to merge the tables and then the content will appear in the desired cell. If you change the data in the source table, it will change in the destination table as well. Note: Your account must have access to the table into which you paste the contents of the source table cells.

Table Data on Web Pages

The ImportHTML command can load data from tables on sites, as long as they are properly formatted. In the formula we introduced above, you need to change the reference to the site address and the range to the table number (“1” for the first table on the page, “2” for the second, and so on):

=IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_sandwiches”; “table”;1)

You can load lists this way. Also note that the link to the site must be specified with a puncture, that is, with http/https.

RSS Feeds in Google Sheets

There is also a command for uploading the content of RSS feeds to a table. For example, you can unload a feed using this formula:

=IMPORTFEED(“https://blog.comfy.ua/ua/feed/”)

If you only want the headers of the posts and only the last five entries, you can add these parameters:

=IMPORTFEED(“https://blog.comfy.ua/ua/feed/”; “items title”;false;5)

And if you want to add links to each story, use this formula:

=IMPORTFEED(“https://blog.comfy.ua/ua/feed/”; “items URL”;false;5)

Google Forms

Many people overlook the ability to take surveys and collect feedback using Google Forms. You can build a form from the Tools – Create a new form menu. In other language versions, explore the Insert menu if you can’t find creating a new form in Tools.  

Once you’ve filled out all the questions, you can save and publish your form, send it to an email, or make a post with a link. The collected answers will be collected in your Google Table.

Mailing Address Database

If you store a database of mailing addresses in Sheets, you can check to see if everything is in the correct format. Use the IsEmail(A1) function with the correct cell or apply the formula to an entire range:

=ArrayFormula(ISEMAIL(A2:A50))

Google Tables will return true or false in response, depending on whether the address in the cell is correct.

Link Base

To check the validity of links in the database, you can use a formula like the one in the previous tip, but specify IsURL. You’ll get it like this: IsURL(A1). The formula will look like this:

=ArrayFormula(IsURL(A2:A50))

Find the Sum of the Numbers Without a Formula

To find out the sum of the numbers from multiple cells, highlight them and look in the lower right corner of the screen. Google Sheets will display the sum automatically, without adding formulas or extra effort.

Hiding Columns or Rows

You can hide any row or column from the context menu: right-click on the header and select “Hide column”. You can bring it back into the table with the gray arrows that will appear next to the hidden columns and rows.

Colored Rows

To make your data table more vivid and accentuated, select the Alternating colors box on the Format menu. Formatting is applied to the selected area: the rows will alternate colors. You can choose a style from the menu on the right that appears automatically.

After all, Google Sheets is a versatile tool that can be precisely and flexibly adapted to your needs. Explore the possibilities, don’t be afraid to experiment, and read our articles to be friends with technology and learn something new all the time.