How to Expand Data in Excel

Your Excel spreadsheet is often just the tip of the iceberg, showing summarized totals while hiding the specific details that tell the full story. To find real insights, you need to know how to expand that data and see the records behind the numbers. This article will show you several powerful and practical ways to expand and drill down into your data in Excel, from simple grouping to advanced data transformation.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

The Basics: Using Excel's Grouping Feature to Expand and Collapse Rows

One of the most straightforward ways to organize and expand data is by using Excel’s built-in grouping feature. This allows you to create collapsible sections in your spreadsheet, perfect for tidying up long reports or summarizing categories without losing access to the underlying detail.

Imagine you have a monthly sales report with data for every single day. Looking at 365 rows of daily sales can be overwhelming. By grouping the rows by month or quarter, you can create a clean, high-level summary that can be expanded with a single click.

How to Group and Expand Rows

Let's walk through an example. Say you have sales data organized with columns for Date, Product, and Sales Amount, and you want to group it by quarter.

• Step 1: Organize Your Data. First, make sure your data is sorted in a way that makes sense for grouping. For a quarterly report, you’d sort by date. You may even want to add a helper column that identifies the quarter for each date.
• Step 2: Select the Rows. Select all the rows you want to include in your first group. For example, highlight all the rows corresponding to Q1 (January, February, and March).
• Step 3: Create the Group. In the Excel ribbon, go to the Data tab and click the Group button. You'll see a small bracket appear along the left side of your row numbers with a minus (-) symbol.
• Step 4: Repeat for Other Groups. Repeat this process for Q2, Q3, and Q4.
• Step 5: Expand and Collapse. Now, you can click the minus (-) sign to collapse a group, hiding the individual rows and showing only your subtotals (if you have them). Click the plus (+) sign that appears to expand the group and see the detail again.

You can also use the small numbers (1, 2, 3...) at the top left of your worksheet to quickly collapse or expand all groups to a specific level. This method is fantastic for creating clean, navigable reports directly within your sheet.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

The Easiest Drill-Down: Expanding Data from a PivotTable

PivotTables are the undisputed champions of data summarization in Excel. They let you slice and dice massive datasets into meaningful reports in seconds. But their power isn't just in summarizing - they also offer the easiest way to "expand" data and see the exact records that make up any summarized value.

This is called a "drill-down," and it feels like a magic trick the first time you use it.

How to Use the PivotTable Drill-Down Feature

Let's say you've created a PivotTable to show Total Revenue by Sales Region. You see that the "North" region generated $150,000 in revenue. Naturally, you wonder which specific sales make up that total.

• Step 1: Choose Your Cell. Find the cell in the PivotTable containing the value you want to investigate. In our example, it's the $150,000 figure for the "North" region.
• Step 2: Double-Click the Cell. This is the crucial step. Simply double-click on that cell.
• Step 3: See the Expanded Data. Poof! Excel instantly creates a brand new worksheet. This new sheet contains a neat table showing every single raw data row that contributed to that $150,000 total.

That's it. There is no step four. This unbelievably simple action saves you from hours of filtering and searching through your raw data. Whenever you look at a PivotTable and ask yourself, "What makes up this number?", just double-click to get the answer. Just be sure to name the new sheets Excel creates so you don't get lost in "Sheet1," "Sheet2," etc.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

The Power User's Move: Expanding Data by "Unpivoting" in Power Query

Sometimes, "expanding" data isn't about revealing hidden rows, it's about fundamentally reshaping your data from a "wide" format to a "long" format. This is a common challenge for marketers and analysts who often get data exported with months, categories, or products listed as column headers.

Wide data is easy for humans to read but terrible for analysis in Excel. For example:

Making a chart or a PivotTable from this structure is a headache. What we need is to "expand" or "unpivot" this, so it looks like this:

This "long" format is perfect for analysis. You can easily create a PivotTable to filter by month, sum spending by campaign, or chart performance over time. The best tool for this job is Power Query, a data transformation engine built into modern versions of Excel.

How to Unpivot Data with Power Query

1. Load Your Data. Select your data table and go to the Data tab. Click on From Table/Range. This will open the Power Query Editor.
2. Select Static Columns. In the Power Query editor, select the column(s) that should not be unpivoted. In our example, this is the "Campaign Name" column.
3. Unpivot Other Columns. Right-click on the header of the selected column ("Campaign Name"). In the context menu, choose Unpivot Other Columns.
4. Witness the Transformation. Power Query will instantly transform your data, collapsing the month columns into two new columns: "Attribute" (which contains the former column headers, like "Jan", "Feb") and "Value" (which contains the cell values, like the dollar amounts).
5. Rename and Load. Rename the "Attribute" and "Value" columns to something more useful, like "Month" and "Spend". Once you're happy, click Close & Load in the top-left corner. Your transformed, analysis-ready data will be loaded into a new worksheet.

Power Query remembers these steps, so next month when you add new data, you can simply refresh the query, and it will be updated automatically. It’s a powerful way to expand your data into a structure that unlocks deeper analysis.

Quick Wins: Expanding Data from a Single Cell

Finally, sometimes expanding data is as simple as breaking apart a single column into multiple columns. This is common when you have data like full names, addresses, or campaign UTM parameters crammed into one cell.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Using Text to Columns

This is the classic tool for splitting one column into many. Let’s say you have a column with a contact's full name, and you want to expand it into separate "First Name" and "Last Name" columns.

• Select the column you want to split.
• Go to the Data tab and click Text to Columns.
• Choose Delimited if your data is separated by a character (like a space, comma, or hyphen). Choose Fixed width if the data is aligned in columns with spaces between each field.
• Follow the wizard to select your delimiter (like a space for names) and confirm the split. Your data will now be expanded across multiple columns.

Using Flash Fill

Flash Fill is a more recent and intelligent feature. It detects patterns in your data and automates the rest of the work. It feels truly magical.

• In the column next to your source data (e.g., "Full Name"), start typing what you want in the first row. For "John Smith", you would type "John" in the adjacent "First Name" column.
• In the second row, start typing the next first name. Excel will recognize the pattern and show a grayed-out preview of all the other first names it has extracted.
• If the preview looks correct, just hit Enter, and Excel will instantly fill the entire column for you.

Both methods are excellent for breaking down crammed data, giving you more granular fields for better sorting, filtering, and analysis.

Final Thoughts

Mastering how to expand data in Excel transforms you from a passive data viewer into an active analyst. Whether you're using simple groups to organize a report, a PivotTable drill-down to investigate a key metric, or Power Query to reshape data for analysis, these skills help you move beyond summary numbers and uncover the details that drive better decisions.

Of course, manually expanding and reorganizing data in spreadsheets is often just the first step in a long reporting process. To get answers, you still need to build the right charts, tables, and dashboards. We built Graphed to eliminate this manual work entirely. Instead of spending hours in Excel, you connect your data sources once and use plain English to create live dashboards in seconds. This automates the entire reporting workflow, from data connection to visualization, letting you focus on insights, not on spreadsheet wrangling.