If you share Excel files outside of your organisation you’ll enjoy this post where I share with you how to remove Excel formulas 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.
Join the 'Excel at Work Insiders' group today!
100% free. Join now and unlock offers exclusive to this group!
Receive tips and guidance to help you SAVE TIME, WORK SMARTER and IMPROVE YOUR UNDERSTANDING of Microsoft® Office.
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.
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.
Replace the formulas with values
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...
“Do I need to use Copy, Paste Values?”. 🙂
Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.
She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.
If you enjoyed this you may also enjoy...
Learn the BEST Excel keyboard shortcuts. Perfect for Beginners and for those who want to save time and work smarter. It doesn’t matter if I’m delivering a Stage 1 or a Stage 3 Excel training course – when I’m demonstrating to the class I whip around the screen, format, select and modify and update using shortcut … ...
Learn how to move, copy and paste information in Excel. Once you have mastered Cut, Copy and Paste you will find yourself shifting data around easily and reusing copied data, saving you the need to recreate the same information again. All Windows applications have access to the Windows Clipboard. This is a special area used for … ...
Learn how to add colour, borders and alignment settings to your Excel data to give your worksheet a more professional look and to make it easier for others to read. ...
Formatting lets you change the appearance of data to control exactly how Excel will display the data on a worksheet. An example of formatting is to add dollar signs, percentage signs and decimal places to Excel data. Join the ‘Excel at Work Insiders’ group today!100% free. Join now and unlock offers exclusive to this group!Receive tips and guidance … ...
Learn how to preview your work before printing and how to print all or part of an Excel worksheet. Join the ‘Excel at Work Insiders’ group today!100% free. Join now and unlock offers exclusive to this group!Receive tips and guidance to help you SAVE TIME, WORK SMARTER and IMPROVE YOUR UNDERSTANDING of Microsoft® Office. envelope envelope Join … ...
Learn how to insert page breaks and scale an Excel worksheet for printing. Once you have your worksheet created and formatted you may want to print it. Printing can be extremely challenging if you don’t know how to control where page breaks occur and how to fit your worksheet to print on a page so that … ...