Graphed LogoGraphed
Login

How to Stratify Data in Excel

Cody Schneider•

Looking at an average number for your entire business is like looking at a blurry photo - you get the general idea, but you miss all the important details. To get a clear picture, you need to break your data down into smaller, meaningful groups. This article will show you exactly how to do that by stratifying your data directly in Microsoft Excel.

What is Data Stratification?

Data stratification is the process of dividing a dataset into distinct, non-overlapping subgroups, or "strata," based on shared characteristics. Instead of analyzing all your data as one big chunk, you analyze each subgroup separately. This simple technique immediately uncovers patterns, pinpoints problems, and highlights opportunities that a single average would completely hide.

Imagine you run an e-commerce store and your average customer satisfaction score is a respectable 4.2 out of 5 stars. That seems good, right? But what if you stratify that data by customer loyalty status?

  • New Customers: 4.5/5 stars (They love the first-purchase experience.)

  • Repeat Customers: 4.3/5 stars (They're still happy.)

  • VIP Members: 2.8/5 stars (Something is seriously wrong here!)

The overall average masked a critical problem: your most valuable customers are unhappy. Without stratification, you would have missed this completely. This is why learning to slice and dice your data is one of the most practical analysis skills you can develop.

Step 1: Get Your Data Ready for Analysis

Before you can stratify anything, you need a clean and organized dataset. Following a few best practices here will save you headaches later.

Clean and Structure Your Data

Your data should be arranged in a simple tabular format where:

  • Each row represents a single record (e.g., a single sale, a specific customer, a marketing campaign).

  • Each column represents a single attribute or variable (e.g., Sale Date, Customer Name, Region, Purchase Amount).

  • There shouldn't be any merged cells or blank rows breaking up your dataset.

Take a moment to scan for inconsistencies. Are "California," "CA," and "california" all used to represent the same state? Fix these to ensure your groupings are accurate.

Convert Your Data to an Excel Table

This is arguably the most important preparatory step. Once your data is clean, turn it into a formal Excel Table. This is different from just having data in cells, it makes your data dynamic and easier to work with.

  1. Click anywhere inside your data range.

  2. Go to the Insert tab on the Ribbon and click Table, or just press the shortcut Ctrl + T.

  3. A small dialog box will appear. Ensure the correct range is selected and check the box that says "My table has headers." Click OK.

Your data will now be formatted as a table, complete with alternating row colors and filter dropdowns in the headers. This isn't just cosmetic. Tables automatically expand to include new rows, making formulas and charts based on them update automatically.

Method 1: Simple Stratification with Sort & Filter

The quickest way to start exploring strata is by using the built-in filtering tools that appear when you create an Excel Table. This method is perfect for quick, on-the-fly questions.

Let’s say you have sales data and want to analyze performance for just a single region.

  1. On your Excel Table, click the dropdown arrow in the header of the column you want to stratify by (e.g., "Region").

  2. In the filter menu that appears, uncheck "(Select All)" and then check the box next to the single stratum you want to view (e.g., "North").

  3. Click OK. The table will now only show rows that belong to the "North" region.

Now that your data is filtered, you can run calculations on just this visible subset. For example, to find the average sale amount for the North region, you can't use a simple =AVERAGE() formula, as it will calculate on the entire hidden dataset. Instead, use the SUBTOTAL function.

In a cell outside your table, type:

=SUBTOTAL(101, [Sale Amount])

(Be sure to replace "[Sale Amount]" with the reference to your sales column in the table.)

The 101 tells Excel to perform an AVERAGE calculation while ignoring hidden rows. This dynamic function is amazing for filtered lists. You can use other function codes as well - for example, 109 is for SUM, and 103 is for COUNTA (counting non-blank cells).

Pros: Fast, intuitive, and great for a quick look at one group at a time.

Cons: It’s manual and repetitive if you want to compare many strata, because you have to filter and calculate for each one.

Method 2: Advanced Stratification with PivotTables

When you need to compare all your strata at once, PivotTables are the best tool for the job. They are the most powerful feature in Excel for summarizing, grouping, and analyzing data. A PivotTable can automate the entire stratification process in seconds.

Let's use an example of analyzing marketing data, stratified by the advertising channel ('Ad Channel') and the 'Age Group' of the audience.

  1. Click anywhere inside your Excel Table.

  2. Go to the Insert tab and click PivotTable. The default options are usually fine, so just click OK.

  3. Excel will create a new sheet with a blank PivotTable builder on the right side of the screen.

  4. Now, drag and drop the fields into the different areas:

    • Drag the field you want to stratify by (e.g., "Ad Channel") into the Rows area. You'll instantly see a unique list of your ad channels.

    • Drag the metrics you want to measure (e.g., "Conversions" and "Spend") into the Values area. Excel will automatically sum them up for each channel.

Just like that, you have a stratified report comparing the performance of each ad channel! To change the calculation from Sum to Average or Count, just click the field in the Values area, select "Value Field Settings," and choose the calculation you want.

Adding a Second Layer of Stratification

The real power of a PivotTable is its ability to handle multiple layers. What if we want to see the performance by age group within each ad channel?

  1. Go back to your PivotTable Fields list.

  2. Drag your second stratification field (e.g., "Age Group") and drop it directly under the "Ad Channel" field in the Rows area.

Your PivotTable will instantly update to show a nested breakdown. Now you can see spend and conversions for each age group, neatly organized under each ad channel. This two-layer stratification allows you to answer far more sophisticated business questions, like "Which age group performs best on Facebook Ads compared to Google Ads?"

Pros: Extremely fast, dynamic, easily allows for multiple layers of data, and perfect for creating summary reports.

Cons: There is a slight learning curve, but it's well worth the investment.

Method 3: Creating Your Own Strata with Formulas

Sometimes, the strata you need don't exist in your original data. For example, you might have exact purchase amounts but want to group them into "Small," "Medium," and "Large" purchase brackets. You can create these custom strata yourself using a few simple formulas.

Using the IF Function

The IF function is perfect for creating simple, rule-based strata. Let's create the purchase brackets described above.

  1. Add a new column to your Excel Table and name it something like "Purchase Tier."

  2. In the first cell of that new column, enter a nested IF formula. Let’s define Small as less than $50, Medium as less than $500, and Large as anything else.

=IF([@[Purchase Amount]]<50, "Small", IF([@[Purchase Amount]]<500, "Medium", "Large"))

Because you're working in an Excel Table, the formula will automatically fill down for all the rows. Now you have a new column that you can use to stratify your data in a PivotTable or with the Filter tools.

Using the VLOOKUP Function

When you have more complex categories or need to map data from another list, VLOOKUP is a more scalable solution. Say you want to stratify your sales data by 'Product Category', but your sales report only contains a 'Product SKU'. As long as you have a separate table that maps each SKU to its category, you can bring that information over.

  1. Create a simple two-column lookup table separate from your main data. Column 1 should have the 'Product SKU', and Column 2 should have its corresponding 'Product Category'.

  2. Add a new column to your main sales table called 'Product Category.'

  3. In the first cell of that column, enter a VLOOKUP formula:

=VLOOKUP([@[Product SKU]], CategoryLookupTable, 2, FALSE)

This formula looks up the SKU from the current row, finds it in your lookup table (CategoryLookupTable), and returns the value from the 2nd column of that table ('Product Category'). Once again, you now have a new stratum ready for analysis.

Final Thoughts

Learning how to stratify your data in Excel is a fundamental step toward becoming truly data-driven. Moving beyond simple averages and using tools like Filters and PivotTables helps you see the detailed story your data is trying to tell, revealing insights that lead to smarter business decisions.

While Excel is great for this, the process of exporting CSVs from multiple platforms and wrangling them in spreadsheets can become a weekly chore. At Graphed, we've designed a way to automatically connect your data sources like Google Analytics, Shopify, and your ad platforms in one place. You can then ask questions in natural language, such as “build a dashboard showing sales stratified by state for the last quarter,” and get a live, automated report in seconds. Try playing around with your own data in Graphed for free.