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:

Excel custom format thousands

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.

Excel custom formatting thousands

Health warning

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”.

Excel paste special

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.

Happy modelling!

Read more

You’re reading stories from Financial Training Associates Ltd, the company that brings you the best in Excel modelling happiness.

Read more