fbpx

Google sheet tips that every researcher should know

The internet has become indispensable for a long time now, especially for researchers. And while there’s a list of things they can use it for, Google giving them the ease to keep their research on the cloud so the whole team could work in the same space sheet tops the chart. Right?

Not just that, just like the good ol’ shortcuts and formulas we used in the good ol’ excel sheet, Google too has some new tricks. These google sheet tips and tricks must be up every researcher’s sleeve. Don’t know them yet? Well, you’ll, now!

We gathered all our researchers and asked them one google sheet trick that has made their life easy and you’d be surprised to see the things that can not just save your time but make your work fun. So without further ado, here’s a list of google sheet tips and tricks you outta know about.  

Open New Document–type sheet.new or doc.new:

Unfortunately, in browser-based softwares, CTRL+N does not work as it should i.e. open a new document. Instead, you will get another chrome window staring back at ya!

Of course, you can always go to the Google-Sheets homepage and click the new sheet button there. But there’s another, much cooler option. Just type sheet.new or doc.new in the browser and automatically get a new document to start working on. 

Sukha

Translate the Text using GOOGLETRANSLATE(cell number, “auto”, “en”) Function: 

Often while doing broad relevance checks of patents by looking at titles, we come across multiple rows of titles from different languages. And even though you can quickly copy-paste the text on google translate, what’s the harm in doing it even faster and right there on your sheet?

GOOGLETRANSLATE() Function can be used in that case to translate multiple rows by applying the formula in one row and auto-filling it for the rest of the rows. You can also translate inventor/assignee names by this method quickly without having to open google translate for each cell content.

Harleen Singh

Removing the extra spaces in the data:

Ever had to check and delete the extra leading or trailing spaces in the whole sheet? Well, that’s one way to drain your spirits!

To remove extra leading or trailing spaces in the data of a cell, use the function ‘TRIM’. The function can be used using the formula =trim(cell).

Or just navigate to the sheet menu > Data > Data Clean up > Trim Whitespaces

Trim function in google sheet

Nandini Narang

Insert Date from Calendar:

This feature is especially useful when you are making a plan. Using this feature, you can bring up the calendar into a cell. You also can adjust the date if you need to. Now you’ll never have to move the mouse to the bottom right corner to see the date and type it out on the cell!

All you have to do is, select the cells (where you need to insert dates from a calendar) and go to data. Then data validation. When you click “data validation”. The Below Dialogue box will appear.

Data Validation function in google sheet

Click on “criteria” and choose “date”. If you want to reject the invalid date then choose “reject input”. Then click on save. Now when you double click on the cell, you can pick your date. 

Insert Date function in google sheet

-Sabarna Choudhury

Create Filter views instead of applying filter:

There’s no doubt that Google-Sheets is great for collaborating. Everything is updated in real-time. Imagine if the same thing applies to filters?

One person filtering the data will not affect the data at the other’s end as well. Whenever using g-sheets in a group, all you have to do is instead of using filters, choose ‘filter views’. And Voila! You’ll see what you need without affecting the data displayed to other people.

Create Filter views function in google sheet

Remove Duplicate: 

Tired of the duplicates while shortlisting the data?

Well, there are two ways you can make this easy.

 First is using the menu: Select the range of data you want to remove duplicates from

>Navigate to the Google Sheets menu > Data > Data Clean up > Remove Duplicates

Remove Duplicates function in google sheet

You’ll then get a pop-up asking to confirm the columns you’re looking for duplicates in, hit “OK”.

The second is using a formula to create a list of unique values from a range of data. 

Syntax: =UNIQUE(Data Range)

Yes, it is THAT SIMPLE!

Hyperlinking is one thing that every researcher needs. To quickly do this you just need to select the text you want to hyperlink and press (Ctrl + K). Now, add the desired link on the pop-up and the text will be hyperlinked.

– Megha Agrawal

“Explore” the AI: 

Want to get some quick insights about your data, but not sure which chart you should?

Well, just you can ask questions about the data from Google AI!

Press Alt+Shift+X or just click on the explore button on the bottom right corner. Based on your data, you’ll get suggestions for formatting, charts, and analysis.

Explore the AI function in google sheet

Some Keyboard Shortcuts to remember:

  1. To add date and time-

Insert current time- Ctrl+Shift+; 

Insert current date- Ctrl+;

  1. Keyboard Shortcuts for Applying borders:
  • Apply top border – Alt + Shift + 1
  • Apply right border – Alt + Shift + 2
  • Apply bottom border – Alt + Shift + 3
  • Apply left border – Alt + Shift + 4
  • Remove borders – Alt + Shift + 6
  • Apply outer border – Alt + Shift + 7/ Ctrl + Shift + 7

                                                                                                                                 – Hadeeba Jalali

  1. Paste the text as values (W/o the formatting)
    Ctrl+Shift+V

Akshay Agarwal

A much-required list of Google sheet tips, right? I can feel your happy faces as I write this. Now, be mindful and share it with your colleagues and friends. 😉

Did we miss something? Add the tips that make your life on sheet easier in the comments section below.

Next, read how this Offline Google for Organizational data can help manage knowledge efficiently.

P.S. Check out these 7 Advanced Tips to unlock the full potential of Google Patent Search.

Leave a Comment

Become a part of GreyB’s insider list

Get our distilled learning delivered to you.

Get the Sample Report

Fill out the form and get the report.