Dividing all your numbers in Excel by 1,000 - quickly
Imagine you have source data in an Excel financial model. But then you suddenly realise that it would make sense to have each of those numbers divided by 1,000 (on the basis that the extra detail is not needed and you’d like to ‘clean up’ your workings).
Sometimes there’s a few tricks to doing something in Excel that seems it should be easy – but just isn’t.
Imagine the source data (yet to be divided by 1,000) was being pulled into Excel from a big block of data, and was already linked to various areas of your spreadsheet where each piece needed to be used.
You might have a job ahead of you. You might start thinking that you should find each link into the source data and type “/1,000” on the back of each of the links.
If the data were landing into different points of the spreadsheet, it wouldn’t be a simple case of modifying one link and filling across and down. You might start thinking that, after modifying one link, you’re going to need to find the next and type “/1,000” on that one too – and so on and so on. The process is going to get pretty repetitive and dull.
Thankfully Excel ‘paste special’ can help us bypass most of the monotony. Here are the steps to follow.
Step 1: find a spare call and type in the formula "=1000"
Step 2: "Ctrl C" (copy) that cell
Step 3: select the data that needs to be divided by 1,000 and paste special "Ctrl Alt V"
When the paste special dialog pops up, select “F” for formulas and “I” for divide.
Voila! All your links are magically divided by the formuala =1000.
Now start selecting any other links sprinkled around your spreadsheet (before doing anything else in Excel) that need the same treatment and press “Ctrl Y” or ‘Redo’, and those other links will be divided by the =1000 formula too.
It’s a little bit tricky, but a lot better than the alternative, should you find yourself tempted to modify a lot of different existing links or formulas in a spreadsheet in exactly the same way (where, for whatever reason, you can’t do the usual “Ctrl R” fill right or “Ctrl D” fill down).
Other favourite paste special options
We love paste special use it all the time. It’s often “Ctrl C” followed by “Ctrl Alt V” and then:
- “T” for formats
- “V” for values
- And even “W” for column widths when you want to get the layout on one tab the same as another tab.
Paste special definitely makes anyone’s list of favourite Excel shortcuts and features, simply because it’s so useful. And, occasionally, when it can save time by modifying a bunch of existing formulas, it might even start to feel as if you’ve just enjoyed a bit of a free lunch!
