Subtract 3 from a Column

Learn Excel from MrExcel Podcast, Episode 2099: Subtract a Value from a Column.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.  You know, I was out on Quora the other day and this Excel question came up which had already been answered: How do I subtract a value from a column in Excel 2010?  And if someone's already answered, I don't want, you know, stamp on their answer but the answer they gave was not the answer that I might suggest.  The answer is already out there is just as great as little. 

Temp call them out here and let's say that we want to do Safety Stock -3, subtract 3 from every value in the Safety Stock column.  Alright, but I looked at that formula, so well you know, someone's not posting out on Quora to try and figure out how to subtract 3.  That's a pretty simple formula, you know.  I think they're probably looking for some faster way than copy and then come over here and paste as Values, alright, and then get rid of the Temp column.  So I went with a slightly different way that I admitted was risky, alright?  You have to be really careful when you do this.

So here's my method.  I'm just going to go to any blank cell, put the 3 in there; I want to subtract 3 from everything.  And then Ctrl C, copy that 3 to the clipboard and then select the column where we want to deduct the 3, select all of those cells.  And what we want to do is Paste Special, so I'm going to right click, Paste Special and then in the Paste Special we will click Subtract.  Alright, so we're going to take that 3 that's on the clipboard, subtract that 3 from all of these values.  In case this was a different format than these, like these have been formatted nicely and this is not, I would choose Values here and then click OK.  Alright, and there are numbers I've updated. 

Now, why is this risky?  Well, there's no audit trail at all.  I mean, what if you put in a 2 instead of a 3 or what if you put it in a 3 and then hit Add or Multiply instead of Subtract, alright?  You know, there's no audit trail.  But hey, face it.  Once we've deleted this column, there was no audit trail on this either so both methods require to be a little bit careful, alright, things like Paste Special, Subtract. 

In this book, Power Excel with MrExcel, click that “i” on the top right-hand corner to check out the buy page for that book, the shopping cart page for that book.

Alright, today's question from Quora: How to subtract a value from a column in Excel?  The method that was already out there, the temporary column =D6-3, copy that down, copy the whole thing, paste Values.  But I tried with Copy, Paste Special, Subtract, Values, OK. 

Hey, I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.