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.

• Topic Participants

• Jaya Shankari S

• 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

• Recruit paid support?

  Hi all, Could anyone who has paid support package advise if it provides value for money with regards to support response times? Exploring the idea as unfortunately when we have faced issues with Recruit it has been a 7+ day timescale from reporting to

• Ticket Status email

  Good day, This was discussed in the past, but it would be helpful if we could have the system assign a custom response to a status. We have various statuses for tickets, e.g. "closed due to no response", or "Pending Status", it would be helpful for the

• Future Orders - Due Date

  Hi In my role, I can receive tickets where the work required is requested months in advance. Using a Future Orders option, which I believe was setup under the On Hold status type, hides the Due Date, in all views/ticket etc. Whilst I understand the reasoning

• Introducing Withdrawal Reasons for Offers

  We’re excited to introduce a new enhancement to the Offer module that brings more clarity and accountability to every withdrawn offer. The Withdrawal Reason update ensures that each withdrawal — manual or automatic — is backed by a clear explanation,

• Zoho Creator customer portal users

  Hi, I'm in a Zoho One subscription with our company. I'm running a project now that involves creating a Zoho Creater application and using the Zoho Creator Customer Portal.  At most we need 25 customer portal users. In our Zoho One plan we only get 3

• GPS tracking only or Check out facility

  Dear Team, Zoho CRM is quite robust CRM but till date i was waiting for the feature of having GPS tracking of Sales employees which is a common demand by all customers for thier field sales executives. We cover them by saying that CRM provides Checkin

• Appraisals on Employee Information Profile

  Is it possible to show completed appraisals on each employee's "Employee Information" page? I would presume at the bottom - similar to the "Related Lists" concept in Zoho CRM. Obviously view access would be limited to employee and appropriate other roles

• Kaizen #220: Actions API - Webhooks APIs - Part 2

  Hello all!! Welcome back to the follow-up Kaizen post of Kaizen #219: Actions API - Webhooks APIs - Part 1. In the previous week, we covered how to configure a basic Webhook and how to include Headers, Body, and URL Parameters using both the POST Webhook

• Standard Payment Term is not pulled from account to quotation

  Hey Team There seems to be something off. I do have "Net 30" as my default payment term in Zoho Books for my customers. If, from the customer overview or quote section, I create a new Quotation, the payment terms field stays blank and doesn't get the

• Manage your invoices from Bigin's mobile app (iOS)

  Hello everyone! We're happy to announce that users can now integrate Zoho Books with the latest version of Bigin's iOS app. Zoho Books can be integrated with Bigin only via the web application. Users can view the Zoho Books tab in the detail pages of

• Systematic SPF alignment issues with Zoho subdomains

  Analysis Period: August 19 - September 1, 2025 PROBLEM SUMMARY Multiple Zoho services are causing systematic SPF authentication failures in DMARC reports from major email providers (Google, Microsoft, Zoho). While emails are successfully delivered due

• Accessibility controls and multilingual captcha authorization for Help Center users ​

  Hello everyone, As part of our ongoing effort to improve user experience, we are excited about the accessibility controls and predefined accessibility personas added in the Help Center, similar to what is already available in Zoho Desk. Help Center users

• Zoho Sign + Zoho CRM : la solution pour simplifier vos accords de vente

  La conclusion d’un contrat de vente nécessite de nombreuses étapes : préparation de documents, validations successives et collecte des signatures. Les équipes commerciales passent souvent par plusieurs cycles de collaboration et de révision, tout en assurant

• Item/service subtotal

  Just discovered & really pleased that we can drag to re-order the line items in Sales orders & Invoices, a very nice feature which doesn't seem to be documented? It would be nice to be able to insert a subtotal as a line item to complete this great feature

• Send email template "permission denied to access the api"

  Hello, Per the title, I'm trying to send a Zoho CRM Email template based on the advice given here: https://help.zoho.com/portal/en/community/topic/specify-an-email-template-when-sending-emails-in-custom-functions (I'd prefer to send right from Deluge

• how to Solve Conflict Invoices in Zoho POS

  Hello Team, I am facing a repeated issue in Zoho POS while saving a sale that contains service-based items. My products are intentionally created as Service (Non-Inventory) items because I do not want to track stock for them. However, every time I try

• Copy, Paste, Highlighted text

  when i copy paste text it gets highlighted. Fix this.  WTF is with this black highlight ? Fix copy pasting. Some people pay for using this mail service. 

• Pricing Strategies: #6 Common Mistakes while Configuring Pricing

  "Why does this month's billing feel messy again?" That was the exact line Priya muttered while staring at the spreadsheet full of edited pricing, one-off discounts and mismatched subscription details. Her business was growing, but the pricing machine

• Publish to LinkedIn via API

  Hi, Is it possible to publish a job opening to LinkedIn (paid job slots) if creating a job opening via api / deluge function? Or is the user required to manually publish via the job boards process? Many Thanks Adrian

• FSM - Associating and selecting Contacts based on Service Addresses

  Hi FSM team, I've come across an FSM limitation I wanted to share for improvement. I'm currently configuring FSM for a client who provides heating system install and maintenance services. The are often sub contracted by building management companies to

