If you share Excel files outside of your organisation you’ll enjoy this post where I share with you how to remove formula in Excel and keep the results so that you can confidently send a file knowing you aren’t disclosing confidential information inside of your formulas.
I hear way too often hell stories about confidential information being sent outside of a business by mistake, because somebody has forgotten to replace formulas with values.
Formulas that might disclose profit margins, mark-up, or discount rates. All confidential to us, to our business.
Information has been sent outside of the business to people that shouldn't have it, inside of formulas.
There are normally two reasons behind this:
- somebody has forgotten to actually change the formulas to values; or
- somebody's forgotten to TEACH somebody to change the formulas to values.
Let’s walk through an example together.
View formulas in Excel
Below we have an example of a template Order form that has been created in Excel.
Placeholders have been inserted so that you can quickly update them with the customer’s details.
Formulas have been inserted so that the quote can quickly be created with minimal input.
By simply updating the number of pages required and the number of copies the quote will populate the cost price for us and then multiply this price by 1.5 to include a 50% markup. And of course businesses do that, because we need to pay overheads.
But do we want to share that information with our customer?
Most of the time, no we don't.
Therefore it makes sense to replace the formulas with the actual calculations RESULT, the value you can see in the worksheet.
The process of removing the formulas and leaving the value instead is referred to many different ways.
Some people call it Paste Values. Others call it Paste 123. And there are a few who call it Paste Special.
All you need to remember is that the process I’m about to take you through removes CONFIDENTIAL information that we would rather wasn’t disclosed, and replaces the formula with JUST the number.
Of course you could send a PDF of the worksheet, and of course they can't see the formulas, which is great. But what if they have requested an electronic version? That’s when we replace the formulas with values.
How to remove formula in Excel
Do a File, Save As first, and save it as a copy of the original template. Maybe use the customer’s name in the file name so that you can easily identify it.
Now select over the cells that currently contain your formulas which have confidential calculations in them. In this case it will be cells E15 to E17.
Now Copy the formula (press Ctrl + C or right-click over the selected area and then left-click Copy).
Now leave the same cells still selected, and then straight over the top of the same selection, do a right-click and then left-click to select Paste 123.
Your formulas will now be replaced with just numbers. And you can now confidentially share the file with your customer knowing that they can do with it whatever they AND they cannot see those formulas in the background.
I may be scaring you a little bit, I know, and I need to because it is really, really important.
You know there's so many things that we need to keep confidential within our business that we don't share with other people.
So, if you're thinking before you send an Excel spreadsheet out, and a lot of people require you to now, customers ask for it.
Before you send it out, if you think, there's stuff in there I'd rather they didn't see. There's formulas in there I'd rather they didn't see, please use Copy, Paste 123, Paste Values.
PDF or a printed copy is always a good idea if you don't need to give somebody an Excel copy, but if you do need to give them an electronic copy ALWAYS slow down and ask yourself...Was this blog helpful? Let us know in the Comments below.
If you enjoyed this post check out the related posts below.
