Google Spreadsheet is a grate product from the google. It has the all features of Microsoft excel as well as some other features. You can work this sheet with online and online in some platform. support many platforms like Windows, Mac, android and IOS.
Google Sheet Features
Data Validation in Google Sheet
Data validation will help many place like validate the defined rule. The following steps need to follow in order to do the data validation
- Select Row(Where you want to data Validation)
- Click inset in tab
- Click data validation in Insert drop-down Menu,
- Set your data validation rules
- Done
- Reference YouTube Video: Relative Data Validation with Dynamic Drop Downs
Copy Sheet in Google Sheet
You can copy the whole each sheet data into another sheet or create new sheet.
Google Sheet Default Function
Google is proving many formula with google sheet like excel. We can use that as per our needs. Here below some functions were detailed explained. All the functions are listed here
IF()
- Syntax : =IF(AND((L3931=”Verified”), (F3932=$M$3931)),”Nill”, “”)
DATEDIF()
- Syntax: DATEDIF() is function return the difference between two dates
- Input Parameters: DATEDIF(start_date,end_date,unit)
EOMONTH()
- End Of Months function
- Syntax : EOMONTH(Date(1992,08,1),0)
SUMIF()
- Sum Specific Criteria Example: =SUMIF(Income!B3:B, B94, Income!F3:F)
- This above formula calculates only the specific dates also specific criteria.
SUMIFS()
- Sum Specific date example: =sumifs(Income!F3:F,Income!A3:A,”>=”&C92,Income!A3:A,”<=”&EOMONTH(C92,0))
- This above formula calculates only the specific dates also specific criteria.
TEXTJOIN()
- Syntax
TEXT()
- Text function convert the number into text according to a specified format
- syntax: =TEXT(Input, Format) , Input = cell, Format = custom format
- Reference: https://support.google.com/docs/answer/3094139?hl=en
GOOGLETRANSLATE()
- Translate English into tamil in Google Spreadsheet GOOGLETRANSLATE(“Arun E world”,”en”,”ta”); Replay is เฎ เฎฐเฏเฎฃเฏ เฎ เฎเฎฒเฎเฎฎเฏ
Convert number system into another number system
- OCT2HEX: The OCT2HEX function converts a signed octal number to signed hexadecimal format.
- OCT2DEC: The OCT2DEC function converts a signed octal number to decimal format.
- OCT2BIN: The OCT2BIN function converts a signed octal number to signed binary format.
- HEX2OCT: The HEX2OCT function converts a signed hexadecimal number to signed octal format.
- HEX2DEC: The HEX2DEC function converts a signed hexadecimal number to decimal format.
- HEX2BIN: The HEX2BIN function converts a signed hexadecimal number to signed binary format.
- DEC2OCT: The DEC2OCT function converts a decimal number to signed octal format.
- DEC2HEX: The DEC2HEX function converts a decimal number to signed hexadecimal format.
- DEC2BIN: The DEC2BIN function converts a decimal number to signed binary format.
- BIN2OCT: The BIN2OCT function converts a signed binary number to signed octal format.
- BIN2HEX: The BIN2HEX function converts a signed binary number to signed hexadecimal format.
Great Examples:
The following lists are the good example of the above formulas
- How to calculate age from birth of date in Google sheet?
- How to fill the empty cell as “Nill” String?
- Convert Date (or Date and time) into String in Google Spreadsheet example?
- How to embed QR code into your spreadsheet cell?
How to calculate age from birth of date in Google sheet?
- Formula : =IF(B2,DATEDIF(B2,TODAY(),”Y”),””)
- Here IF() is a if function, B2 is the date of birth stored location, DATEDIF() is function return the difference between two dates, TODAY() is a date function it returns the current date of today, Y is returns year.
How to fill the empty cell as “Nill” String
- Formula : =IF(AND((L3931=”Verified”), (F3932=$M$3931)),”Nill”, “”)
Convert Date (or Date and time) into String in Google Spreadsheet example
- Assume the below examples A1 cell contains the input and A2 cell is for result.
- Method 1: A1 cell contains date (or any input )input, then =TEXTJOIN(“”,TRUE,A1) . this will return the A1 cell data.
- Method 2: A1 cell contains date, then =TEXT(A1,”YYYYMMDD)”) this will return year, month, date.
- Method 3: A1 cell contains date and time, then=TEXT(A1,”YYYYMMDDMMHHSS)”) this will return year, month, date, hour, minute, seconds.
How to embed QR code into your spreadsheet cell?
- Formula =IMAGE(“https://chart.googleapis.com/chart?chs=150×150&cht=qr&chl=”&A2)ย here A1 is the input of the QR.
- Reference:ย Click Here
Google Spreadsheet API
Google Sheet with App Sheet
Inventory Management using Google Sheet
- App Sheet is a platform to build your inventory, with use your spreadsheet as data base. refer for more info
You must be logged in to post a comment.