Imagine you wanted to move the decimal point in your Excel financial model and reduce all the numbers by a factor of one thousand/ 000. There’s a quick trick in custom formats that will help you do that. Essentially all you need to do is add an extra comma (,) on the end. See the screen shot below. If you click on the image you can download the Excel template:
If you’re not sure how to access custom formatting there’s a handy shortcut for that. Just press “Ctrl 1” on your keyboard. Then type in the code as per the screenshot. It’s just an extra comma you need to add on the end.
Remember it’s just the way the numbers are displaying that you will have changed if you format your spreadsheet like this. The numbers themselves will not have changed. So you’d be right to question whether applying a format like this is an example of good modelling practice. At best it’s probably a ‘patch’ that enables you to change the appearance of all your numbers really quickly.
An alternative: paste special divide
There’s another way to scale all your input numbers by 1,000 permanently. Type 1,000 into an empty cell. Then “Ctrl C” for copy”. Then select the numbers you want to scale. Press “Ctrl Alt V” for paste special. Select “Values” and “Divide” and then press enter. You will have divided all your input numbers by 1,000. Now select any other input numbers and press “Ctrl Y” for redo. You can go through your spreadsheet selecting all further numbers that need changing and pressing “Ctrl Y”.
Formatting vs. a permanent change
It’s up to you (of course) to make the call on your financial model: do I change those numbers permanently or do I employ a custom format that just changes their appearance? You might find you have an issue using the custom format if someone later adds or links to other numbers that are scaled differently. There’s a risk that the custom format ends up misleading a model user. They think they’re working with the number 1,000 but they’re actually working with the number 1,000,000. We’ll leave it to you but changing the numbers permanently using paste special > divide might be a slightly safer option.