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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
If you have a lot of text in your workbook, at some point you might want to split out sentences into individual cells. ...
Discover MoreGot some numbers and letters mixed up in the same cell? You may need to get rid of those letters so you are left with ...
Discover MoreSelect a large range of cells and you may later want to remove a few cells from that selection. This is not as easy as ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-09-25 15:33:03
JR
You are a superhero! Thank you so much!
2018-08-17 11:09:59
Greateful Person
God bless you!!
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 © 2018 Sharon Parq Associates, Inc.
Comments