Python in Excel: How to sample rows

Sampling rows from a table is common for analyzing subsets of large datasets, enabling quicker insights or testing without processing the entire dataset. In Excel, this is difficult because it lacks built-in random sampling functions, requiring complex formulas or manual steps that are time-consuming and error-prone for large tables.

In this post, we’ll explore simple techniques for sampling a table in Excel using Python. Download the exercise file below to follow along:

The first method we’ll cover is using the head() method to display the first five rows of the DataFrame. I’ll read the Excel table into Python as orders_df. This is a common approach for large datasets, allowing you to preview the data without overloading memory by printing everything.

orders_df.head()

By default, head() displays the first five rows, but you can easily customize this by adjusting the method’s first parameter. For example, let’s set it to 10:

orders_df.head(10)

Instead of just showing the top rows of the dataset, you can use head() for a top N analysis by chaining sort_values() to the DataFrame. Set ascending=False to avoid the default ascending order, which would give you a bottom N analysis:

orders_df.sort_values('order_amount', ascending=False).head(10)

Some operations, like the above sorting, cause the DataFrame’s index to be re-displayed because they modify the row order or structure, making the index a relevant part of the output.

Including reset_index(drop=True) in method chaining resets the index to a default sequential one and discards the old index, preventing it from appearing in the result:

orders_df.sort_values('order_amount', ascending=False).head(10).reset_index(drop=True)

Next, we can display the last few rows of the data using the appropriately named tail() method:

orders_df.tail()

Just like with head(), we can customize the number of rows with tail().

To sample rows randomly instead of selecting the first or last ones, Pandas offers the sample() method, which, by default, returns one randomly chosen row:

orders_df.sample()

The row returned in your workbook likely differs from the one shown above since it was chosen randomly. For reproducibility, set the random_state argument, and I’ll increase the sample size to 200 rows.

When sampling this many rows, you might exhaust the available rows; by default, Pandas avoids duplicates, but you can allow them by setting replace=True:

orders_df.sample(n=200, random_state=1234, replace=True)

Sampling rows in Excel is the kind of task that feels small. Until you realize how often you do it. Python in Excel offers greater control and less fuss to do this over native Excel options.

Want more practical tips like this?

Sampling rows is just one of the 15 bite-sized lessons in my Python in Excel: Quick Wins course, designed for Excel users who want fast results without the coding overwhelm.

$15 gets you lifetime access + a downloadable cheatsheet.

No fluff. No Python experience needed. Just real-world use cases you can apply today.

What questions do you have about sampling rows using Python in Excel, or about Python in Excel in general? Let me know in the comments.

The post Python in Excel: How to sample rows first appeared on Stringfest Analytics.