How to Automatically Round Large Numbers

Large numbers, such as thousands and millions, are often easier to work with as a rounded number than the exact value. You can set the cells containing large values to the nearest number of thousands using a custom Excel format.

First select the cell or range to round off. Next, right-click the selection and choose Format Cells, Number. From the Category list in the Format Cells dialog box, select Custom. In the Type box, type ###, "k" and click OK. This format will round up the number to a whole number of thousands and display it with the letter k after it. For example, the number 21,678.22 will display as '22k'. Likewise, to format a number in the millions, create a custom formula that reads ###,,"m".

In addition to using this number format in your worksheets, you can apply it when formatting the axes of your charts. To format a chart axis this way, right-click the axis and choose Format Axis. Then choose the Number group and set up your custom format by typing it in the Format Code box.

Note: This format controls only how the numbers appear on your worksheet or chart axis, and does not affect the cell's value. Therefore, Excel will use the actual value in a cell when the cell is referred to in a calculation, while keeping the data reasonable to look at.