For a long time, Google Sheets was perceived merely as a lightweight, online “clone” of Microsoft Excel. While Excel remains the undisputed king of handling massive offline datasets, Google Sheets has quietly built a formidable arsenal of features powered by cloud computing and the vast Google ecosystem.
The ability to collaborate in real-time, connect seamlessly with APIs, and utilize exclusive cloud-based functions has transformed Google Sheets into an indispensable tool for the digital age. It is no longer just a spreadsheet; it is a dynamic data engine.
In this comprehensive guide, “Thủ Thuật” presents 16 essential Google Sheets tips, ranging from foundational shortcuts to advanced data manipulation techniques. Whether you are a casual user or a data analyst, these tricks will help you optimize your workflow and unlock the full potential of your spreadsheets.
1. Mastering Keyboard Shortcuts
Speed is the defining factor of productivity. If you are migrating from Excel, you will be pleased to know that Google Sheets supports most standard shortcuts. However, because it runs in a browser, it also possesses a unique set of commands designed to navigate the cloud interface efficiently.
Memorizing every single key combination is unnecessary. Google Sheets provides a built-in “cheat sheet” overlay that you can access instantly without leaving your work. Simply navigate to Help > Keyboard Shortcuts or press Ctrl + / (on Windows) or ⌘ + / (on Mac).
Animated GIF showing the keyboard shortcut overlay in Google Sheets
This overlay displays shortcuts relevant to your current context. A pro tip for frequent web users: Make Ctrl + Shift + V (Cmd + Shift + V on Mac) your best friend. This command performs a “Paste values only,” stripping away unwanted formatting from websites—a common headache when compiling data from the internet.
2. Embedding Images Directly into Cells
In traditional spreadsheet software, images often float above the grid as separate objects, making sorting and filtering a nightmare. Google Sheets solves this with the IMAGE function, which embeds a picture directly inside a cell. This means the image acts like data: it resizes, moves, and hides along with the row or column it resides in.
The syntax is straightforward:
=IMAGE("URL"; [mode]; [height]; [width])
The [mode] argument offers four distinct behaviors:
- 1 (Default): Resizes the image to fit entirely within the cell, maintaining the aspect ratio.
- 2: Stretches or compresses the image to fill the entire cell (may cause distortion).
- 3: Displays the image at its original size (may be cropped if the cell is too small).
- 4: Allows you to specify custom dimensions in pixels.
Screenshot demonstrating the IMAGE function syntax to insert a picture into a cell
This feature is particularly useful for product catalogs, employee directories, or tracking project assets visually.
3. Powerful Data Querying with SQL Syntax
The QUERY function is arguably the most powerful tool in Google Sheets, bridging the gap between a simple spreadsheet and a database. It allows you to use commands similar to Structured Query Language (SQL) to manipulate data.
Instead of creating multiple temporary tables or complex nested filters, you can write a single sentence to ask your data a question. You can select specific columns, filter rows, sort results, and even limit the number of outputs in one go.
For example, to find all employees with sales greater than $1000 and sort them by name, you would use:
=QUERY(A1:D100, "SELECT A, B WHERE D > 1000 ORDER BY A")
Animated demonstration of the QUERY function filtering data dynamically
Mastering QUERY allows you to build dynamic dashboards that update automatically as your source data changes, significantly reducing manual maintenance.
4. Extracting Dates from Datetime Stamps
In the underlying architecture of Google Sheets, time is stored as a number. The integer part represents the date (days since Dec 30, 1899), while the decimal part represents the time of day. When you import data from system logs or form submissions, you often get a “Datetime” format (e.g., 12/05/2025 14:30:00).
To analyze data by day, you need to strip away the time component. The INT() function is the cleanest way to do this. By rounding the number down to the nearest integer, it effectively removes the fractional “time” data, leaving you with a pure date value. This is crucial for accurate pivot tables and SUMIF calculations based on dates.
Spreadsheet showing the INT function separating dates from timestamps
5. Generating QR Codes Automatically
You can turn your spreadsheet into a QR code generator without installing third-party add-ons. By combining the IMAGE function with the Google Charts API, you can generate QR codes for URLs, text, or contact information instantly.
This is invaluable for inventory management (scanning product codes), event management (generating tickets), or creating contactless digital business cards for employees.
Use the following formula structure:
=IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=150x150&chl=" & A2)Note: A2 represents the cell containing the data you want to encode.
Bulk generation of QR codes in Google Sheets using the IMAGE function and Google APIs
6. Multi-Condition Lookup with Array Formulas
The classic VLOOKUP is a staple of office work, but it struggles when you need to find a value based on two or more criteria (e.g., finding the price of “Product X” in “Region Y”).
While you can create “helper columns” to merge data, a more professional approach involves INDEX and MATCH combined with ARRAYFORMULA. This technique creates a virtual “key” by combining multiple columns on the fly, allowing for a precise lookup without altering your data structure.
A typical formula might look like this:
=ARRAYFORMULA(INDEX($E$4:$E$8, MATCH($G4&$H4&$I$3, $B$4:$B$8&$C$4:$C$8&$D$4:$D8, 0)))
Complex formula using ARRAYFORMULA, INDEX, and MATCH for multi-criteria lookup
This method is robust and less prone to breaking if columns are inserted or moved. It separates the “lookup” logic from the “retrieval” logic, providing greater flexibility than VLOOKUP.
Below is a visual breakdown of how the formula matches the combined criteria against the combined data columns:
Visual breakdown of how the index and match formula processes multiple columns
7. Dynamic Data Sifting with FILTER
The standard filter button on the toolbar is useful for quick checks, but it hides rows for everyone viewing the sheet. The FILTER() function, however, extracts data meeting specific criteria to a new location, leaving the original dataset untouched.
This is perfect for creating specific views from a Master Data sheet—for example, creating a separate tab just for “Pending Orders” or “High Priority Tasks.”
Syntax: =FILTER(range, condition1, [condition2, ...])
Output of the FILTER function based on specific criteria
8. Extracting Unique Values
De-duplicating data in legacy spreadsheets often required navigating through menu ribbons. Google Sheets streamlines this with the UNIQUE() function.
By simply referencing a range, UNIQUE instantly returns a list of distinct values, removing any duplicates. Because it is a formula, the list is dynamic; if you add new data to the source column, your unique list updates automatically. You can wrap this in a SORT function to keep your list organized.
Animation showing the UNIQUE function extracting distinct values from a list
9. Automated Web Scraping
Do you need to track stock prices, currency exchange rates, or sports league tables? Manually copying and pasting this data is inefficient. The IMPORT family of functions transforms Google Sheets into a web scraper.
- IMPORTHTML: Ideal for scraping data that is structured as a
<table>or<list>on a website. - IMPORTXML: More advanced, allowing you to target specific data points using XPath queries.
For example, to pull a table from Wikipedia:
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_highest-grossing_films", "table", 1)
Importing a table from Wikipedia using the IMPORTHTML function
10. AI-Powered Language Detection
Leveraging Google’s massive AI capabilities, the DETECTLANGUAGE() function can identify the language of any text string. It returns the two-letter ISO language code (e.g., “vi” for Vietnamese, “en” for English, “fr” for French).
This is extremely helpful for global teams handling feedback forms or support tickets that come in various languages. You can use this to route data to the correct regional team automatically.
The DetectLanguage function identifying text languages as language codes
11. Instant Translation
Perhaps the most distinct advantage of Google Sheets over desktop competitors is the GOOGLETRANSLATE function. It allows you to translate thousands of cells instantly without copy-pasting into a translation tool.
Syntax: =GOOGLETRANSLATE("text", "source_language", "target_language")
Combined with DETECTLANGUAGE, you can build a self-translating spreadsheet that automatically standardizes foreign inputs into your local language.
Translating text between languages using the GOOGLETRANSLATE function
12. Validating Emails and URLs
Data hygiene is critical. When collecting user data, invalid email formats or broken links can ruin your marketing campaigns. Google Sheets offers specialized validation functions to audit your data quickly:
- ISEMAIL(cell): Returns
TRUEif the text follows the standard email format (e.g., [email protected]). - ISURL(cell): Checks if the text is a valid web address protocol.
Using ISEMAIL to validate email address formats in a list
These functions are best used in Conditional Formatting to highlight invalid entries in red automatically, alerting you to potential errors immediately.
Checking website link validity using the ISURL function
13. Forecasting with GROWTH
Predicting future trends based on historical data is a common requirement for business planning. The GROWTH() function (similar to FORECAST options in other tools) calculates predicted exponential growth.
It uses existing data points to determine a trend and then extrapolates that trend forward. This is ideal for predicting revenue, user acquisition, or viral growth where the increase is not merely linear.
Using the GROWTH function to predict future values based on historical data
14. Clean Coding with Line Breaks
As your skills improve, your formulas will get longer. A complex nested IF or QUERY formula can become a “wall of text” that is impossible to debug.
To make your formulas readable, use line breaks. By pressing Alt + Enter (Option + Enter on Mac) while typing in the formula bar, you can move to a new line. Google Sheets treats this purely as formatting; it does not affect the calculation. This allows you to structure your code logically, indenting specific sections for clarity.
15. Inserting Special Characters via ASCII
Sometimes you need symbols that aren’t on your keyboard, such as copyright signs (©), checkmarks (✓), or currency symbols. The CHAR() function allows you to insert these by referencing their Unicode or ASCII number.
For instance, =CHAR(10004) outputs a heavy checkmark. This is useful for creating visual status indicators in your reports without using images.
Inserting special symbols like checkmarks using the CHAR function
16. Random Selection and Gamification
Need to pick a winner for a giveaway or assign a random task? The combination of CHOOSE and RANDBETWEEN is a fun and functional trick.
The formula =CHOOSE(RANDBETWEEN(1,5), "Option A", "Option B", "Option C", "Option D", "Option E") works by first generating a random number between 1 and 5, and then selecting the corresponding option from your list. It’s a simple way to introduce randomization into your data models.
Selecting a random name from a list using CHOOSE and RANDBETWEEN
Conclusion
Google Sheets is far more than a simple grid for numbers; it is a versatile platform for data processing, automation, and analysis. By mastering features like direct image embedding, SQL querying, and API integrations, you transform your spreadsheet into a powerful application.
We hope these 16 Google Sheets tips help you elevate your office skills, saving you time and reducing errors. Try applying just one or two of these to your daily workflow and experience the difference in efficiency.
Do you have a favorite Google Sheets trick that we missed? Share your experience with the Thủ Thuật community in the comments below!
References:
- Google Workspace Learning Center – Sheets Help
- Ben Collins – Google Sheets Developer & Expert
- Google Charts API Documentation
Discussion about this post