Round to Thousands or Millions in Excel

To round to thousands in Excel, use ROUND with -3 as the number of digits: =ROUND(A2,-3) turns 1,234,567 into 1,235,000. Use -6 for millions. If you only need a report to show thousands or millions, a custom number format is often better, because it changes the display and keeps the real value. This guide covers both, for anyone preparing finance reports, dashboards or test answers.

The sample data#

The examples use this revenue table in A1:B5:

The total in B6 is =SUM(B2:B5).

How to round to thousands with ROUND#

ROUND takes two arguments: the number, and how many digits to keep. A positive number of digits rounds to decimal places. A negative number rounds to the left of the decimal point: -1 to tens, -2 to hundreds, -3 to thousands. In C2, type:

=ROUND(B2,-3)

Fill it down (Windows: Ctrl+D; Mac: ⌘D) to get:

ROUND looks at the hundreds digit. 567 is 500 or more, so North rounds up to 1,235,000. 210 is less than 500, so South rounds down. East is exactly 500, and ROUND rounds halves up (away from zero), so it becomes 2,500,000. The ROUND function guide covers the arguments, and the Excel rounding functions guide compares the whole family.

How to round to millions#

Use -6 to round to the nearest million:

=ROUND(B2,-6)

North becomes 1,000,000 and East becomes 2,000,000: 2,499,500 is just under 2.5 million, so it rounds down. South (845,210) rounds up to 1,000,000, and West (62,840) rounds to 0.

Whole millions are often too rough. To show one decimal place, divide by a million first:

=ROUND(B2/1000000,1)

This returns 1.2 for North, 0.8 for South, 2.5 for East and 0.1 for West. Label the column "Revenue ($m)" so readers know 1.2 means 1.2 million.

Round up or down to thousands#

ROUNDUP and ROUNDDOWN take the same arguments, but they always go one way:

=ROUNDUP(B3,-3)
=ROUNDDOWN(B2,-3)

The first returns 846,000 for South, even though 845,210 is closer to 845,000. The second returns 1,234,000 for North. Use ROUNDUP for budgets and limits that must not be too low, and ROUNDDOWN when you must not overstate. The comparison of ROUND vs ROUNDUP vs ROUNDDOWN explains when each one fits. For other steps, like the nearest 500 or 5,000, see rounding to the nearest 5, 10 or 0.5.

Show numbers in thousands without changing them#

A custom number format changes how a number looks, not what it is. Each comma at the end of the format divides the display by 1,000:

1. Select B2:B6.
2. Press Ctrl+1 (Mac: ⌘1) to open Format Cells.
3. Choose Number > Custom.
4. Type a format from the table below and click OK.

Click a formatted cell and the formula bar still shows 1234567. That is the big advantage: SUM, charts and other formulas use the full values, so the total of 4,642,117 is exact. Use a format when the rounding is only for reading.

Get the number of thousands as a value#

Sometimes you need the rounded figure itself, for example to type into a summary or to compare with a budget kept in thousands. Divide by 1,000 and round to zero decimals:

=ROUND(B2/1000,0)

North returns 1235, meaning 1,235 thousand. To build a text label, join a K with TEXT for the thousands separator: =TEXT(B2/1000,"#,##0")&"K" returns 1,235K. Text labels look right, but you can't add them up, so keep them for titles and chart labels, not for calculations.

Why rounded numbers don't add up#

Rounding each part and adding the rounded parts can give a different answer from rounding the total. In thousands:

The parts add up to 4,643, but the real total rounds to 4,642. Neither is wrong; each small rounding difference adds up. Most reports show the rounded true total and add a note such as "figures may not add due to rounding". Whatever you do, round the total from the original values, =ROUND(B6/1000,0), not from the rounded column. The same issue appears with money, for example when you calculate a discount on each line and on the total.

A last detail on negative numbers: ROUND treats them like positive ones, so =ROUND(-1500,-3) returns -2,000 (away from zero). ROUNDDOWN moves toward zero, so =ROUNDDOWN(-1500,-3) returns -1,000.

Practise it#

Try the exercise on this page: it rounds North's revenue to the nearest thousand with a negative number of digits. Then practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet, where you type each formula and it checks it instantly, including the difference between rounding a value and only formatting it.