Tip #8: Using RAND and RANDBETWEEN functions to generate random data sets

Tip #8: Using RAND and RANDBETWEEN functions to generate random data sets

Have you ever spent hours working on a sample data record for a demonstration? We're here to show you how to do it in less time. If you want to generate a sample data set for a demonstration, or just to learn how a pivot table works, using the RAND and RANDBETWEEN functions can be a savior. With these functions, you can generate random data that includes text, numbers, dates, and even cell references.
 


How to use them 

The RAND function generates random values with no defined limits, while RANDBETWEEN, as its name signifies, returns random values within a specified range of values. Let's see how they can be used to generate various data sets.
 

Random numbers 

To get random numbers, you can use these functions the default way (i.e. without any special changes). 



Example
=RAND() returns float values between 0 and 1
=RANDBETWEEN(10;100) returns values between 10 and 100, inclusive
 

Random text values 

To get random text values (letters of the alphabet), you can use the RANDBETWEEN function in combination with the CHAR function. The numbers given as the input must be between 65 to 90 for capital letters and 97 to 122 for small letters.



Example
=CHAR(RANDBETWEEN(65;90)) returns capital letters
 

Random date values 

To get random date values, you can use the RANDBETWEEN function and specify the start and end dates. If the result is returned in the form of a date-time serial number, apply the Date format to the cells to get the desired result.



Example
=RANDBETWEEN("1/2/2022";"19/2/2022") returns dates from the given date range
 

Random cell references

To get random cell references as the result, use RANDBETWEEN function in combination with ADDRESS function. You can also choose the reference type to be absolute or relative.



Example
=ADDRESS(RANDBETWEEN(1;100);RANDBETWEEN(1;5);4) returns random cell references between columns A to E and rows 1 to 100.
 

Random values from a list 

If you wish to get random values from a specified data set or range of values, you can use RANDBETWEEN in combination with INDEX function.



Example 
=INDEX(L2:L8;RANDBETWEEN(1;5);1;1) returns random values from the cell range L2:L8.
 
Apart from the above mentioned functions, you can also use RANDARRAY and SEQUENCE to get an array of random and ordered data respectively! Are there any specific features you'd like to learn more about in future tips? Tell us in the comments below! 
 
