Changing many cells in Excel to recalculate new values after VAT changes

So you have a spreadsheet with lots of values in – future monthly invoices for service contracts, say. Actual values, not calculations which multiply up by a VAT rate stored in another cell, or a named range, or even as a fixed number in a formula. And the Chancellor of the Exchequer just announced that the VAT rate (sales tax for our colonial cousins) has changed so all your values are now going to be wrong for the next twelve months.

What can you do to change many cells at once by a specific amount?

A few approaches spring to mind, depending on the scale of the problem and the structure of your data.

1 – Write a macro

You could create a macro to go to all the relevant cells and change them by the appropriate amount (multiply by 115/117.5 in our case). But you can’t change historical cells, and you shouldn’t change any values too far into the future (the new rate only applies for one year it seems). So for every worksheet of every workbook you need to make sure you only apply your wonder macro to the right cells.

Sounds like a lot of effort to me, and too open to risk of changing values you should not (and possibly without any audit trail), but the up-front investment of time might pay off if you had loads of identical sheets to run it on.

2 – Use temporary formulas and paste special (values)

Easy enough – in an unused column or row, create a formula to calculate the right values from the original data (eg =A1*115/117.5). Copy this down (or across) to build a new range of corrected data. Copy all these cells then use edit > paste special > values to paste this over the original data.

Not bad, but laborious, and if you have lots of separate ranges to do this could take a while.

3 – Use paste special on its own

About 90% of people I meet when doing training courses have only ever used Paste Special to copy values without the formats and formulas; it’s like the rest of the dialogue box is just a blur. This is even worse in Excel 2007’s ribbon, where you use the drop-down arrow under the Paste button and choose Paste Special Values directly without ever seeing the full range of options. I like that the frequently-used option is even quicker to find and use now, but wonder if some people won’t ever discover the rich set of options they could be using. This technique will utilise one of the lesser used options of paste special.

Find a single unused cell in your spreadsheet and put in the correction factor – a formula such as =115/117.5 would be great for our purposes (0.978723 to be precise). Now copy that cell (CTRL-C). While that selection is still copied (showing the “marching ants” border round it), select some of the cells you want to change and go to edit > paste special (or use the drop down under the Paste button in Excel 2007).

Now choose one of those options you usually ignore – multiply. Click OK and hey presto! The target cells are all multiplied by the source multiplicand. Even better – it is still selected as a copy source, so you can just go find your next range and select that and paste special > multiply again. And again, and switch to the next worksheet in your workbook and do it some more.

For the keyboard lovers out there, the easiest way to get to paste special is Alt then E, S, then M (for multiply) and Enter for the OK button. So, copy the source cell, select your target, Alt, E, S, M, Enter. This will still take a while, but I think it could turn out to be the most efficient in terms of actual clicks, and it is all undo-able one step (pasted range) at a time if you click on the wrong thing at any point.

Differences between versions

If you use this Paste Special > Multiply function (or similar ones such as divide, add etc) you should be aware that the different versions of Excel achieve this in slightly different ways. In all cases any formula in your target cells will be preserved, but numbers may get overwritten or not, depending on the source cells and your version of Excel.

In all versions that I have available to test*, if both source and target contain just numeric values (no formulas) then it calculates the result and pastes this straight in. In Excel 2007 if the destination you paste over had a formula in to start with it writes in a new formula to do the multiplication, using the value of the source cell, but it never creates a formula because the source cell had one, it always uses the resulting value from there.

Using Excel 2000 and 2003, if either the source or destination cells have a formula in then in it builds a formula in your target cell to do the calculation without asking. So if you use the formula =115/117.5 to get your multiplier, then this forces Excel 2000/2003 to create formulas in the target cells even if they only had plain numbers in them, which you may not want. You could be better off using the explicit value 0.978723 so that it will only create formulas if it needs to. Alternatively, select “values” as well as “multiply” to force it to use the result from the source cell, rather than the cell contents as a formula (so the key sequence becomes Alt, E, S, V, M, Enter).

