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 #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 #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 #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 #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

• Tip #15: Stop guessing and start estimating with Zoho Sheet's Goal Seek

  Goal Seek is an ideal spreadsheet tool for devising estimates, as it helps you retrieve your desired output value when the input values are not known. In other words, Goal Seek does back-solving to find input values once you've provided an output value.

• Recent Topics

• Calendar Bookings in Recruit

  Hi there, We have recently started using Zoho recruit and although it has some great functionality there are a few gaps that are causing real headaches. One of those being how interviews are scheduled. The majority of our hiring managers are field based

• Getting Error : Developer Tool Detected

  Hi Team, Getting the error during open the portal, error attached on the same ticket. Please check and help us to resolve the same.

• Send Zoho Creator Template by Email or sendemail

  Hello All Question:- How we can send the Zoho creator email template using the send email by the workflow or using the Function? by the Workflow sendmail [ from: zoho.adminuserid to: "zohodeveloper@yopmail.com" subject: "Test Template" message: "Test

• Upgraded to Zoho One but Zoho Meeting still says Free Plan

  I signed-up for the Zoho One plan. When exploring the applications included, I came across a problem with Zoho Meeting. It says it's the free plan. I emailed support but they sent me a link that doesn't work and, when I found the article on my own, it

• 新年のご挨拶、直近のイベントスケジュール

  🎍🐍謹賀新年🎍🐍 ユーザーの皆さま、明けましておめでとうございます！ コミュニティチームの中野です。 本年もよろしくお願い致します。 昨年のZoho Japanコミュニティでは、東京・大阪・名古屋・福岡 4都市でのユーザー交流会開催や、自社最大イベント「Zoholics Japan 2024」でZoho Championの皆さまとのパネルディスカッション、10月の東京ユーザー交流会では本社CRMプロダクトマネージャーを招きロードマップ解説セッションの実施、さらにZohoアンバサダープログラムのローンチ（近日公開予定）など、新たな取り組みに挑戦しました。

• How to create Item in the Eazy ERP Software ?

  Item Master is used for the creation of an item in the Eazy ERP Software. It is necessary to create items to maintain the stock and pass the transactions in the software. Go to Control Panel > Store > Item Master. An Items window will appear. The user

• Directly Edit, Filter, and Sort Subforms on the Details Page

  Hello everyone, As you know, subforms allow you to associate multiple line items with a single record, greatly enhancing your data organization. For example, a sales order subform neatly lists all products, their quantities, amounts, and other relevant

• Request to Customize Module Bar Placement in New Zoho CRM UI

  Hello Support and Zoho Community, I've been exploring the new UI of Zoho CRM "For Everyone" and have noticed a potential concern for my users. We are accustomed to having the module names displayed across the top, which made navigation more intuitive

• Client script: Can not choose a date field for an onChange field event

  Hi Zoho Team Why can't I choose one of my date fields to trigger an onChange event? Is this a client script limitation, or something wrong with my instance? If it is a limitation, is this mentioned in the documentation anywhere? Thanks. Marcus

• How to upload mass inventory quantity adjustment with batch number?

  I wanted to clean-up our inventory list; however, I have trouble performing the task as I don't know how to mass upload inventory adjustment with batch numbers. I don't want to do it manually as there are if not thousands at hundreds of inventory tr

• Power BI connector (Zoho Creator) to Zoho Projects

  How can i connect power bi to Zoho Projects? domain is zoho.com How can i find workspace name, application link name and Report link name?

• Upgrade User

  Hi Sir/Madame, I have 7 users in my organisation but I need upgrade more space only 2 users. Is it possibe? Thanks

• Dashboard Auto-refresh

  Why is there no Auto-refresh on the Dashboard? People have been asking for the last 6 years for this function from what I can see in my reasearch, it seems like a very simple intergration for Zoho to achieve. on Sept. 2, 2014, this was added: Support

• Console error with widget in View mode, not present in Edit Mode with ZOHO.CRM.API.updateRecord(config)

  hello i have that function to update an account records (from a widgets created with sigma) function handleCopyButtonCRMClick() { console.log('handleCopyButtonCRMClick'); /* * Fetch Information of Record passed in PageLoad * and insert the response into

• add meeting link

  i want to add A meeting link in the mail when the user book the appointment then with the conformation mail i want to add meeting link there

• Unable to add Guest Members

  We are having issues adding Guest Members to our Cliq account. We have sent out a number of request but it seems that only some people are able to access the platform. Others have received a message stating that they need to be granted access from an

• Zoho One - White Label

  Releasing a white-label feature for Zoho One, or any software or service, can offer several advantages and benefits for both the company providing the software (Zoho in this case) and its users. Here are some key reasons for releasing a white-label feature

• Missing parameters in request, any way i can figure out what parameters i need to add?

  const url = "https://people.zoho.com/people/api/forms/json/P_Task/insertRecord"; const inputData = { "Status": "Open", "Description": "Task to set up and configure Zoho Mail on desktop application.", "CreatedTime": "01-Jan-2025 10:30 AM", "Due_Date":

• Can you modify "Last Activity Time" in deluge? If so what's the field name?

  I need to perform some bulk modifications on records in the Leads module, but I need to avoid changing the "last activity time" or "date modified" because I am using those fields to filter and sort leads for follow-up action. I cannot find an answer anywhere

• Running Total % in Pivot with filters

  Hi there, I have seen a few posts on this topic, but i cant seem to find one that will work when applyig filters to the data. I have Rows and Data in a pivot view I want to show the running total of revenue as a % of the total for the data set. If i add

• Included in Zoho One?

  Will LandingPage eventually be included in Zoho One?

• Add an "Impersonate" feature to Zoho Projects

  It would be nice to have an "impersonate" feature added to Zoho Projects that would allow administrators to impersonate employees to enable administrators to see for themselves what employees can and cannot see in the system. I am aware of all of the

• Select CRM Custom Module in Zoho Creator

  I have a custom module added in Zoho CRM that I would like to link in Zoho creator.  When I add the Zoho CRM field it does not show the new module.  Is this possible?  Do i need to change something in CRM to make it accesible in Creator?

• GROUPING OF CUSTOMER

  SIR PLEASE ADD GROUPING OF CUSTOMER IN ZOHO BOOKS

• Zoho LandingPage is integrated with Zoho One!

  Greetings to the Zoho One users out there! We're delighted to let you know that Zoho LandingPage is available in Zoho One too! With Zoho LandingPage, you can host custom-made landing pages, and persuade the visitors to dive deeper by making further clicks,

• 2024 Wrap: Rediscover these features and enhancements in Zoho CRM

  Hello everyone! I wish all of you a joyful and prosperous 2025! As we welcome 2025, I’m excited to share a recap of the year 2024 and highlight some of the coolest new features and enhancements we’ve added to the Zoho CRM platform. Last year, we announced

• Allow "add new" option to picklists and multiselection fields from add or edit pages

  Hello zoho, please insert an add option (ie +)sign) to pick and multiselect fields so we can add new options while entering or editing records. For example. in my lead module, while adding a new record, I realized I had a new lead source. I went to my

• A quicker way to provide accountants access to Zoho Books, similar to Xero and Quickbooks

  Hey guys, I'm finding the procedure to give access to an external accountant to Zoho Books less than ideal. Having to create an account by Zoho instead of myself, and then wait for it to be given a license to then pass to the accountant seems a bit time

• This user is not allowed to add in Zoho. Please contact support-as@zohocorp.com for further details

  Hello, Just signed up to ZOHO on a friend's recommendation. Got the TXT part (verified my domain), but whenever I try to add ANY user, I get the error: This user is not allowed to add in Zoho. Please contact support-as@zohocorp.com for further details I have emailed as well and writing here as well because when I searched, I saw many people faced the same issue and instead of email, they got a faster response here. My domain is: raisingreaderspk . com Hope this can be resolved.  Thank you

• Update Candidate Status Through Workflow in Blueprint

  Hi Team,  We have a blueprint built out with custom functions that update particular fields based on candidate actions. When particular fields are updated we need to move the candidate forward in the blueprint. We tried to do this through a workflow,

• Zoho Canned respond do have a huge lag issue.

  Previously the Zoho canned respond works perfectly ... on once server update and all the Canned respond enconter huge lag... in the end cause most of the canned respond just shown code with /xxx and not the sentence....

• ShipStation and Zoho Inventory

  Hello, I am looking to sync zoho inventory with shipstation ZOHO INVENTORY           SHIP STATION Sales Order  ==>  create ORDERS INVOICE  <==    Shipments What exactly does BETA mean on the Shipstation connector?  This is required for me to sign-on in the next month. Thanks in advance for your efforts

• Saving slide elements

  I have created grouped items including text and animation that I want to use in later slides. (Like an animated logo) Is there a way to save these grouped elements in my library?

• Are downloadable product available in Zoho Commerce

  Hi all. We're considering switching to Zoho Commerce for our shop, but we sell software and remote services. Is there a features for downloadable products? I can't find any information about this. Thank you very much Alice

• Function #10: Update item prices automatically based on the last transaction created

  In businesses, item prices are not always fixed and can fluctuate due to various factors. If you find yourself manually adjusting the item rates every time they change, we have the ideal time-saving solution for you. In today's post, we bring you custom

• Move site from WIX to ZOHO Sites

  I have a simple website on WIX.  I am wondering if someone is available to help me move this website - https://www.videothreezero.com/ to ZOHO.  Michael  Boston

• zoho calendar week view - "super compact by default"

  every time i go to my calendar i have to re-engage the "super-compact view" for the week view...is there a way to make "super-compact" a default view so I dont have to keep on setting it manually?

• Change work hours per day for employees

  Hello, Is there a way to modify the work hours per day for employees in Zoho projects? This would be helpful for resource allocation to more accurately see when an employee who works 35 hours a week vs 40 hours has a full schedule. Thanks.

• Zoho CRM Automation Help: Send Email When Fault is Marked as Done & Module Relationships

  Hi everyone, I have the following User-Created Modules in Zoho CRM: Clients Assets Faults Handymen Every client can have multiple assets. Every asset can have multiple faults. Every fault is assigned to one handyman. What I Want to Achieve: ✅ I want to

• Adding New Domain to Zoho mail

  Hi, I have one Zoho account already called for example "Awesome Animals". Under this account I have one domain already setup with zoho mail, example: - awesomecats.com I have another website as well which I want to add under this "Awesome Animals" account,

• Next Page