Join the Zoho Sheet community and stay up to date on the latest features, interact with fellow users, and learn from the team behind Zoho Sheet. For more such exciting tips follow our Tips and Tricks forum.

    • Sticky Posts

    • Tip #20 - Three things you probably didn't know you can do with picklists

      Hello Zoho Sheet users! We’re back with another quick tip to help you make your spreadsheets smarter. Picklists are a great tool to maintain consistency in your spreadsheet. Manually entering data is time-consuming and often leaves typos and irregular

    • Tip #18: 6 Trendlines and when to use them in your spreadsheet data?

      Charts are a great tool for visualizing and interpreting large chunks of data in spreadsheets. Zoho Sheet offers you 35+ chart options, along with AI-powered chart recommendations based on the data set you select. There are various chart elements that

    • Tip #17: Easily share spreadsheets with non-Zoho account users

      With efficient, collaboration-friendly options, Zoho Sheet is a popular choice among users. Sheet's external share links help you collaborate more effectively by sharing your spreadsheets with anyone outside your organization, including users who do not

    • Tip #16: Ask Zia your data-related questions and get powerful insights

      Zoho Sheet provides a variety of tools and functionalities to help you analyze your data from end to end. But what if you could analyze all your data sets with a single AI-powered tool? Meet Zia, our smart virtual assistant who will answer all your data-related

    • Tip #7: Four things you didn't know you can do with Conditional Formatting

      Conditional formatting helps you highlight and visualize data in your spreadsheet based on provided rules. Zoho Sheet offers three types of conditional formatting: Classic, Color Scales, and Icon Sets. If you're already familiar with how Conditional Formatting works, here are some bonus tips for Classic formats: Customize rules with formulas Zoho Sheet allows you to create personalized rules based on formulas. For example, in a task list you can use customized formulas to highlight tasks which are

    • Recent Topics

    • Automated log-out/session end

      I'm concerned about security of our data. Is it possible to set an automatic time-out for user sessions on Zoho CRM, after a certain period of inactivity or when the session reaches a certain duration (12 hours perhaps)? 

    • Subform auto populate values

      Hi Team, I’m trying to retrieve values from Zoho People using API functions and dynamically populate them into a subform. For example, I’ve created a form with several fields that users will fill out. Based on their input, I need to fetch records from

    • What is New in CRM Functions?

      What is New in CRM Functions? Hello everyone! We're delighted to share that Functions in Zoho CRM have had a few upgrades that would happen in phases. Phase 1 An all new built-in editor for better user experience and ease of use. ETA: In a couple of days.

    • Gantt Chart - Zoho Analytics

      Are there any plans to add Gantt Charts capabilities to Zoho Analytics?

    • WhatsApp Calling Integration via Zoho Desk

      Dear Zoho Desk Team, I would like to request a feature that allows users to call WhatsApp numbers directly via Zoho Desk. This integration would enable sending and receiving calls to and from WhatsApp numbers over the internet, without the need for traditional

    • Identify long running sync jobs/tables

      My sync process causes strain on my production database and I'd love some tools/alerts to help me identify which tables are taking the longest. The current screen only shows 3 tables at a time and truncates the last fetch time so that it is very cumbersome

    • Temporarily rate limited due to IP reputation.

      We have suddenly started receiving the following Mail Delivery Status Notification: Diagnostic-Code: 4.7.650 The mail server [136.143.184.12] has been temporarily rate limited due to IP reputation. For e-mail delivery information, see https://aka.ms/postmaster

    • Associate emails from both primary and secondary contacts to deal

      We need to associate emails from multiple contacts to a deal. Please advise how this can be achieved. At present, only emails from primary contacts can be associated. Thanks

    • New integration: Zoho Sign for Zoho Projects

      Hey there! We’re excited to announce the brand-new Zoho Sign integration for Zoho Projects! With this integration, users can now send documents for signatures, track their progress, and manage approvals—all without leaving Zoho Projects. This bridges

    • Update to attachment display in ticket threads

      This enhancement will provide faster access for support teams and end-users, significantly boosting productivity for everyone. Get ready for a more efficient and satisfying experience! Immediate benefits Faster ticket rendering reduces wait times and

    • Narrative 15: Blueprint - Automate, guide, and transform your support processes

      Behind the scenes of a successful ticketing system: BTS Series Narrative 15: Blueprint - Automate, guide, and transform your support processes Even organizations that deliver quality products and services can face low customer satisfaction when their

    • Different MRP / Pricing for same product but different batches

      We often face the following situations where MRP of a particular product changes on every purchase and hence we have to charge the customer accordingly. This can't be solved by Batch tracking as of now so far as I understand Zoho. How do you manage it as of now? 

    • ZOHO BOOKS - RECEIVING MORE ITEMS THAN ORDERED

      Hello, When trying to enter a vendor's bill that contains items with bigger quantity than ordered in the PO (it happens quite often) - The system would not let us save the bill and show this error: "Quantity recorded cannot be more than quantity ordered." 

    • Refresh frequency

      Dear Zoho Team, I really, truly appreciate that Zoho Books gets frequent updates. As a matter of fact this is how a good SaaS company should stay on top. However, I feel that I have to hit refresh almost every day. This was exciting at the beginning but

    • Refund

      My plan expired today, and I updated my payment details with a new credit card. At the same time, I wanted to downgrade, but the system wouldn’t allow the downgrade until the payment details were updated. As a result, I was charged for the same plan before

    • Calling Function via REST API with API Key gives 401 using Zoho Developer

      Hi, I created a couple of functions using the one month trial of Enterprise edition, which I was able to call using the API Key method from Postman and from an external site. Now that my trial has expired, I have created the same functions in the Developer

    • Error due to - 'Internal Exception' when uploading Sign-generated PDF file to workdrive via Deluge in Zoho CRM

      Hi I wasnt getting this error a few days ago and my code had not changed, so I'm wondering if there's a Zoho bug somewhere? I am downloading a PDF file from a Zoho Sign url using invokeurl and then uploading it to a Workdrive folder using zoho.workdrive.uploadFile.

    • Embed CRM record images in email templates

      I have email templates that I want to embed dynamic images in their body - not as an attachment. For the context, the image is a QR code individual to each contact. So there are couple of challenges for which I think there is no solution in CRM: 1/ I

    • Assign multiple departments to multiple helpcenters

      Hi there! I have a reseller company for a software and I'm using Zoho Desk as my helpcenter and ticket management system. The software is great and I would like to make a suggestion! With multi-branding activated, your departments that visible in help

    • Zoho Desk Training

      Hello, We've had Zoho desk for a while now, but we run into issues occasionally, and I was wondering if there was a customer who currently uses it and really enjoys the functionality, that would be wiling to chat with us?

    • PO Based Advance payment to Vendor

      We recommend to introduce a provision at PO to make advance payment to vendors and auto apply that advance paid later at the time of Vendor Bill submission for that PO. This will help us track PO-wise Total Payments.

    • How to get Quickbooks Desktop Info into Zoho?

      Our team has used Quickbooks desktop for years and is looking at switching to Zoho books in 2026. I want to bring all old sales history over since we use Zoho CRM. I can export Item sales history and generic sales orders from Quickbooks desktop. How do

    • ZeptoMail API Request

      We tried to send mail using ZeptoMail using Django. Following is my payload {'from': {'address': 'abc@abc.com'}, 'to': [{'email_address': {'address': 'xyz@xyz.in', 'name': 'Bhavik'}}], 'subject': 'Report Name', 'htmlbody': '<p>Test</p>'} Following is

    • Mass import of documents into Zoho Writer

      I'm using Google's word processor at the moment but feel that Zoho does a better job (on the online apps market). Iwant to move my documents (about 50-70) to Zoho but it seems to me that I have to import them seperately. Is it already possible to upload several documents at a time or is this a forthcoming feature? Cheers Rolli :?:

    • Add home page or dashboard in CRM customer portal

      is it possible to add home page or dashboard in CRM customer portal?

    • User Tips: How to change the the label display name of a system defined field

      Most users know how to change field label names via Settings > Modules & Fields but if you want to change the name of a system defined field you can’t as there is no “edit properties” option.  However with a simple hack you can edit any system defined

    • Search not working!

      I have items in my notebook tagged but when I search for a tag nothing comes up! Any fix for this?

    • Zoho CRM Community Digest - October 2025 | Part 1

      Hello Everyone! Here's a quick recap of first two weeks of October! Product Updates: Zoho CRM Android App Update: Surveys, Blueprints, and Smarter Mobile Features! Zoho CRM’s Android app just got a useful upgrade. You can now share records, upload your

    • Automate onboarding emails with CRM Workflow and Accounts module

      We’re a B2B SaaS company selling to public-sector organisations. Each organisation is stored as an Account in Zoho CRM, and each organisation typically has multiple associated Contacts. Our backend syncs product-usage data (setup status, user activity,

    • Important update: Enhanced security measures for account operations in Zoho Cliq

      Greetings from the Zoho Cliq team! We’d like to share an important security update that has an influence on some admin actions such as password reset, MFA reset, and MFA backup code generation. What’s changing? With our latest security enhancements, these

    • Sales Receipts Duplicating when I run reports why and how do we rectify this and any other report if this happens

      find attached extract of my report

    • No Functional Autosave or Manual Save Button

      Application : Zoho Notebook So I wanted to try Zoho Notebook(On Ubuntu) as an application, I installed the application and went solving my LeetCode problems visually(Drawing mode), at one point the app just stopped saving anything... Every time I tried

    • Enterprise subscription support

      My organization sells subscription services to enterprise customers, which is a different model from the consumer subscription model that Zoho Billing has been designed to support and I beleve this capability should be added. An enterprise subscription

    • Issue with Creator's IF logic

      Hi, I found the following code produces unexpected results: if(-1.0 < 0.0000000) {       info "True"; } else {       info "False"; } if(-1.0 < 0.000000) {       info "True"; } else {       info "False"; } The output returned is: False True However, the

    • Need option to send Package PDF in shipment email (Shipment PDF is missing Lot info)

      Is there any way to automatically attach the Package PDF instead of (or alongside) the Shipment PDF in the notification emails? We really need this feature because the default Shipment PDF creates a blind spot for our customers. It does not display Batch/Lot

    • zoho creator view is not present in the workspace and blank reports

      Hi Support,  Users who have "write" permissions keep getting this error for all of our embedded reports all of a sudden. See screen shot below: Meanwhile, my developer permissions account sees a blank screen in view and edit mode as shown in the screenshots

    • Customize portal email template

      Can i fetch only first name of the user in portal email template instead of the below code Hi ${User.FULL_NAME}

    • Can't we let users decide which options they'd like to add at embed widget?

      It seems embed widget DOES NOT offer a feature, where users can choose options upon subscribing plans. What Zoho has instead, is that admins have to manually create plan with options. How come no one in Zoho dev team never raised issue about usability

    • Reupload and rename from one field to another field (file upload)

      Hi Everyone, Sorry, i have question to use invoke url for rename and reupload attachments file to another field. Tested on development mode. Zoho C6. Refer to https://www.zoho.com/creator/help/api/v2/upload-file.html look my error notification. Does anyone

    • Printing Multi-Page Reports (PDF Export)

      Hi, I am moving a report from Google's Looker Studio to Zoho Analytics and trying to reproduce the Looker page by page dashboard editing experience. With Google, what you see is what you get when you print to PDF. But I can't seem to create the same experience

    • Next Page