15 Spreadsheet Formulas Working Professionals Should Know!

About this video

Jeff shares Google Sheets formulas he says he has used most over seven years as a management consultant and product marketer, opening with DETECTLANGUAGE and GOOGLETRANSLATE as a fun example. For VLOOKUP he warns using TRUE finds a closest match and can return the wrong result — in his example Model 3 instead of Model S — while FALSE finds an exact match, and a wildcard such as ‘TI*’ can find a name beginning with TI. He uses TODAY to auto-update a days-until-launch calculation, and IMPORTRANGE to copy live data from another tab or file (for example Google Forms feedback) so raw data is not edited directly. From there he counts with COUNTIF (13 people gave five out of five) and COUNTA (32 people gave feedback, 40.6%), splits timestamps with SPLIT, and extracts month or year parts with LEFT and RIGHT. For cleaning data he uses ISEMAIL and ISURL to check formatting, SUBSTITUTE to fix errors such as a missing @ before gmail.com, and ARRAYFORMULA to check a whole range at once. He builds bulk unique IDs with CONCATENATE plus RIGHT for the last four phone digits, classifies channels as organic or paid by combining SEARCH, IF, and IFERROR, totals them with SUMIF (organic channels contributed $4,714 toward a $10,510 total in his example), and finishes with formatting functions TRIM, UPPER, LOWER, and PROPER.

Key takeaways

• Use FALSE, not TRUE, as VLOOKUP’s fourth argument for an exact match; TRUE can return a closest match and the wrong row
• Use IMPORTRANGE to work on a live copy of data from another sheet or file so calculations and formatting never corrupt the raw source
• Check and fix messy data with ISEMAIL or ISURL to find bad entries, SUBSTITUTE to correct them, and ARRAYFORMULA to apply a check to a whole range at once
• Combine SEARCH inside IF inside IFERROR to label text automatically — e.g. return ‘organic’ when found and ‘paid’ when the search errors
• Use SUMIF with locked ranges (F4) to total by category reliably, and clean text before database use with TRIM, UPPER, LOWER, and PROPER

Episode details: published August 24, 2021 · 14:42 long. Summary written by Self Growth Videos from the episode itself. Business results and any income figures mentioned are Jeff Su’s own claims, shared for education — they are not typical or guaranteed.

← Previous: Personal Finance Tracker for Beginners (template included)! · Next: Dating, FOMO, Perfectionism: 100K Q&A →

All Jeff Su videos · Jeff Su profile · Jeff Su (official site)