Function Problems

How to Create a Refreshable Excel Random Sample with Fixed Percentages

Nimra Malik Sep 28, 2026 869 views

Question details

The user needs to select a random sample of 100 files from a growing dataset while maintaining fixed proportion percentages across multiple categories.

How to Create a Refreshable Excel Random Sample with Fixed Percentages

Product: Excel
Device & OS: not provided
Scenario: Extracting a representative random sample of files (such as locations, image types, and camera types) for auditing or testing without skewing the distribution.
Observed behavior: Using standard RAND() formulas and basic sorting results in purely random selections that fail to meet the required categorical quotas.

Before you start

Format your source data as an official Excel Table (Ctrl + T). This ensures that any formulas or Power Query connections will automatically include newly added data when refreshed.

Solution 1Recommended

Use Power Query to Build a Refreshable Stratified Sample

Power Query is the most reliable method for handling dynamic data growth while applying fixed quotas to categorical groups.

By mapping out your exact category percentages in a separate table, you can merge it with your raw data in Power Query, assign random numbers, and group them to pull the precise number of records needed per category.

Define Your Quotas

Create a new Excel table detailing each category and the exact number of rows needed to meet your percentage quota (e.g., 'Location A', 30 rows).

Load Data to Power Query

Select your main dataset, navigate to the Data tab on the Ribbon, and click 'From Table/Range' to open the Power Query Editor.

Add a Random Number Column

Go to the Add Column tab, select Custom Column, and use the formula =Number.Random() to assign a unique random value to every row.

Sort by Random Value

Click the drop-down arrow on your new Random column and choose 'Sort Ascending' to shuffle the dataset.

Merge with Quotas and Keep Top Rows

Merge your main query with your Quota table based on the category column. Group the data by category, using the Table.FirstN function in your Advanced Editor to keep only the specific number of rows dictated by your quota.

Use Power Query to Build a Refreshable Stratified Sample

Automatic Refreshing: Once configured, you only need to click 'Refresh All' on the Data tab to generate a completely new, mathematically accurate sample anytime your raw data grows.

Solution 2

Use Dynamic Array Formulas to Extract Proportionate Samples

For users preferring standard workbook functions over Power Query, combining FILTER, SORTBY, and RANDARRAY can pull specific quotas per category.

Efficient Data Analysis

Create Stratified Random Samples in WPS Spreadsheet

WPS Spreadsheet supports advanced array formulas and robust table formatting, making it simple to build refreshable, proportion-based random samples for your expanding datasets.

1. Format Data as a Table: Select your data range and press Ctrl + T to format it as a Table, allowing dynamic updates when new records are added.
2. Assign Random Values: Create a new column and input the =RAND() formula to assign a temporary random decimal to each row.
3. Filter by Target Category: Utilize built-in Sort & Filter tools or dynamic array formulas to isolate specific categories based on your predefined quota table.
4. Combine Sample Results: Stack your filtered results together into a new worksheet to form the final 100-file sample maintaining the exact percentages.

Fully compatible with Microsoft Excel's advanced data functions and array formulas.High-performance processing that handles large and growing datasets smoothly.Free and intuitive interface for seamless data manipulation and data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't basic sorting work for fixed percentages?

Basic sorting of a purely random column selects the top 100 items regardless of category. Because randomization naturally fluctuates, this almost always distorts the required categorical distribution, leaving you with incorrect quotas.

How do I stop my random sample from constantly changing?

Because RAND and RANDARRAY are volatile functions, they recalculate upon every edit or refresh in the workbook. To freeze your random sample permanently, highlight the generated values, copy them, and select 'Paste Special > Values'.

Can I automate the percentage quotas if the total dataset size changes?

Yes. Instead of hardcoding the number of files per category, multiply your desired percentage by your total target sample size (e.g., 100). By linking this calculation to your extraction formula or Power Query steps, the row count will update dynamically based on the percentage requirement.

Excel & Data Problems