Mastering Google Sheets: Essential Tips and Tricks

Google Sheets is a powerful tool for data organization, analysis, and automation. Whether you’re a beginner or an advanced user, understanding key functions and tricks can significantly improve your workflow. This guide covers essential techniques such as formatting, merging cells, using IF statements with IMPORTRANGE, highlighting duplicates, and much more. Let’s dive in!

How to Make 0.00 Blank in Google Sheets

If you’re working with numbers in Google Sheets and want to display blank cells instead of “0.00,” you can achieve this using custom number formatting.

Select the range of cells where you want to hide 0.00.
Click on Format > Number > Custom number format.
Enter this format: 0.00;;; and click Apply

This format ensures that zero values appear as blank while keeping other numbers visible.

How to Merge Cells in Google Sheets

Merging cells in Google Sheets helps in creating headers or structuring tables for better readability.

Select the cells you want to merge.
Click Format > Merge cells.
Choose one of the options:

Merge all: Combines all selected cells into one.
Merge horizontally: Merges only across rows.
Merge vertically: Merges only down columns.

Note: Merging will keep only the top-left cell’s content.

How to Use IF Statement with IMPORTRANGE in Google Sheets

The IF function combined with IMPORTRANGE allows you to filter imported data based on conditions.

=IF(IMPORTRANGE(“URL”,”Sheet1!A1″)>10,”High”,”Low”)

Steps:

Use IMPORTRANGE(“spreadsheet_url”, “range”) to import data.
Apply IF to check conditions and return different values accordingly.

This is useful for dynamically filtering data from another sheet.

How to Highlight Duplicates in Google Sheets

To highlight duplicate values in Google Sheets:

Select the column or range to check for duplicates.
Click Format > Conditional formatting.
Under “Format cells if,” choose Custom formula is.
Enter the formula: =COUNTIF(A:A, A1)>1 (Adjust range accordingly).
Select a formatting style and click Done.

This will highlight all duplicate values within the selected range.

Google Sheets: How to Copy Non-Hidden Cells

If you have hidden rows or columns and want to copy only the visible data:

Select the visible cells (use Ctrl + Click to select manually if needed).
Press Ctrl + C to copy.
Paste in another location using Ctrl + V.
Alternatively, use FILTER() to extract only visible data dynamically.

Google Sheets: Fill Formula to the Right

To fill a formula across multiple columns:

Enter the formula in the first cell.
Click and drag the small square in the bottom right corner of the cell to the right.
Alternatively, use ARRAYFORMULA() for an automatic expansion: =ARRAYFORMULA(A1:A10 * B1:B10)

This method speeds up calculations across multiple columns.

Google Sheets Link from Two Different Google Accounts

To link Google Sheets from two different Google accounts:

Open the target sheet.
Use IMPORTRANGE(“URL”,”Sheet1!A1:Z100″).
When prompted, grant permission to access the other account’s sheet.
You can also Share the source sheet with “View” or “Edit” permissions for seamless access.

Google Sheets Random Phone Number Generator

To generate random phone numbers in Google Sheets:

=”+1″ & RANDBETWEEN(1000000000, 9999999999)

Explanation

+1 is the country code (change as needed).
RANDBETWEEN(1000000000, 9999999999) generates a random 10-digit number.

Use ARRAYFORMULA for bulk generation: =ARRAYFORMULA(“+1” & RANDBETWEEN(1000000000, 9999999999))

Identify Peaks in Google Sheets

To find peaks in a dataset (e.g., highest values within a range):

=ARRAYFORMULA(IF(A2:A>MAX(A1:A3), “Peak”, “”))

Alternatively, using conditional formatting:

Select data range.
Click Format > Conditional formatting.
Use formula =A1=MAX(A$1:A$100).
Apply formatting.

Embed Google Sheets to Google Colab

To embed a Google Sheet in Google Colab:

Use gspread Python library:
import gspread
from google.colab import auth
auth.authenticate_user()
gc = gspread.authorize(GoogleAuth())
sh = gc.open(“Sheet Name”)
worksheet = sh.sheet1
data = worksheet.get_all_values()
print(data)
Alternatively, use the public URL of a sheet with Pandas:
import pandas as pd
url = “https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/export?format=csv”
df = pd.read_csv(url)
print(df.head())

Formule Google Sheet SI Contient Texte

To check if a cell contains specific text and return a value:

=IF(REGEXMATCH(A1, “word”), “Yes”, “No”)

Alternative:

=IF(ISNUMBER(SEARCH(“word”, A1)), “Yes”, “No”)

Use this in filtering or categorizing data based on text presence.

Conclusion

Mastering Google Sheets can significantly boost productivity and streamline data management. From formatting numbers to linking sheets across accounts, these essential tips will help you navigate and optimize your workflow. Keep experimenting with formulas and features to unlock Google Sheets’ full potential!

Click Writes