*I don’t have Excel 2002 / XP, but I am pretty sure it will be identical to 2000 and 2003 since it comes between those versions

Warning!

OK, nothing is perfect. When you use this feature of paste special, blank cells are replaced with 0 (zero). Ticking the “skip blanks” option has no effect, it multiplies the implied zero by our number to give a zero result.

Why does this matter? Well, it might not matter if you select specific cell regions, but if you select whole rows or columns at a time, you will get all zeroes to the end / bottom which may not be ideal – it may try to print pages with these on, for example, if you have page layout settings such as “1 page wide x 20 pages tall”, and you were relying on the fact that it never prints past page 12 if everything below there is blank.

Similarly if the blank cells are part of a range which you calculate average values from, these cells will now be counted even though they do not contribute to the sum (so if you previously had 117.5, BLANK, 117.5, BLANK you would now have 115, 0, 115, 0. Your AVERAGE will have shifted from 117.5 (=sum of 235 divided by count of 2) to 57.5 (=sum of 230 divided by count of 4). Other functions such as SMALL, COUNT etc may be distorted by having lots of zeroes to include where previously there were blank cells to ignore. If you are not using such summary functions, or do not have blank cells in the regions you are changing, then there is no problem.

Formatting considerations

You are multiplying a number (our VAT fixing factor of 0.978723) with several decimal places by another number with probably a couple (if your values have pence / cents  / etc). The result will therefore have even more decimal places – anything up to eight, potentially (where you are multiplying 3 one millionths by some number of hundredths). If your cells are already formatting as “Number” with an explicit number of decimal places showing, that will be retained and everything will be fine. If the target cells are formatted as General (but just happen to have two decimal places on their values) your new result will have lots more.

So you will probably have to re-format your target cells either before or after the exercise of multiplying them out (leave the source cell showing its full decimal value). If you are happy to keep the underlying precision of the new calculated numbers, then I would suggest reformatting afterwards so you can see which ones you have done.

Whether your cells are already formatted to two decimals, or you do this afterwards, Excel will as always be storing the actual, detailed, tiny decimals underneath. This could be seen as more accurate, but can make sums appear to be incorrect due to rounding errors. So what if you don’t want that? Read on…

Precision as Displayed

If you turn on “precision as displayed” then whenever Excel performs a calculation, it looks at the format for the cell, displays what you are expecting, and then throws away any additional detail that it is not showing you. Yes, throws it away, discards it, gone for ever. Use this option with extreme caution. Luckily it is a per-workbook thing so this will not affect other files you work on, and it will persist with the file you use it on when other people open it.

So – step one, format your cells to display what you need (probably two decimal places). To turn on the option you want:

  • Excel 2000 / 2002 / 2003 go to Tools > Options > Calculation > tick “precision as displayed”, then OK, then OK to acknowledge the warning.
  • Excel 2007 go to Office button > Excel options > Advanced > calculation (scroll way down) > tick “precision as displayed”, then OK to acknowledge the warning, then OK to save the settings

Now when you do any calculations in this workbook, what you see is what is stored – but there’s no going back to get more detail out, so make really sure your display formats are what you want before you start.

A better way

No, not a better way to fix it if you already have a problem, but a potentially better way to build your spreadsheets in future:

Create a named range called VATRate (VAT is too short a name for Excel 2007 since there is a column of that name, and everything else up to XFD, but older versions such as Excel 2003 would be fine with it). Don’t put a cell reference in there, just put our formula “=115/117.5” or just =0.978723 directly. Now we can use this name wherever we like and at any later date just update the formula in one place – our named range. When you build any formulas that calculate out a value including VAT, refer to this named range as part of your formula. Eg, C27=A1*B27*VATRate.

About ukcrmguru
I'm an MVP for Dynamics CRM, consultant, Microsoft Certified Trainer and self-confessed geek. I also lead the UK CRM User group when I'm not too busy with all that.

Comments are closed.

%d bloggers like this: