Hi there - want to learn how to remove Excel formulas and keep the results of
the formula, therefore keeping the value only and removing the formula completely?
Hey I'm Sharyn from Excel at Work where we offer straightforward non-technical
assistance to help you save time, work smarter or improve your Microsoft Office
skills in some way . If you share Excel files outside of your organization
you'll enjoy this video where I share with you how to replace formulas with
values so that you can confidently send Excel files
knowing you aren't sharing confidential information inside of your formulas.
A must-watch if you share any files that contain formulas where margins,
markups or discount rates could easily be disclosed inside of a formula. For
example, price lists and quotes and I'll also share with you a quick way to
ensure all of your formulas in your worksheet have been replaced with their
value and if you personally have any super helpful paste tips that may be
really helpful to others go ahead and share them we'd love to hear from you.
Hello and welcome to this lesson. Now I hear way too often horror stories about
confidential information which has been sent outside of a business by mistake
because somebody has forgotten to change formulas. Formulas that might show profit
margins, they might show markup they might show discount rates all
confidential to us to our business information has been seen outside of the
business to people that shouldn't have it look what we've got here is a little
example and this is a good example of a template of sorts within Excel kind of a
layout for a couple obviously you know with placeholders are you put the
customer name the date and end your name in here now it has been set up and this
is ideally why people put quotes inside of Excel look at this if we come into
here it's all full of formulas so if I came and weand okay there's going to be
75 pages watch what happens did you see everything kind of up
so I'll show you what's going on this area over here is used to do
calculations to work out exactly how much it's going to cost to produce this
particular print job and of course if I wanted to I could update the number of
copies as well and look everything just updates so that's the beauty of using
Excel which you know we do and I guarantee you'll be able to read what's
going on if I come over here of course we're using 100 GM paper so we're
pulling from this area over here that is watch this so there's a formula in the
air that's taking the 75 pages and multiplying it by the copy costs up here
for a hundred GM paper get it okay now these are just being plugged in and you
could link them up to here if you wanted to so if price increases we'll put in
here they'd come straight down here and then down down here we've got a sum so
it's just adding up the total price well obviously how much it's going to cost to
pull this particular print job for one one item and over here we have got
taking the cost of the job for us multiply it by the number of copies and
then it's multiplying it by 1.5 so there's 50% markup on there and of
course businesses doing it because we need to pay overheads but do we want to
share that information this is the question most of the time no
we don't I just really quickly this is just GST obviously and then taking that
neat figure and adding the gist here on top of it okay
so once we've actually filled this out routine that I've put names up here this
is normally what happens when you get somebody that's not aware of the fate
that you know information is actually pulling from different parts of the
worksheet this is normally what would happen that would go well I'm gonna send
this you know my customers asked for me to send it to them in a spreadsheet
format of course I could PDF the person of course they can't see the formulas
which is great okay so this is what most people do they come over here of course
they do a file save as first and save it underneath the clients name and then
that come over here and go what I'm just going to get rid of this information so
when we go clear all so um that wasn't really probably what they were going for
what can you say where I'm leading with this
so what I would suggest there would have done for two reasons one of course you
don't want to leave the formula in the background showing that you're doing a
50% markup no in tow if you want to disconnect from the formulas over here
you're going to do a paste 1 2 3 so this is what we would do we would highlight
down here we would do our copy remember to leave it in place and then you come
straight over the top of that and you do your paste 1 2 3 now look so as far as
the customer is concerned that just numbers course they can Haven I'll just
hit my Escape key of course they can have it in the excel that's fine you can
do they can do with it whatever they like then and of course we can get rid
of this on the Home tab clear clear all and look still got my numbers and they
cannot say those formulas in the background so I guess what I'm trying to
do as I'm scaring you a little bit I know and I need to because it is really
really important you know there's 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 oh there's stuff in there and rather they didn't say these formulas in
there and rather they didn't say copy paste 1-2-3 paste values okay if you are
unsure PDF is always a good idea if you don't need to give somebody a a Excel
copy but you do need to give them an electronic copy and of course printing
as well they can't get to the information or the formulas and printing
so I really hope this instruction has been helpful and is going to help you
feel more confident when sharing files just remember select the cells
containing the formulas do a copy and then do a paste value now here's a quick
tip on how to ensure all of your formulas are replaced within a worksheet
so one more really handy hint when you're working with calculations that
you would rather people didn't see and you wanted to use your paste values of
course you can see and you have been showing you throughout the video that
you can see that there's a formula on here
on cell h4 15 and it's be 10 x h5 obviously and then of course all the way
down here we've got a sum and already you know that we've got these little
formulas over here so there are times when people say to me look how do I know
that every formula has been replaced by its value if I'm going to send this out
to somebody I want to know that I haven't missed one that they're all gone
so I want to show you a quick hint what you can do is you can go up to the
corner at the top here take your mouse pointer over that little arrow and click
now what will happen is you'll select the entire worksheet at this point you
can then do your copy or control C whichever one you prefer and you'll
paste values paste 1 2 3 now I'll just hit my Escape key to stop the less we're
going around and I'm going to come down and let's have a look there we go so we
have taken the calculations away and we replaced them with the values that were
there let's gonna have a look over here look at this everything now is just
numbers and don't know if you know this but you can actually go up to the
formulas tab and you can come over here and you can go show formulas now if you
click onto that and you have a good look around your worksheet and you can no
longer see any formulas you've done it correctly and you can then go off you'll
I can actually send this off now and know that it's all good if this has been
helpful please let me know in the comments below and if you use Excel and
want to stay connected just hit that like button and please make sure that
you subscribe because we're sharing new videos regularly and if you know someone
who would benefit from this help please be sure to share this video with them
hey thanks for watching and have a fabulous day