How Do You Stratify Data in Excel? (2026 Guide)

Averages hide things. A support team with a four-hour average response time might be answering half its tickets in twenty minutes and the other half in eight hours, and the single number tells you nothing about either group.

Stratification is the fix. You split the dataset into meaningful subgroups first, then look at each one separately, and the pattern the average was flattening finally shows up. It is one of the oldest ideas in quality control and one of the most useful things you can do in a spreadsheet.

Excel has no command called Stratify, which is why searching for it turns up surprisingly little. What Excel has instead is every component you need to do it properly: helper columns, lookup ranges, pivot tables and a grouping feature most people never open. This guide walks through the whole process.

How Do You Stratify Data in Excel?

To stratify data in Excel, you add a column that assigns each row to a category, then summarise the dataset by that column instead of as a whole. The category can come from a field you already have, or from a rule you write that turns a continuous number into bands.

For categorical strata, such as region or product line, the column already exists and you go straight to a pivot table. For numeric strata, such as order value or response time, you first need a helper column that converts each number into a band label, and the usual tools for that are nested IF statements or a VLOOKUP against a small bin table.

Once every row carries a stratum label, a pivot table with that label in Rows and your metric in Values gives you the stratified view: one line per group, with counts and averages you can actually compare.

The whole job typically takes under five minutes. The part that takes judgement is not the mechanics but choosing the strata, because a badly chosen split will hide a pattern just as effectively as no split at all.

Wondering which slices of your audience actually convert? Get a free SEO Stuff audit and we will break your traffic down for you.

What Stratification Actually Means

The word comes from geology, where strata are the visible layers in a rock face. The analytical idea borrows the image directly: a dataset that looks uniform from a distance is usually made of distinct layers, and the analysis is worthless until you separate them.

Stratifying versus filtering

Filtering shows you one group and hides the rest. Stratifying shows you every group at once, side by side, which is the whole point. The insight almost never comes from looking at one segment in isolation. It comes from noticing that segment A behaves nothing like segment B.

A filter is a temporary view you apply and remove. A stratum is a permanent property of each row that you can pivot, chart and compare against. That distinction matters when you build something other people will use.

Stratifying versus stratified sampling

These two get conflated constantly. Stratified sampling is a data collection method: you deliberately draw a set number of records from each subgroup so that small groups are properly represented in your sample. Stratifying data is an analysis method applied to records you already have.

They use the same underlying split, and the Excel technique for building the strata column is identical. The difference is what you do afterward, either sampling from each group or summarising each group.

Choosing your strata

The useful strata are the ones where you have a real reason to expect different behaviour. Time period, acquisition channel, customer tenure, geography, device type, order size, team or operator. If you cannot articulate why a group might behave differently, splitting on it usually just adds noise.

Two practical rules keep this from going wrong. Keep the number of strata small, ideally between three and seven, because a table with forty rows is not a stratification, it is just the raw data again. And watch your group sizes, because an average computed from six records is not a finding, it is a coincidence waiting to be over-interpreted.

It also pays to check that your strata are genuinely exclusive. If a record can land in two groups, the counts will not sum to the total and every percentage you calculate afterward will be quietly wrong. Excel will not flag this, so a quick check that the group counts add up to your row count is worth doing every time.

Step by Step: Stratifying a Numeric Column

Say you have a sales table with an order value in column C, and you want to see whether small, medium and large orders behave differently.

Build the bin table

1. In a spare area of the sheet, create two columns. In the first, list the lower bound of each band in ascending order: 0, 100, 500, 2000.
2. In the second, list the label for each band: Small, Medium, Large, Enterprise.
3. Give the range a name such as Bins so your formula stays readable.

Ascending order is not optional here. The approximate-match lookup in the next step walks down the list and stops at the last value that does not exceed your number, which only works if the list is sorted.

Add the stratum column

1. In an empty column, head it Stratum.
2. In the first data row, enter =VLOOKUP(C2, Bins, 2, TRUE). The TRUE is doing all the work, because it switches VLOOKUP into approximate-match mode.
3. Double-click the fill handle to copy it down the whole column.

On Microsoft 365 you can use =XLOOKUP(C2, $F$2:$F$5, $G$2:$G$5, "Unbanded", -1) instead, where the -1 asks for the next smaller match. XLOOKUP has the advantage of a built-in fallback value when a number falls outside every band.

If you prefer to avoid a lookup table, nested IFs do the same job inline: =IF(C2>=2000,"Enterprise",IF(C2>=500,"Large",IF(C2>=100,"Medium","Small"))). This is fine for three or four bands and becomes unmaintainable past that, which is why the bin table is the better habit.

Summarise by stratum

1. Select your data range including the new column and insert a pivot table.
2. Drag Stratum into Rows.
3. Drag your metric into Values, once as Average and once as Count.
4. Sort the rows into your intended band order if the pivot alphabetises them.

That count column is not optional. Every stratified table should show how many records sit behind each average, because that is the only way a reader can tell which rows to take seriously.

Want the same treatment applied to your keyword data? Claim a free SEO Stuff audit and see which segments are worth your time.

