How to Normalize Data in Excel Between 1 and 10

Scaling data to a consistent range, like between 1 and 10, is a perfect way to compare values that are otherwise measured on wildly different scales. It makes it easy to see relative performance without getting hung up on the original units. This tutorial will walk you through a simple, step-by-step formula to normalize any dataset in Excel to a custom 1-to-10 scale.

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

What is Data Normalization and Why Bother?

In simple terms, data normalization is the process of taking a range of values and rescaling them to fit within a new, specific range, like 1 to 10 or 0 to 1. This isn't about changing the story your data tells, it's about rephrasing it so different stories can be compared more easily.

Imagine you're reviewing marketing campaign performance. You have "ad spend" in thousands of dollars, "website traffic" in tens of thousands of sessions, and "customer satisfaction" on a 1-5 survey scale. Trying to plot these on the same chart would be a mess, the massive traffic numbers would make everything else look flat. By normalizing each metric to a 1-10 scale, you can easily compare their performance relative to each other on a single, intuitive graph.

Common uses include:

• Performance Reviews: Converting various performance metrics (sales numbers, projects completed, attendance) to a single 1-10 scale for fair employee comparisons.
• Product Ratings: Combining different user ratings - like a 5-star rating, a 100-point critic score, and a 3-point thumbs-up system - into one standardized score.
• Financial Analysis: Comparing the growth of a small startup and a large corporation by normalizing their revenue figures to see relative changes.
• Machine Learning: Many AI models require input features to be on a similar scale to prevent certain features from unduly influencing the results.

Normalization vs. Standardization: What's the Difference?

You may also hear the term "standardization." While often used interchangeably, they're slightly different. Normalization scales your data to a fixed range (e.g., 1 to 10). Standardization, on the other hand, transforms your data to have a mean of 0 and a standard deviation of 1. For creating comparable scores or ratings, normalization is almost always the method you need.

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 Formula to Normalize Data Between 1 and 10

To rescale your data, we'll use a standard normalization formula. It looks a little complex at first, but each part plays a simple, logical role.

The general formula is:

New Value = (((Original Value - Min Value) / (Max Value - Min Value)) * (New Max - New Min)) + New Min

Let's break that down:

• (Original Value - Min Value) / (Max Value - Min Value): This core part of the formula calculates where your value falls within its original range, expressing it as a proportion between 0 and 1. For example, the smallest value becomes 0, the largest becomes 1, and a value exactly in the middle becomes 0.5.
• ***(New Max - New Min):** This multiplies our 0-1 proportion by the size of our new range. For a 1-10 scale, the range is 9 (because 10 - 1 = 9). This stretches our 0-1 scale to a 0-9 scale.
• + New Min: Finally, this adds the new minimum value to shift the range up. By adding 1, it moves our 0-9 scale to a 1-10 scale.

For our specific goal of scaling between 1 and 10, the formula simplifies to:

Normalized Value = (((Original Value - Min Value) / (Max Value - Min Value)) * 9) + 1

Now, let’s put this into practice in Excel.

A Step-by-Step Guide to Normalizing Data in Excel

Let's say we have monthly sales figures for several products and we want to create a performance score for each on a 1-10 scale. Our data is in column A, from cell A2 to A11.

Step 1: Find the Minimum and Maximum Values

First, you need to find the lowest and highest values in your dataset. Excel makes this easy with the MIN() and MAX() functions.

Find an empty spot in your worksheet (say, cells D1 and D2) to calculate these values. They will act as reference points for our formula.

• In cell D1, type: =MIN(A2:A11)
• In cell D2, type: =MAX(A2:A11)

Excel will find the smallest and largest numbers in your data range. Adjust A2:A11 to match your actual data range.

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: Enter the Normalization Formula

Now it's time to build the formula. Click on the first empty cell next to your data (in our example, a new column called "Normalized Score" in cell B2).

Using our simplified formula, we'll translate it into Excel syntax, referencing our cells. The key here is to use absolute references (the dollar signs) for our MIN and MAX values. This "locks" those cell references, so they don't change when we drag the formula down to other cells.

In cell B2, type the following formula and press Enter:

=((A2-$D$1)/($D$2-$D$1))*9+1

Let's match it to our formula breakdown:

• A2 is the Original Value we want to convert.
• $D$1 is the absolute reference to our Min Value.
• $D$2 is the absolute reference to our Max Value.

The first product, "Omega," generated $2,189 in sales, which was the minimum amount. Its normalized score is rightfully a 1.

Step 3: Drag the Formula Down

You don't need to re-type the formula for every row. Simply click on cell B2, then move your cursor to the small green square in the bottom-right corner of the cell's border - this is called the fill handle.

Click and drag the fill handle down to the last row of your data (B11 in our case). Excel will automatically apply the formula to the rest of your cells, adjusting the reference to the "Original Value" (from A3, A4, etc.) while keeping the MIN and MAX references locked.

You now have a new column with your sales data perfectly scaled to a 1-10 range! The product with the highest sales ("Alpha") gets a perfect 10, the product with the lowest sales ("Omega") gets a 1, and all other products fall somewhere in between.

Practical Tips and Common Mistakes

Mastering this formula is easy, but a few pointers can save you some headaches.

Why Absolute References ($) are Critical

If you forget the dollar signs and use relative references (e.g., D1 instead of $D$1), Excel will shift your MIN and MAX references as you drag the formula down. This will lead to #DIV/0! errors and completely incorrect results. Always lock your range-wide references!

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

Handling New Data

If you add new rows of data, remember that the original MIN and MAX values might change. You'll need to update the data range in your MIN/MAX formulas (e.g., changing A2:A11 to A2:A15) and then re-apply the normalization formula across your dataset.

For more dynamic sheets, consider converting your data into an Excel Table (select your data and press Ctrl+T). Tables automatically expand as you add data, and you can use structured references like MIN(TableName[ColumnName]) that update automatically.

Interpreting Normalized Scores

Keep in mind that the normalized score is relative, not absolute. A score of "10" just means "this was the highest value in this specific dataset." It doesn't mean it's the highest possible value that could ever exist. Context is everything.

Final Thoughts

Normalizing data in Excel to a 1-10 scale turns abstract numbers into clear, comparable scores. With a combination of the MIN and MAX functions and one powerful formula, you can standardize any dataset and create more insightful reports that are easier for anyone to understand.

Learning spreadsheet functions is incredibly useful, but it often becomes a bottleneck when you need to combine data from multiple platforms, like Google Analytics, Shopify, and your sales CRM. When reports require manually exporting and then normalizing data every week, the process becomes slow and tedious. At Graphed, we built a tool to solve this problem by connecting directly to your favorite apps. Instead of wrangling CSVs, you just ask questions in plain English, and we build live, interactive dashboards that update automatically - freeing you to focus on analyzing your business instead of just preparing the data for it.