Some tips for using Google Sheet

Tram Ho

I. Introduction to Google sheet

  • Google Sheet is a free tool.
  • Google Sheet is used almost like Excel.
  • Google has some great tricks that make using Google sheets faster and more efficient. Here are a few typical tips. Hope it will help everyone

II. Some tips

1. Keyboard shortcuts in Google sheet

  • Some commonly used keyboard shortcuts:

Copy: Ctrl + C or Ctrl + Insert

Cut: Ctrl + X

Paste: Ctrl + V

Paste format only: Ctrl + Alt + V

Redo: Ctrl + Y

  • To see a specific list of Google sheet shortcuts, do the following: Click on Help Menu => select Keyboard Shortcuts => the list will be displayed.
  • We can search for function shortcuts right in Keyboard Shortcuts

2. Insert pictures in cells

Have 2 way:

  • The usual way: Click Insert => Image => Select Image in cell in case you want to insert pictures into cells, choose Image over cells in case you want to insert pictures out of cells.
  • Use the Image function: = image (“URL to image”, 1) – image resizes itself to fit into a cell = image (“URL path to image”, 2) – image expands itself to display full cell = image (“URL path to image”, 3) – image retains its original size = image (“URL path to image”, 4) – image with custom size

3. Generate QR Code

  • QR code is a 2-dimensional barcode used to encode an information such as a phone number, email address or URL or some other information.
  • To do this encoding we will use the Image function.

4. Get unique values ​​in the column

  • Use the Unique function
  • Formula: = Unique (highlight the number of cells you want to get unique value) Example: Unique (A2: A15)

5. Get information and data from a website

  • Use the IMPORTXML function
  • Usage: For example, need to get the price of 1 product on Tiki to Google sheet

a. Right click and select Inspect

b. Click on the price and you will see something like this:

c. Right-click on the line of code and select Copy => copy Xpath

d. The copy content will be:

// * [@ id = “next”] / div [1] / main / div [2] / div [2] / div / div [3] / div / span [1]

To use it in Google sheet, change “to ‘ // * [@ id =’ next ‘] / div [1] / main / div [2] / div [2] / div / div [3] / div / span [first]

e. Back on the Google sheet, attach the product link to the Product Link column and the formula input

IMPORTXML (A2, “// * [@ id = ‘__ next’] / div [1] / main / div [2] / div [2] / div / div [3] / div / span [1]”)

in the box to display the price

f. Product prices will be automatically displayed and we can easily track realtime product prices

6. Language recognition

  • Google sheet is capable of language recognition due to its integration with Google services.
  • Formula: = detectlanguage (column want to detect language) For example: = detectlanguage (A1) => then shows the language that Google sheet has recognized

7. Translate

  • Use the GOOGLETRANSLATE () function
  • Usage: The part in () includes 3 parameters:
  • The passage you want to translate
  • The language of the passage you want to translate
  • The language you want to translate into Example: GOOGLETRANSLATE (A1, ja, en)

8. Add special characters

9. URL Inspection

  • Use the isurl function (the cell you want to check) Example:

10. Compact text

Use the functions:

  • UPPER – converts all data to capitalization
  • LOWER – converts all data to lowercase
  • TRIM – removes leading and empty rows in data

For example, let the case use the UPPER function

Share the news now

Source : Viblo