• Do Unpublished Products in Shopify Sync to Zoho POS?

  Will an unpublished product in Shopify sync to Zoho POS? I would like to know how Zoho POS handles products that are in draft/unpublished status in Shopify.

• Problem in Zoho POS frontend ERP.

  We are facing some difficulties in our billing and inventory processes and require your support to resolve them at the earliest. Customer Advance Payments We receive advance payments from customers for upcoming sales. Please guide us on how to record

• Categorize Items with Item Headers

  Hello customers, Did you ever want to classify items based on specific categories to help your customers understand your invoice better? With the new Item Header feature, you can easily categorize items in your invoices and estimates and give them a common title.  Item Headers are available in the Invoices, the Recurring Invoices and the Estimates module. It can be carried forward from estimates to invoices at the time of converting the estimates.  To add an item header: Go to the Estimates, Invoices

• Discount on the transaction level in POS

  "Is it possible to apply a discount on the total bill before tax in Zoho POS?" I want to apply a discount on the total transaction amount in Zoho POS, but the discount should be calculated before the taxes are applied. Is there an option to do this in

• Send Automated WhatsApp Messages and Leverage the Improved WhatsApp Templates

  Greetings, I hope all of you are doing well. We're excited to announce a major upgrade to Bigin's WhatsApp integration that brings more flexibility, interactivity, and automation to your customer messaging. WhatsApp message automation You can now use

• Jobs at multiple locations

  Hi A lot of times we have same job open at multiple locations. So I would like an option where we can create a job and choose multiple locations. Currently we have to create individual jobs for each location. If we can create one job and mention multiple

• Tag Adding Option in Kanban Card Customization Bigin CRM Mobile App

  I would like to request an option to add and display tags on Kanban cards in the Bigin CRM mobile app. This feature would make it easier to categorize deals and quickly identify priorities while working on the go.

• Empowered Custom Views: Cross-Module Criteria Now Supported in Zoho CRM

  Hello everyone, We’re excited to introduce cross-module criteria support in custom views! Custom views provide personalized perspectives on your data and that you can save for future use. You can share these views with all users or specific individuals

• Associating a Candidate to a Job Listing

  Hello, I am trying to use Zoho Forms embedded on my website for candidates to apply for a job opening. I want the form then to tie directly with zoho recruit and have the candidate be automatically inputed into Recruit as well as associated with the specific

• WhatsApp Voice Message Sending Option in Bigin CRM

  I would like to request a feature that allows users to send WhatsApp voice messages directly from Bigin CRM. This would help improve communication efficiency and make it easier for teams to respond quickly to customer inquiries.

• CRM: hosting a single html file in Zoho and displaying it as a widget

  I have seen that CRM offers the option of uploading a web project to Zoho itself and displaying it as a widget in CRM. The instructions then talk about setting a development environment with Node and developing an application to upload to Zoho. But I

• How to change position button transtition of Blueprint?

  Hi Everyone, Look at my screenshoot, it is possible move the reject button to the right? I couldn't find that setting in the blueprint. Thank you.

• Multi file upload

  Hi, I just wonder if one could upload multiple files in one shot, say between one and three files, without adding multiple File Upload fields? Thanks, Alalbany

• Sorting Descending in a lookup

  I have a case number lookup on multiple forms. I need the most recent added to appear at the top of the lookup or as the list of cases grom it's too much to scroll. Is there a way to have a look up sort descending?

• Auto-upload Creator Files to WorkDrive

  Hi everyone, I’m working on a workflow that uploads files from Zoho Creator to specific subfolders in Zoho WorkDrive, as illustrated in the attached diagram. My Creator application form has two multi-file upload fields, and I want—on successful form submission—to

• Asap Widget 2.0

  Where's the documentation for the new ASAP widget? https://www.zoho.com/desk/developers/asap/#introduction this one is outdated How can we dynamically navigate between different views? How can we prefill ticket forms using ASAP 2.0?

• Can the Trigger be changed?

  I'm afraid I already know the answer, but here goes... After activating a workflow (under Campaigns > Automations), Then later choosing to Edit the workflow, Can the Workflow's Trigger be changed? Currently the entire Trigger section + options are gone

• Marketer's Space: Proven tips to improve open rates – Part I

  Hello Marketers! Welcome back to another post in Marketer's Space! In this week's post, we'll discuss the ever-important concept of open rates. This will be a multi-part series, as we have a range of topics to cover. Open rates—which measure the percentage

• Can't send nor receive emails on my external email accounts since this morning

  Greetings, I have two external accounts integrated into Zoho mail through POP3 + SMTP. These accounts have always worked normally since the day I've set them up. Starting 10:30 AM this morning (Western European Time) both suddenly stopped sending and

• Creating Custom PDF Template from Form

  I am trying to create a custom PDF from form submissions. I have the standard subscription and it indicates that PDF forms are included. I cannot find anywhere to create PDF forms. I try to follow the instructions from here: https://help.zoho.com/portal/en/kb/forms/form-settings/pdf-settings/pdf-editor/articles/creating-your-own-pdf-template#Creating_your_template_from_the_scratch

• Next Page