Excel Help please

[Disneymaddaddy]

Hi everyone

Can someone please help with an excel question?

I have a table 120 rows and 30 columns all with various costs in and I want to add 20% to them all. I only know how to go into each cell and add *1.2 but this will take an age!

Anyone know how I can do the whole sheet in one go. (The costs are all different)

Thanks in advance

[Damn Dirty Ape]

You could insert a formula in an adjacent column eg. =D2*1.2 with D2 containing your data. Then copy that formula down the rows. If you don’t want this column then you could copy the column data back into the original column as numbers as opposed to copying the formula

[Puddle5]

Someone will probably be along with a better solution but
you can just put formulas into adjoining columns and copy down and across

Example Formulas:

Values:

Can then just delete original rows

Remember if you double click on the green box (bottom left corner) of the top formula it will copy it all down the column

Sorry if this is to simplistic

[Disneymaddaddy]

Thank you both x will try these!

[Bozza]

Originally Posted by Puddle5

Someone will probably be along with a better solution but
you can just put formulas into adjoining columns and copy down and across

Example Formulas:

Values:

Can then just delete original rows

Remember if you double click on the green box (bottom left corner) of the top formula it will copy it all down the column

Sorry if this is to simplistic

See more...

As soon as you delete the original rows, the new rows will be incorrect/error since the cells used as part of their calculation no longer exist, ie D3 can't be A3 * 1.25 because A3 has been deleted.

The way round this is to do your first step, ie calculating values in new cells, then:

- select the new cells
- copy those cells
- Paste, Paste Special, Values

You can either paste these into a new sheet, or paste them over the original values (don't worry - you can undo if something goes wrong).

This will retain the values calculated, and free them from being tied to the cells you no longer want.

[Puddle5]

Originally Posted by Bozza

As soon as you delete the original rows, the new rows will be incorrect/error since the cells used as part of their calculation no longer exist, ie D3 can't be A3 * 1.25 because A3 has been deleted.

The way round this is to do your first step, ie calculating values in new cells, then:

- select the new cells
- copy those cells
- Paste, Paste Special, Values

You can either paste these into a new sheet, or paste them over the original values (don't worry - you can undo if something goes wrong).

This will retain the values calculated, and free them from being tied to the cells you no longer want.

See more...

Thanks Bozza, forgot about that!