Be a Spreadsheet Technocrat!

Working with Google Spreadsheet has been a part of almost all office arrangements. Going along with data and information is so easy by using Spreadsheets! For some fresh graduates working for the first time with this tool, it’s been way too far exploring before you can get used to easy access to it. We bet that you also want the best working tips on your Spreadsheet. We’re here to help you with that! Worry no more with these easy-access tricks, tips and hacks that you can use. No need to define the basics, let’s just go straight to the advanced!

1) DATA CLEAN UP

• Conditional Formatting
  • For those of you who are working on tasks and data monitoring, this feature will perfectly work for you! Just highlight the cells, columns or rows that you want to format. From there,  you can either use a single color or color scale. This won’t waste your time looking double-eyed in your data. Just look at those colors! 💚

• Data Validation
  • This amazing feature will prevent you from inputting unnecessary data especially on those columns that you only need a range of specific data. One there is data that is not included in the range of options that you set, there will be an “Invalid” mark that can easily catch your attention.

• Text Formatting
  • tHeSE UNEVEN tExT Format LIKE this Was rEaLly ANNOYING. iSn’t it? You can easily fix it through these formulas by just setting it to one cell then using the Clone Formatting for the next and other cells.
    • Use =Proper(“text”) if you want to have the cases in proper format.
    • Use =Upper(“text”) if you want to have the letters all in uppercase.
    • Use =Lower(“text”) if you want to have the letters all in lowercase.

• Splitting Text
  • Instead of manually encoding the names again, try this: =SPLIT(range, “put the delimiter such as a SPACE”)

2) PASTE SPECIAL

• Clone formatting
  • Have you ever wondered what that paint roller icon right after the Printer and Spelling and Grammar checker was about?  It’s the Paint Format icon! With that, you can easily copy the format of a specific cell without the hassle of manually setting the format.

3) VERSIONS HISTORY

• If you want to have a view of the edit and version history that has been made in your spreadsheet, just go on File > Version History > See Version History and there you go! You can also simply press CTRL+ALT+SHIFT+H for easy access.

4) ADVANCE FORMULAS

• Date
  • Use =Today() if you want to input the date today
  • Use =Today()+1 if you want to input the date tomorrow
  • Use =Today()-1 if you want to input the date yesterday
  • Just adjust the number of days that you want to input, whether plus or minus.

• Lookup
  • Vertical Lookup
    • To determine the key value of a specific word in the lists by column in the same sheet, try using this: =VLOOKUP(search_key,range,index,[is_sorted])

• Vertical Lookup (On another sheet)
  • To determine the key value of a specific word but the list is in another sheet, highlight the table first then right-click then select ‘Define Named Range’. Name the table then go back to the other sheet then try using this: =VLOOKUP(search_key,range_name,index,[is_sorted])

• Age
  • If you’re used to manually computing age with your personal information databases, here’s a shortcut: =DATEDIF(range,TODAY(),”Y”)&”(space)Years Old” then drag the formula down. 💚

• Barcodes
  • If you’re needing barcodes for any purpose for your business or organization, here’s how it is: =”*”&range&”*”

• QR Codes
  • QR codes instead of barcodes? Try using this =IMAGE(“https://chart.googleapis.com/chart?chs=150×150&cht=qr&chl=”&range)

• Determining if the email is right
  • If you’re having a mass email and want to check if all the email addresses are correct, try this: =ISEMAIL(range)

• Maximum and Minimum Value
  • Just simply select a blank cell then type =MAX(range) and =MIN(range) to determine the highest and least value in the column. This is a very helpful formula. Some of the databases that you can apply for are on grades or sales database analysis.

• Language Translate
  • If there’s a lot of words to translate in your spreadsheet, you don’t have to do it manually! Just use this and finish the translation right before the 3rd blink of your eye! Try this: =GOOGLETRANSLATE(range, “language code of language to be translated”,”language code of language you want to have”) then drag it down! 

5) PRINTING SHEETS

• Unlike Excel that you need to manually adjust the page area and margin, Spreadsheet automatically fits the sheet into the page. You can either choose to print the entire worksheet or just specific sheets in one click. The setting of gridlines and the likes can be all found in the print setup! Yes, you heard it right, you don’t need to jump from one setting to another.

6) TEMPLATES

• If you ever find it hard to customize your sheets in the most appropriate way possible, try looking for an available template for it! Just go through your Drive> New (This can be found in the upper left corner of the screen) > Google Sheets > From a Template. From there, you can choose between several templates! If you’re a license holder, you can also customize sheet templates suited for every position, workload, team or a department needs!

The perks of having a license don’t just end in a customized template. We’re pretty sure that you gained something with this short article. Are you considering buying a Google Workspace license at a very economically friendly price? Let us know! For the terms, conditions and SLA that we were offering, you may also visit this link https://bit.ly/32N2aK7 or email us at gsuite@i4asiacorp.com.