How to decrease selected values by a certain percent in Excel

Question

I often need to change a certain amount for a set percentage (let's say 20%). I do not need to change the whole column just certain cells. How do I do that?

Example

Values

200

234 <--- I wish to decrease this for 20%

567<---- This for 20%

789<---- This for 20%

335

778

367<--- This for 20%

New values should replace old values. I found a formula on how to change the percentage value of the whole column but that is not helpful to me.

So is there a way to apply the decrease of 20% on selected cells (and selected multiple cells) without changing it in the whole column?

Thank you for the responeses :)

Answer 1

To decrease a number by 20% you need to multiply by 0.8.

Enter 0.8 in any available cell

Copy that cell (CTRL-C)

Now select the cells you want to decrease (CTRL-Click if they are not contiguous)

Choose Paste Speical

Choose MULTIPLY radio button and click Ok

Answer 2

Type .8 into a cell somewhere and then copy it. Select all the cells in your column that you wish to affect (Press the CTRL key when selecting cells to select individual multiple cells)

Then hit paste special and select multiply.

Answer 3

Hi

For a change on active cell

Macro reside in the sheet module code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim cl As Variant

Dim nPercent As Double

nPercent = 20

cl = Target

cl = cl - cl * nPercent / 100

Target = cl

End Sub

For a change on all selected cells

Sub ChangePercent()

Dim cl As Range

Dim nPercent As Double

nPercent = 20

For Each cl In Selection

    cl = cl - cl * nPercent / 100

Next cl

End Sub

Regards

JY

Answer 4

Quickly Updating Values

http://excelribbon.tips.net/T006137\_Quickly\_Updating\_Values.html

Summary: Need to apply a quick, standardized update to all the numeric values in a range? It’s very easy to do, without the use of formulas. Just use the Paste Special dialog box, as described in this tip.

If you are multiplying by the same amount, you can use <CTL> click to select the individual cells the use the technique described above.