How to Scale Data in Excel

Hitting a wall in Excel when your data gets too big can be incredibly frustrating. One million rows of sales data, a year's worth of web traffic, or a massive customer export can bring even the most powerful computer to a standstill, leaving you with a "(Not Responding)" message of doom. The good news is that Excel already has powerful, built-in tools designed to crunch millions of rows of data with ease. This article will show you how to use modern Excel features to analyze large datasets without the crashes, freezes, and frustration.

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

Why Excel Gets Slow with Large Datasets

Before jumping into the solution, it’s helpful to understand what’s causing the problem. Excel wasn't originally designed to be a massive database. When you open a typical workbook, it tries to load absolutely everything into your computer's RAM (short-term memory). The more data you have, the more memory it eats up.

Several factors make this problem worse:

• Calculation Overload: Every formula in your workbook needs to be calculated. If you have a hundred thousand rows with five columns of complex formulas, that's half a million calculations Excel has to process. Any small change could trigger a storm of recalculations.
• Volatile Functions: Certain "volatile" functions are performance killers. Functions like NOW(), RAND(), OFFSET(), and INDIRECT() recalculate every single time you make any change to the workbook, significantly bogging down performance.
• Excessive Formatting: Applying formatting like colors, borders, and conditional formatting across thousands or millions of rows adds a surprising amount of bloat to your file. Each formatted cell stores extra information, increasing file size and slowing rendering.
• Large File Connections: Linking to other massive workbooks can create a fragile and slow reporting chain. Your computer has to load data from multiple sources just to update a single chart.

Trying to solve these issues by buying a faster computer is only a temporary fix. The real solution is to start using Excel differently - the way it was designed to handle big data.

Meet Your Modern Toolkit: Power Query and the Data Model

Buried within the "Data" tab of modern Excel (Excel 2016 and later) are two awesome tools that completely change the game for data analysis: Power Query and the Data Model. Instead of dumping raw data into a worksheet, you'll use these features to create an efficient, powerful, and automated data pipeline.

Power Query (Get & Transform Data)

Think of Power Query as a pre-processor for your data. It lets you connect to hundreds of different data sources (CSV files, databases, web pages, cloud services, etc.), clean and shape that data, and then decide how to load it. The best part? It records all your cleaning steps, so you can refresh your entire process with a single click. This means no more mind-numbing copy-pasting or manual data cleaning every month.

The Data Model (Powered by Power Pivot)

The Data Model is essentially a lightweight database built directly into Excel. It uses a high-compression engine to store massive amounts of information far more efficiently than standard worksheets can. While a worksheet might choke on a million rows, the Data Model can handle tens of millions of rows with ease. Data stored here doesn’t bloat your file and can be analyzed almost instantly using PivotTables without ever touching the spreadsheet grid.

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

Step 1: Get Your Data with Power Query (Without Crashing Your Sheet)

Let's walk through the process of importing a large CSV file without loading it directly into a worksheet. This single change in your workflow is the key to scaling your Excel file.

Connecting to Your Data Source

Imagine you have a 500 MB CSV file containing two million rows of sales transactions. Simply double-clicking it would probably crash Excel. Instead, let's pull it in with Power Query.

1. Open a new, blank Excel workbook.
2. Go to the Data tab in the ribbon.
3. In the "Get & Transform Data" group, click From Text/CSV.
4. Navigate to your large CSV file and click Import.
5. A preview window will pop up. Instead of clicking "Load," click the Transform Data button. This opens the Power Query Editor.

Cleaning Up in the Power Query Editor

You are now in the Power Query Editor. This is your data workshop. On the right, you'll see a panel called "Applied Steps." Every change you make here is recorded, creating a repeatable recipe for your data cleanup.

Here are a few common transformations you can apply without any code:

• Remove Unnecessary Columns: Right-click the header of a column you don't need and select "Remove." This reduces the amount of data being processed.
• Filter Rows: Click the filter arrow on a column header to remove categories you don't need. For example, filtering out "Test" or "Cancelled" orders before the data loads.
• Change Data Types: Make sure columns are set to the correct type (e.g., Whole Number, Decimal Number, Date). This is crucial for accurate calculations downstream.

