|
Getting your Trinity Audio player ready...
|
Every great spreadsheet user starts with one essential skill — understanding basic functions.
Whether you’re working on sales reports, HR dashboards, or financial summaries, these basic functions in Zoho Sheet help you calculate, clean, and analyze data faster.
Zoho Sheet, much like Excel or Google Sheets, supports a powerful set of statistical, text, and logical functions in Zoho Sheets. But it also adds its own touch of simplicity and real-time collaboration, making data work easier for individuals and teams alike.
In this article, we’ll cover all the essential functions you’ll need to get started — grouped by their purpose and illustrated with real-life examples.
1. Basic Statistical Functions in Zoho Sheet
Let’s start with the most common and important set of formulas — the ones that deal with numbers and summaries.
1.1 SUM()
Purpose: Adds up a range of numbers.
Syntax:
=SUM(number1, [number2, ...])
Example:
You manage monthly sales data for different branches:
| Branch | January | February | March |
|---|---|---|---|
| Raipur | 15000 | 18000 | 17000 |
| Durg | 12000 | 14000 | 13500 |
| Bilaspur | 18000 | 20000 | 21000 |
To calculate total sales for Raipur:
=SUM(B2:D2)
Result → ₹50,000
Read More: All About SUM Function
1.2 SUBTOTAL()
Purpose: Returns subtotal values based on filters and operations.
Syntax:
=SUBTOTAL(function_code, range)
function_codedefines the type of calculation (e.g., 9 for SUM, 1 for AVERAGE).- Works dynamically — excludes filtered-out rows.
Example:
If you’ve filtered your sales data to show only “Raipur” and “Durg”, use:
=SUBTOTAL(9, C2:C10)
Result → Adds up only visible rows, not hidden ones.
Also Read: All About SUBTOTAL Function
1.3 AGGREGATE()
Purpose: A more advanced version of SUBTOTAL — lets you ignore hidden rows, errors, or nested subtotals.
Syntax:
=AGGREGATE(function_num, options, range)
Example:
To calculate the sum of sales but ignore errors:
=AGGREGATE(9, 6, B2:B10)
Result → Adds all valid numeric values and skips any #VALUE! or #DIV/0! errors.
Also Read: All About AGGREGATE Function
1.4 AVERAGE()
Purpose: Returns the average (mean) of numbers.
Syntax:
=AVERAGE(range)
Example:
To find the average sales of Raipur branch:
=AVERAGE(B2:D2)
Result → ₹16,667
Also Read: All About AVERAGE Function
1.5 COUNT() and COUNTA()
COUNT() — Counts only numeric values.
COUNTA() — Counts all non-empty cells (numbers + text).
Example:
| Employee | Target | Status |
|---|---|---|
| Aman | 50000 | Done |
| Riya | 45000 | Pending |
| Nitin | Done |
=COUNT(B2:B4) → 2
=COUNTA(B2:B4) → 3
Read More: COUNT and COUNTA Function
1.6 MIN() and MAX()
Purpose: Return the minimum and maximum value from a range.
Syntax:
=MIN(range)
=MAX(range)
Example:
=MIN(B2:B10) → Finds smallest sales value
=MAX(B2:B10) → Finds largest sales value
Read More: MIN & MAX Function
2. Text Conversion Functions
Text data often comes messy — uppercase, lowercase, extra spaces, or mixed cases. These functions help you clean them up.
2.1 LOWER(), UPPER(), PROPER()
Purpose: Change text case.
Syntax:
=LOWER(text)
=UPPER(text)
=PROPER(text)
Example:
| Name |
|---|
| AMAN KUMAR |
| riya sharma |
=PROPER(A2) → Aman Kumar
=LOWER(A2) → aman kumar
=UPPER(A3) → RIYA SHARMA
Read More: LOWER, UPPER & PROPER Function
2.2 TRIM()
Purpose: Removes extra spaces from text (except single spaces between words).
Example:
| Data |
|---|
| “ Zoho Sheet ” |
=TRIM(A2)
Result → Zoho Sheet
Read More: TRIM Function
2.3 SUBSTITUTE()
Purpose: Replaces old text with new text in a string.
Syntax:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Example:
If product codes changed from “OLD_” to “NEW_”:
=SUBSTITUTE(A2, "OLD_", "NEW_")
Result → “NEW_101”
Also Read: All About SUBSTITUTE Function
3. Text Functions for Extraction and Analysis
These functions help you pick parts of text, find positions, or calculate length.
3.1 LEFT(), MID(), RIGHT()
Purpose: Extract specific parts of a text string.
Syntax:
=LEFT(text, num_chars)
=MID(text, start_num, num_chars)
=RIGHT(text, num_chars)
Example (Invoice Numbers):
| Invoice |
|---|
| INV-2024-001 |
| INV-2024-002 |
=LEFT(A2,3) → INV
=MID(A2,5,4) → 2024
=RIGHT(A2,3) → 001
Read More: LEFT, MID & RIGHT Function
3.2 FIND() and SEARCH()
Purpose: Find the position of a substring.
Syntax:
=FIND(find_text, within_text, [start_num])
=SEARCH(find_text, within_text, [start_num])
Difference:
FIND()is case-sensitive.SEARCH()is not.
Example:
=FIND("-", "INV-2024-001") → 4
Read More: FIND & SEARCH Function
3.3 LEN()
Purpose: Counts total characters in a text.
=LEN("Smart Tejas") → 11
3.4 TEXT()
Purpose: Formats numbers or dates as text.
Syntax:
=TEXT(value, format_text)
Example:
=TEXT(TODAY(),"dd-mmm-yyyy") → 17-Oct-2025
Read More: TEXT Function
4. Joining Text in Zoho Sheets
When working with datasets like employee lists or invoice details, you often need to combine multiple cells into one.
4.1 CONCATENATE()
Purpose: Joins two or more strings.
Syntax:
=CONCATENATE(text1, text2, ...)
Example:
| First Name | Last Name |
|---|---|
| Aman | Kumar |
=CONCATENATE(A2," ",B2)
Result → Aman Kumar
Read More: CONCATENATE Function
4.2 CONCAT()
Purpose: Shorter and newer version of CONCATENATE.
=CONCAT(A2, " ", B2)
Read More: CONCAT Function
4.3 TEXTJOIN()
Purpose: Joins text with a specified delimiter and can ignore empty cells.
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2...])
Example:
| Name | Dept | Location |
|---|---|---|
| Riya | HR | Raipur |
=TEXTJOIN(", ", TRUE, A2:C2)
Result → “Riya, HR, Raipur”
Read More: TEXTJOIN Function
4.4 Ampersand (&) Operator
Purpose: Simplest way to join text.
Example:
=A2 & " " & B2
Result → Aman Kumar
5. Rounding Functions in Zoho Sheet
When dealing with financial data or metrics, precise rounding is crucial for readability and consistency.
5.1 ROUND()
Purpose: Rounds a number to a specified number of digits.
Syntax:
=ROUND(number, num_digits)
Example:
=ROUND(125.567,2) → 125.57
5.2 ROUNDUP()
Purpose: Always rounds a number up, regardless of decimal value.
=ROUNDUP(125.231,0) → 126
5.3 ROUNDDOWN()
Purpose: Always rounds a number down.
=ROUNDDOWN(125.987,0) → 125
Read More: ROUND, ROUNDUP & ROUNDDOWN Function
Real-Life Scenarios
Example 1: Sales Dashboard Summary
You have a dataset of monthly sales.
You can calculate:
- Total sales per region →
SUM() - Average sales per employee →
AVERAGE() - Highest sales month →
MAX() - Clean up product names →
TRIM()andPROPER() - Merge Region & Manager →
TEXTJOIN(", ", TRUE, B2:C2)
Example 2: Employee Directory
| First Name | Last Name | Department | Employee ID |
|---|---|---|---|
| Aman | Kumar | HR | HR101 |
| Riya | Sharma | IT | IT203 |
Functions to apply:
- Full Name →
=A2&" "&B2 - Department Code →
=LEFT(D2,2) - Clean Extra Spaces →
=TRIM(A2)
Example 3: Financial Report Formatting
Convert total sales values to readable format:
=TEXT(A2, "$#,##0.00")
and round off decimals using:
=ROUND(A2, 2)
Summary
| Category | Functions | Purpose |
|---|---|---|
| Statistical | SUM, SUBTOTAL, AGGREGATE, AVERAGE, COUNT, MIN, MAX | Calculate totals, averages, and summaries |
| Text Conversion | LOWER, UPPER, PROPER, TRIM, SUBSTITUTE | Clean and format text |
| Text Analysis | LEFT, MID, RIGHT, FIND, SEARCH, LEN, TEXT | Extract and find text patterns |
| Joining | CONCATENATE, CONCAT, TEXTJOIN, & | Combine multiple values |
| Rounding | ROUND, ROUNDUP, ROUNDDOWN | Round numbers cleanly |
Conclusion
Mastering these basic functions in Zoho Sheet will make 70% of your daily spreadsheet tasks effortless. Whether you’re summarizing reports, cleaning messy data, or formatting names and numbers — these functions will always come in handy.
In the next tutorial, we’ll move to Logical and Lookup Functions in Zoho Sheets, where we’ll explore IF(), AND(), OR(), and real-time lookup formulas like VLOOKUP() and INDEX MATCH.
Pro Tip:
If you’re migrating from Excel or Google Sheets — the formulas here are fully compatible. You can even copy-paste them directly in Zoho Sheet with minor syntax adjustments.
What’s Next?
In the next post, we’ll learn about the Logical and Conditional Functions in Zoho Sheets