In some worksheets Mustafa has many tables that contain values in the millions (six digits) and he wants to reduce them to, for example, thousands. He wonders if there is a simple shortcut to make that reduction without going into each cell and dividing by 1,000.
Assuming you don't want to take the route of creating a formula that actually does the division, there are a couple of ways to go about this. First, if you really want to divide the values by 1,000, you could use the Paste Special feature of Excel:
Figure 1. The Paste Special dialog box.
That's it; all the cells you selected in step 3 are divided by 1000. You can also delete the value you placed in the cell in step 1.You should be aware that the Paste Special will work on all cells you selected, even if those cells are in hidden rows or columns.
If you don't want to permanently modify your data, you could create a custom format that will display your data as if it were divided by 1,000. To do this, follow these steps:
Figure 2. The Number tab of the Format Cells dialog box.
The cells now appear as if they've been divided by 1,000, even though the original values remain in the cells. If you prefer to "divide" by 1,000,000, then you can place two trailing commas in the custom format in step 5.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11229) applies to Microsoft Excel 2007, 2010, and 2013.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Excel allows you to edit the contents of a cell in two places—the cell itself or in the Formula bar. If you want to ...
Discover MoreAs you enter data in a worksheet, you may want to have Excel automatically move from cell to cell based on the length of what ...
Discover MoreIf you copy a cell that contains a reference to external data, do you get an error? It could be due to the complexity of the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2015-10-06 01:31:23
VP
SK is referring to divide by "Lakh" = 100,000. The Indian counting system is slightly different from metric.
100,000 - 1,00,000 => 1 Lakh
1,000,000 - 10,00,000 => 10 Lakhs
10,000,000 - 1,00,00,000 => 1 Crore
etc..
2015-10-05 09:51:31
David Zwaaf
You can avoid having all hidden cells being affected by first using the Go To (F5) menu, press Special and selecting visible cells only, then proceeding with the Paste Special Divide
2015-10-05 08:32:35
Fernando Cava
You can also reemplace the =(1000) for a specific cell (i.e. =A1)... the problem then is substitute the A1 for A1,B1,C1,etc
Anyway the tip is excellent, thanks again!!!
Best regards from Madrid (Spain),
Fernando
2015-10-05 06:36:51
Thank you very much to all, I have many gains,
Regards,
2015-10-05 03:57:10
Andrew Evans
Richard - yes they can be.
For Millions use - #,##0,,"m"
For Thousands use - #,##,k
2015-10-05 03:43:49
Richard
If I was doing this, I would prefer to append a "k" when dividing by 1,000 (to indicate kilo-) or "M" when dividing by 1,000,000 (to indicate mega-). Could the solutions accommodate this?
2015-10-05 03:05:34
Bunio
Brian: the trick you proposed (formula =1000 instead of a plain number 1000) very neat, indeed. Useful e.g. when converting currency - you can follow what rate of exchange has been applied.
The trick can be extended by applying fixed address/name instead of number. E.g obtaining =21414523/($G$2) or =21414523/roe.
2015-10-04 15:09:16
Dennis Taylor
Each trailing comma in a format hides the display of three characters to the LEFT of the decimal point.
For the value 39,678,783:
the format ##,##0.0, displays 39,678.8
the format ##,##0.0,, displays 39.7
the format ##,##0, displays 39,679
the format ##,##0,, displays 40
For all of these formats, the actual value remains unchanged, but the displayed value appears to be rounded.
2015-10-04 11:13:57
Kara Olson
Be very careful about data and model integrity when using the formatting method using "##,##0.00,"
It's easy to forget that the VALUES in the cells are actually DIFFERENT from what is visually seen.
When I use this formatting feature I include a COMMENT in the first cell of the range, stating this warning.
2015-10-04 00:12:20
Brian L.
Allen -- That trailing-comma format is something I'd never heard about -- seems really useful for financial reports ("000s") & the like!
Brian -- Ditto for the use of a formula in the Paste Special / Divide function. Neat trick!
SK -- Huh?!?
2015-10-03 11:04:03
Brian
Another way is to use the PasteSpecialDivide but instead of the cell being 1000, use =1000. This way it looks like a formula and the divided cells containing say numbers like 11576486 become formulas that look like =11576486/(1000)
This also gives you the opportunity at any point in the future to undo this using Replace.
Regards
Brian
2015-10-03 10:20:12
Very Good format, If possible please share how can dived in lack
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
Comments