Loading Directly to the Data Model

Once your data is cleaned, it's time for the most important part. We will load the data, but not to your worksheet.

1. In the top-left of the Power Query Editor, click the "Close & Load" dropdown arrow.
2. Select Close & Load To...
3. In the "Import Data" dialogue box, select Only Create Connection.
4. Crucially, check the box that says Add this data to the Data Model.
5. Click OK.

You'll be taken back to your blank Excel sheet. It might feel like nothing happened, but hidden in the background, Excel's Data Model just loaded your two million rows of data. Your file remains tiny and fast because the data isn't cluttering up the worksheet grid.

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

Step 2: Analyzing Millions of Rows with PivotTables

Now that your data is securely stored in the Data Model, you can analyze it with the familiar interface of a PivotTable. This PivotTable will be incredibly fast because it's querying the efficient Data Model, not slogging through cells on a sheet.

1. Go to the Insert tab.
2. Click PivotTable.
3. In the dialog, select From Data Model Detections. It may default to this if a Data Model exists in the file.
4. Click OK.

A new PivotTable will be created. The field list on the right now pulls directly from your Data Model. You can drag and drop fields to build your report just like a normal PivotTable, but with one major difference: it will summarize your multi-million row dataset in seconds.

Level Up with DAX Measures

While PivotTables are great for summarizing, the true power of the Data Model comes from a formula language called DAX (Data Analysis Expressions). You create calculations, called "measures," that exist within the Data Model.

For example, instead of summarizing the "Revenue" column, you can create an explicit measure. To do so, in the "PivotTable Fields" list, you can either select to add through the "Power Pivot > Measures > New Measure" section, or by right-clicking the table name in the field list and selecting "Add a Measure..." A dialogue box will appear where you define your calculation.

A simple DAX measure for total revenue would look like this:

Total Revenue := SUM(Sales[Revenue])

This may look like a normal formula, but it's far more powerful because it exists purely within the performant data model, instead of taking up valuable workbook recalculation power as a calculation would in a typical spreadsheet cell with a similar formula, e.g. SUM(F2:F2000001) in a traditional workbook. Once created, "Total Revenue" appears in your field list as a custom calculation you can add to any PivotTable. You can create much more advanced measures for things like year-over-year growth, customer lifetime value, and more.

More Pro Tips for Scaling Excel

Apart from the Power Query and Data Model workflow, here are a few other habits that help keep your workbooks lightning-fast.

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

Save as an Excel Binary Workbook (.xlsb)

This file format can often be up to 50% smaller and is significantly faster to open and save than a standard .xlsx file. Go to File > Save As and choose Excel Binary Workbook (*.xlsb) from the format dropdown.

Keep Data and Analysis Separate

It's always a good idea to keep your raw data connections in one workbook and build your reports and dashboards in a separate file. This keeps your analysis file clean and focused.

Switch to Manual Calculation

If you have a workbook with many formulas, you can prevent Excel from freezing by switching calculation from automatic to manual. Go to Formulas > Calculation Options and select Manual. Now, Excel will only recalculate everything when you press the F9 key.

Use Official Tables

Whenever you do have data in a worksheet, format it as an official Excel Table by selecting a cell and pressing Ctrl+T. Tables allow you to use structured references (e.g., [Revenue]) instead of cell references (D2:D1000), which is more efficient and easier to read.

Final Thoughts

To truly scale Excel, the key is shifting your mindset away from using the worksheet grid as a database. By adopting the built-in powerhouse tools like Power Query for data cleaning and the Data Model for efficient storage and analysis, what once felt like massive and impossible-to-calculate datasets can become easy-to-manage and ready for insight-producing analytics.

Setting up even these efficient data pipelines in Excel still requires some manual work - downloading CSVs, setting up queries, and building your reports click-by-click. At Graphed , we’ve taken this a step further by automating the entire process. We connect directly to your various data sources like Shopify, Google Analytics, or Salesforce, and allow you to build real-time dashboards and reports simply by asking for what you want in plain English. This approach eliminates the data wrangling so you can get immediate answers and focus entirely on making better decisions for your business, not on the next steps to getting an accurate report on the books.