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
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.