The Shortcut Most People Miss

If your strata are evenly spaced numeric bands, you can skip the helper column entirely. Pivot tables can group numbers on the fly.

Drop the numeric field itself into the Rows area, right-click any value in that column and choose Group. Excel offers a starting value, an ending value and an interval, and it will create the bands for you in place.

This is genuinely faster, and it is the right choice for a quick look. It has two limits worth knowing. The intervals must all be the same width, so you cannot do 0-100, 100-500, 500-2000 this way. And the bands live inside the pivot table, so nothing else in your workbook can reference them.

Dates get the better version of this feature. Right-click a date field in a pivot table and Group offers days, months, quarters and years, with multiple levels selectable at once. Stratifying by month or quarter takes two clicks and no formulas at all.

One warning on date grouping. Excel only offers it when every value in the column is a real date serial rather than text that looks like a date. If Group is greyed out, that is almost always the cause, and running the column through Text to Columns with a date format usually fixes it in one pass.

When Stratification Is the Right Move

Reach for it whenever an average is being quoted and you do not know what is underneath it. That is the core use case and it comes up constantly.

It is also the standard first step when a metric moves and nobody knows why. Stratify by the two or three most plausible dimensions and the change usually localises to one group, which turns an open-ended question into a specific one.

Before-and-after comparisons need it too. If you changed something in March and want to know whether it worked, comparing the overall average before and after is only valid if the mix of groups stayed the same. Stratifying first tells you whether it did.

And it is the honest way to report on anything with an uneven distribution. Revenue, session duration, time to resolution and basket size are all heavily skewed in most businesses, which means the mean is being dragged around by a small number of extreme records. A stratified table shows that immediately.

A useful test before you start: write down what you expect each group to look like. If the stratified table comes back matching your expectation, you have confirmed something cheaply. If it comes back different, you have found the thing worth investigating. Either outcome is worth five minutes.

Curious whether your best-performing pages share anything in common? Get a free SEO Stuff audit and we will group them for you.

Where It Falls Down

The most common failure is strata that are too fine. Split a thousand records across twenty-five groups and you have forty rows per group, which is not enough to say anything confident about any of them. Fewer, larger groups almost always beat more, smaller ones.

The second is choosing boundaries after seeing the data. If you slide the cutoffs around until a difference appears, you will find one, and it will not replicate. Decide the bands from something external, such as pricing tiers, service targets or round numbers everyone already uses, and then look.

The third is forgetting that the helper column is static when it should not be. A VLOOKUP-based stratum column recalculates with the data, but if you ever paste it as values, or if someone edits the bin table without telling you, the labels and the numbers quietly stop agreeing. Keep the bin table visible on the sheet rather than hidden away.

A fourth issue is unstable band edges. If one of your cutoffs sits right where the data clusters, a handful of records moving a few units across the boundary will swing both groups' averages noticeably. Placing boundaries in the sparse parts of the distribution rather than the dense parts makes the whole table far more stable month to month.

There is also a subtler problem known as Simpson's paradox, where a pattern that holds in every subgroup reverses when the groups are combined, or the other way around. Stratification is what reveals it, which is good, but it also means you cannot treat the stratified view and the overall view as interchangeable. They can genuinely disagree, and when they do the stratified one is usually the one telling the truth.

Stratification vs Other Ways to Split Data

Filtering answers a question about one group. Stratifying answers a question about the relationship between groups. If you find yourself applying a filter, noting a number, changing the filter and noting another, you wanted a stratification.

Slicers are filters with a nicer interface, and they are excellent for letting someone else explore a pivot table. They do not replace stratification, because the comparison still happens in the user's memory rather than on the screen.

Conditional formatting highlights individual rows that meet a condition. That is useful for spotting outliers and useless for comparing group behaviour, because it never aggregates anything.

Grouping rows with the outline tools in the Data tab is another thing people reach for by mistake. It collapses and expands sections of a sorted sheet, which looks like stratification on screen but produces no summary you can compare or chart. It is a presentation device, not an analysis one.

Power Query sits one level up. If you are stratifying the same dataset repeatedly, or the bin logic is complicated, building the stratum column as a Custom Column in Power Query means it applies automatically every time the data refreshes. That is the right answer for anything that becomes a recurring report rather than a one-off look.

Not sure how to segment your own search data? Book a free SEO Stuff audit and we will do the first pass with you.

Final Thoughts

Stratification is less a feature than a habit. The Excel mechanics take five minutes to learn and the judgement takes longer, because the value is entirely in choosing splits that correspond to something real about how the underlying thing works.

The practical version is short. Add a column that labels each row, keep the number of labels small, always show the count alongside the average, and decide your boundaries before you look rather than after. Use a bin table and VLOOKUP when the bands are uneven, pivot table grouping when they are not, and Power Query when you will be doing it again next month.

If your dataset is already in a pivot table and you want to go further with the summarising side of things, our guide to what a pivot table in Excel actually does covers the layout options that pair naturally with stratified data.

The payoff is consistent: you stop reporting one number that describes nobody, and you start reporting three or four that each describe somebody real.