Spreadsheet Practice

How to scale values to 0-100% in Excel

Formula (Excel and Google Sheets)

=(B2-MIN($B$2:$B$9))/(MAX($B$2:$B$9)-MIN($B$2:$B$9))

Use min-max scaling: =(B2-MIN($B$2:$B$9))/(MAX($B$2:$B$9)-MIN($B$2:$B$9)). The lowest value becomes 0% and the highest 100%.

Result on the example
one result per row in C2, filled down to C9
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in C2, filled down to C9. Change any input and the result updates, or edit the formula to experiment.

How it works

Subtracting the minimum moves the lowest score to 0, and dividing by the range (maximum minus minimum, 37 here) makes the highest 1. Everything else lands in between in proportion: store C's 88 is 81.1% of the way from the lowest to the highest.

=(B2-MIN($B$2:$B$9))/(MAX($B$2:$B$9)-MIN($B$2:$B$9))

Result0.1081081081

  1. B262

    The value in B2.

  2. $B$2:$B$98 rows × 1 col: 62, 75, 88, 70, …

    A range: 8 cells in one column.

  3. MIN($B$2:$B$9)58

    MIN: Smallest number.

    • number1$B$2:$B$98 rows × 1 col: 62, 75, 88, 70, …
  4. B2-MIN($B$2:$B$9)4

    The - operator subtracts.

  5. MAX($B$2:$B$9)95

    MAX: Largest number.

    • number1$B$2:$B$98 rows × 1 col: 62, 75, 88, 70, …
  6. MIN($B$2:$B$9)58

    MIN: Smallest number.

    • number1$B$2:$B$98 rows × 1 col: 62, 75, 88, 70, …
  7. MAX($B$2:$B$9)-MIN($B$2:$B$9)37

    The - operator subtracts.

  8. B2-MIN($B$2:$B$9))/(MAX($B$2:$B$9)-MIN($B$2:$B$9)0.1081081081

    The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =(B2-MIN($B$2:$B$9))/(MAX($B$2:$B$9)-MIN($B$2:$B$9)). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=LET(lo,MIN($B$2:$B$9),hi,MAX($B$2:$B$9),(B2-lo)/(hi-lo))10.8%, 45.9%, 81.1%…Excel 2021, Microsoft 365 and Google Sheets: LET names the minimum and maximum so the formula reads clearly.

Common mistakes

  • If all values are equal, the range is 0 and the formula returns #DIV/0!. Guard it with IFERROR.
  • Adding a new extreme value rescales every other row. For scores that must stay comparable over time, scale against fixed limits instead.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Scale values to 0-100%

In C2, rescale the score so the lowest store is 0% and the highest 100%, then fill down to C9.

Questions

How do I scale to 0-10 or 1-5 instead?

Multiply and shift: for 1 to 5, use =1+4*(B2-MIN($B$2:$B$9))/(MAX($B$2:$B$9)-MIN($B$2:$B$9)).

How is this different from percent of maximum?

Percent of maximum divides by the top value only, so the lowest store isn't 0%: =B2/MAX($B$2:$B$9).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.