MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Subtract 3 from a Column


June 09, 2017 - by Bill Jelen

Subtract 3 from a Column

You need to reduce every number in an Excel column by 3 or 7 or 22 or 12%. Here are several ways that you can quickly solve this problem.

Watch Video

  • How to subtract a value from a column in Excel
  • Method 1: Temporary column with =D6-3, Copy and Paste values
  • Method 2: Put the 3 in any cell. Copy it. Paste Special, Subtract

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • episode 2099 subtract a value from a
  • column hey welcome back to MrExcel
  • netcat I'm Bill John how 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 our answer but the
  • answer they gave was not the answer that
  • I might suggest the answer that's
  • already out there is just great this
  • little temp call them out here and let's
  • say that we want to do safety stock
  • minus 3 subtract 3 from every value in
  • the safety stock all right but I looked
  • at that formula so well you know
  • someone's not posting out on Quora to
  • try to 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 all right
  • and then get rid of the temp call so I
  • went with a slightly different way that
  • I admitted was risky right you have to
  • be really careful when you do this so
  • here's here's my method I'm just going
  • to go to any blank cell put the 3 in
  • arrow and 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 what we want to do is paste
  • special so I'm going to right click
  • paste special and then in the pay
  • special we will click subtract all right
  • so we're gonna 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 always choose values here and then
  • click OK all right 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
  • right you know there's a lot of jail 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 all right
  • 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 by page for that
  • book the shopping cart page for that
  • book all right today's question from or
  • how to subtract a value from a column in
  • Excel the method that was already
  • they're temporary column equals D 6-3
  • copy that down copy the whole thing
  • paste values but I tried with copy paste
  • special subtract values okay I hey I
  • want to thank you for stopping by we'll
  • see you next time for another net cast
  • from MrExcel

Download File

Download the sample file here: Podcast2099.xlsm

Title Photo: Olaf Pictures / Pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.