Part II. Some tips for using Google Sheet

Tram Ho

Continuing part I would like to introduce to you 10 more tips from Google Sheet offline.

Refer to Part I here

11. Drag data from other Google Sheets

  • Use the IMPORTRANGE function
  • Formula: = IMPORTRANGE (“spreadsheet_URL”, “Sheet and cell references”)
  • Examples: = IMPORTRANGE (“1jghakueH148eLoExb-DNj2j6YgGf5cVOgpuwfCtoo2I”, “Sheet1! A2: B6”)


Sheet and cell references: are the sheet and from & to of the value cell we want to get the result.


Note: there will be a small popup showing out asking us to connect to the sheet that we want to get data from. You just need to select Access allow , the data of that sheet will be transferred to the current sheet according to the formula.

12. Lock cells to prevent unwanted changes

– Purpose: want to limit editing rights of members sharing files. – How to lock: Step 1: Highlight the area you want to lock. Step 2: On the toolbar, select Data> Protected sheets and ranges

Step 3: The Protect Sheets and Ranges options bar will appear on the right side of the window. Here, you can enter a brief description of the reason for the key in the Enter a description box

Step 4: Click the green “Set Permissions” button to switch to the set permissions for locked cells.

Step 5: After setting up permissions is complete, click Done. If you want to show warning when others edit, then check the Show a warning when editing this range

13. Convert files to Sheets

– How to transfer: Step 1: Upload the .XLSX file to google drive Step 2: Open the .XLSX file using Google sheets

** Step 3: ** on the toolbar select File> Save as Google Sheets

Step 4: After clicking Save as Google Sheets will generate a new file similar to the Google Sheets format and you can delete the old .XLSX file format.

14. Share your Google Sheet

– How to share:

Step 1: Click the green Share button on the right corner of the screen

Step 2: Input the email addresses you want to share.

Step 3: Select the permission for the account you want to share and then click Done.

15. Copy format

  • Use the Format Painter to format cells and use a similar style in other cells.
  • Steps to be followed:

Step 1: Select the cells that you want to copy the format from.

Step 2: Click on the format painter and drag over the cells that you want to apply the formats to.

16. Enter from Form into Sheets

Step 1: In the toolbar of Google spread sheets select Insert > Form

Step 2: The interface will appear as follows and here you can create a custom form.

Explanation of items: 1: title of the form (this title will default to be taken by the title of google sheet) 2: description of form 3: add question 4: import form 5: place to input question 6: place to input answer 7: question answer method 8: establish whether the answer is required or not? If the answer is required then turn this function on

17. Show full screen

– Purpose: to help limit distractions appearing on the computer screen. – How to show full screen: press View > Full screen

18. Conditional Formatting

Purpose: conditional formatting is used for large spreadsheets, using a coloring tool to help you view large amounts of data easily to handle it

How to perform conditional formatting:

Step 1: On the toolbar select Format > conditional formatting

Step 2: Then on the right, Conditional format rules will appear on the screen to set conditions for data.

Explanation of items:

1: the cell for which you want to set the condition

2: spot setting condition. The conditions are as follows:

Cell is emptyEmpty box
Cell is not emptyThe cell is not empty
Text containsContains text
Text does not containDoes not contain text
Text is exactlyHave exact same text
Date isContains the date
Date if beforeContains the previous date …
Date is afterContains the following date ….
Greater thanBigger…
Greater than or equal toGreater than or equal
Less thanLess…
Less than or equal toLess than or equal
Is equal toEqual to …
Is not equal toNot equal to …
Is not betweenNot in the middle …
Custom FormulaAccording to the formula

3: color setting for the condition

4: press Done to complete the condition setting

5: add another condition

19. Freeze / freeze rows / columns in Google sheets

-Purpose: fixing a row or column is the hardening of that row / column at the top of the worksheet, whether you scroll down or drag the article sideways, the row / column remains in this position.

-How to freeze rows / columns:

Step 1: Open the Google Sheets page, click the cursor on the number or word of that row / column.

Step 2: Select View > Freeze , then you will see a menu of options appear. In this options table you will be able to select the row / column that you want to fix.

Step 3: As a result, the row or column you select has been frozen and fixed with a gray border to separate the rest area, and cannot be moved no matter where you drag the mouse. again.

20. How to create an option list

– How to create a dropdown menu:

Step 1: Select the cell where you want to create the dropdown list

Step 2: Next, choose Data > Data validation

Step 3: Then it will show popup with title Data validation as below and you can set the drop-down list by clicking on the red box.

Step 4: After clicking the red box, a popup will appear again with the title Select a data range and you will select the range of options you want> click OK > click Save as the setting is complete.

Share the news now

Source : Viblo