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
- Using the CHAR () function
- List of special characters can be added: Refer to page: https://graphemica.com/unicode/characters/page/2 Example: = char (169)
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