This is something that will come handy for a lot of Finance professionals.
You end up working with thousands of cells with numbers, and each cell has a terrifying 10/12/14 digit number which makes it very difficult to read and comprehend the numbers, leave alone making an analysis.
To overcome this issue, people generally resort to one of these methods:
a) create another sheet where you enter a formula to link up to the original sheet, where they divide the cells by 1000 or 1,000,000 etc and view it in the condensed form.
b) if another backup of the file is available, they use the paste special function (which has got a divide option embedded) to divide the numbers directly by 1000 or 1,000,000 and get the numbers truncated. But the original values would be lost. [Feel free to ask if you need more details on this]
We see that both the methods above can give you the result but are not really a smart way to work. One of the simplest ways to achieve this is to deploy the following trick:
1) Select all your numeric cells.
2) Press Ctrl+1 to bring up the Format Cells dialog box.
3) Now go to the last item in the list (Custom) – and type a zero followed by a comma (0,) under the Type on the right side.
4) Press OK, and you will see that all figures now appear at thousands level. The change has happened only in the display and the cells still retain their original values, as you can witness in the formula bar or by going to the cell and pressing F2 key.
5) If you want it to show only millions, type “0,,” in the custom type box.
Now, does that qualify as a nice tip for the day?
Note: This tip is an extract from my book on "Excel for the CEO" - details available at www.mrexcel.com/ceo.shtml. You can also find this in the ebook edition "Excel for the Small Business Owner" available for online ordering at www.mrexcel.com/sbo.shtml
You end up working with thousands of cells with numbers, and each cell has a terrifying 10/12/14 digit number which makes it very difficult to read and comprehend the numbers, leave alone making an analysis.
To overcome this issue, people generally resort to one of these methods:
a) create another sheet where you enter a formula to link up to the original sheet, where they divide the cells by 1000 or 1,000,000 etc and view it in the condensed form.
b) if another backup of the file is available, they use the paste special function (which has got a divide option embedded) to divide the numbers directly by 1000 or 1,000,000 and get the numbers truncated. But the original values would be lost. [Feel free to ask if you need more details on this]
We see that both the methods above can give you the result but are not really a smart way to work. One of the simplest ways to achieve this is to deploy the following trick:
1) Select all your numeric cells.
2) Press Ctrl+1 to bring up the Format Cells dialog box.
3) Now go to the last item in the list (Custom) – and type a zero followed by a comma (0,) under the Type on the right side.
4) Press OK, and you will see that all figures now appear at thousands level. The change has happened only in the display and the cells still retain their original values, as you can witness in the formula bar or by going to the cell and pressing F2 key.
5) If you want it to show only millions, type “0,,” in the custom type box.
Now, does that qualify as a nice tip for the day?
Note: This tip is an extract from my book on "Excel for the CEO" - details available at www.mrexcel.com/ceo.shtml. You can also find this in the ebook edition "Excel for the Small Business Owner" available for online ordering at www.mrexcel.com/sbo.shtml
No comments:
Post a Comment