How to Find the Highest and Lowest Numbers in an Excel Column

The simplest way to find a range in Excel

Range means the difference between the highest number and the lowest number in a set of data. In Excel, you calculate it by subtracting the minimum value from the maximum value. The formula looks like this: =MAX(A1:A10)-MIN(A1:A10)

This formula tells Excel to find the largest number in cells A1 through A10, find the smallest number in that same group, and subtract the small one from the large one. The result is your range. You can use this same pattern with any column or row of numbers — just change the cell references to match where your data actually sits.

You do not need to calculate the maximum and minimum separately unless you want to see those numbers on your spreadsheet for another reason. If you only care about the range itself, one formula does the whole job.

Key Takeaways

Range is calculated by subtracting the minimum value from the maximum value using the formula =MAX(range)-MIN(range).
You can explore this formula to any column, row, or selection of cells by changing the cell references to match your data location.
Excel will automatically ignore empty cells and text when you use MAX and MIN functions, so you do not need to clean your data first.
If your data spans multiple columns or is scattered across your sheet, you can select non-adjacent cells by holding Ctrl and clicking each one before entering the formula.

Setting up your data and choosing where to put the formula

Before you calculate range, decide where you want the result to appear. Most people put it in an empty cell below or to the right of their data, so it is straightforward to find and does not get mixed up with the numbers being measured.

Your data can be in a single column (like A1:A50), a single row (like B5:K5), or even a rectangular block (like C2:E10). Excel handles all of these the same way. The MAX and MIN functions will look at every cell in the range you give them and find the highest and lowest values, no matter how the data is arranged.

If your numbers are spread across multiple separate areas — for example, some in column A and some in column C — you can still calculate range. Hold down the Ctrl key (or Cmd on a Mac) and click each area you want to include. Then type your formula, and Excel will treat all those separate selections as one group.

Writing and entering the formula step by step

Click on the empty cell where you want your range result to appear. Type an equals sign to start the formula: =

Then type MAX( followed by the cell range that contains your data. If your numbers are in cells A1 through A20, type MAX(A1:A20). Close that with a closing parenthesis: ) Now type a minus sign: -

Type MIN( followed by the exact same cell range: MIN(A1:A20). Close it with another parenthesis. Your complete formula should look like =MAX(A1:A20)-MIN(A1:A20)

Press Enter. Excel calculates the result and displays it in that cell. If you see a number, the formula worked. If you see an error message like #VALUE! or #NAME?, check that you typed the cell references correctly and that your data actually contains numbers, not text that looks like numbers.

Adjusting the formula for different data layouts

If your data runs down a column instead of across a row, the formula stays exactly the same — just use the column reference. Data in column D from row 3 to row 50 becomes =MAX(D3:D50)-MIN(D3:D50)

If your data is arranged in a rectangle — say, columns B through E and rows 2 through 15 — you can include the whole block: =MAX(B2:E15)-MIN(B2:E15) Excel will search every cell in that rectangle and find the single highest and single lowest value across all of it.

If you have data in multiple separate locations and want to include all of them in one range calculation, use commas to separate the areas. For example: =MAX(A1:A10,C1:C10)-MIN(A1:A10,C1:C10) This tells Excel to look at both column A and column C when finding the maximum and minimum.

What happens when your data changes

Once you enter the formula, it stays connected to those cells. If you change a number in your data, the range result updates automatically. If the highest value gets larger, the range gets larger. If the lowest value moves closer to the highest value, the range shrinks. You do not have to re-enter the formula.

This automatic updating is one reason to use a formula instead of calculating by hand. You can build a spreadsheet once, and it stays correct even as the numbers change. If you add new data below your original range, you will need to edit the formula to include the new cells — Excel does not automatically expand the range for you.

Common mistakes and how to fix them

The most frequent error is typing the cell references wrong. If you write =MAX(A1:A10)-MIN(A1:B10) — notice the B in the second part — Excel will look at different cells for the minimum than for the maximum, and your answer will be wrong. Always use the exact same range in both MAX and MIN.

Another common problem is including a cell that contains text or a label. If row 1 has a header like "Sales" and you use =MAX(A1:A20)-MIN(A1:A20), Excel ignores the text and only looks at the numbers. This usually works fine, but if every cell in your range contains text, you will get an error. Make sure at least some of your cells hold actual numbers.

If you see a very large or very small range that does not make sense, check whether your data contains any extreme outliers — one unusually high or low number that skews the result. The range formula is working correctly; it is just showing you that your data has a wide spread.

Using range to understand your data

Range tells you how spread out your numbers are. A small range means your data points are close together. A large range means they are far apart. This is useful information for spotting problems or understanding patterns.

For example, if you are tracking daily temperatures and the range is 2 degrees, the weather was stable. If the range is 40 degrees, there was a big swing from coldest to warmest. If you are looking at employee salaries and the range is very large, it means some people earn much more than others. Range does not tell you the whole story about your data, but it is a quick way to see whether the numbers cluster together or spread out.

Frequently Asked Questions

Can I calculate range for data that includes negative numbers?

Yes. The formula works exactly the same way. If your data includes -10, 5, and 20, the maximum is 20, the minimum is -10, and the range is 30. Excel handles negative numbers automatically.

What if I want to see the maximum and minimum values separately, not just the range?

Put the MAX formula in one cell and the MIN formula in another. Type =MAX(A1:A20) in one cell to see the highest value, and =MIN(A1:A20) in a different cell to see the lowest value. Then you can calculate range from those two results if you want, or just look at them separately.

Does the formula work if my data has empty cells mixed in?

Yes. MAX and MIN skip over empty cells and only look at cells that contain numbers. If you have data in A1, A3, A5, and A7 with empty cells in between, the formula =MAX(A1:A7)-MIN(A1:A7) will still find the correct range.

Can I copy this formula to other cells?

Yes, but be careful. If you copy the formula down to another row, Excel will automatically adjust the cell references. If your original formula was =MAX(A1:A10)-MIN(A1:A10) and you copy it to the next row, it becomes =MAX(A2:A11)-MIN(A2:A11). This is usually not what you want. To copy the formula without changing the references, add dollar signs: =MAX($A$1:$A$10)-MIN($A$1:$A$10)

What if my range is zero?

A range of zero means every number in your data is identical. The maximum and minimum are the same value, so subtracting them gives zero. This is mathematically correct and tells you your data has no variation.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.