Often we have to start with a sample dataset for experimentation or verification even if the dataset isn’t based on real-life data. In many cases, people start manually typing their own data to create the sample, or search online for some sample datasets. There’s a better way! All data can be automatically generated within Excel for those purposes to populate your worksheets. Here I show 3 examples: 1) How to generate rows of Dates. 2) How to generate rows of Time. 3) How to generate weekday names.
All these are created using variants of Random functions. See the animations below for step by step instructions. For the Time animation, the Number format selection is at the very bottom: “More Number Formats…” and you’d select Time from the pop-up dialog (that part got clipped in the video).
The ideas shared here can be used for any data types for any length of rows and columns you want. Note that all values will be refreshed every time the sheet is refreshed, opened, or a new calculation takes place elsewhere in the sheet…which is not always desirable. If you want to just generate the dataset and lock it (as I do), then after the data generation, copy the cells/range and paste as Values into a new column (or overwrite where the formulas were). All your data values will be static/fixed from then on.
Generating Date and Time Values
Can you guess why I used 33 to 83 for time generation and divided the results by 100?
Generating Weekday Names
Hope these tips will save you immense amount of time! I also have created name, and email generators (among others) that apply similar techniques.
NOTE: For more detailed explanation of this, actual worksheet, formulas used, etc. feel free to contact me directly as I may offer assistance in the topic(s) with help of donation (since I do not do this for a living and my time is extremely limited). Please see Home page for details/link.
