How can I modify a lot of values easily in Excel?

Question

I have a big file of numerical data with 600 columns and 4000 rows. I would like to be able to change the data of about 3/4 of the columns by multiplying them with 1.02. For example, let's say I have a row like that :

5 6.2 8.2 9.6 3.2 55.6 3

and I would like to obtain

5 6.2*1.02 8.2*1.02 9.6*1.02 3.2 55.6*1.02 3*1.02

There is no pattern of the distributions of the columns that needs to be changed or not, so I will do it manually...

I have found one solution which is to copy my sheet in another one, and use the change of cell to multiply the values, but it takes too much time to write the reference for each column (i.e. to write =E6*1.02, then =F6*1.02 in the next column, etc.).

Is there a way for me to select all the columns that need to be changed and do this only one time?

Answer 1

Multiplying with 1.02

1. Click on any other empty cell, and type 1.02 and copy it, now select the cell/column range you want to multiply with 1.02
   
2. Right-click on the selection and select Paste Special..
   
3. Choose All and Multiply and click OK, as shown in the picture below: Now all the selected cells will be multiplied with 1.02. Now you can delete the cell value1.02 you copied from.
   

Answer 2

Yes, you can do this simply by copying the values to another sheet, then writing the formula =OtherSheet!A1*1.02 to cell A1 (or the first cell that needs the multiplier), then copying that, selecting all the cells that need this change with CTRL pressed and then selecting paste.

And if you copied the values, you then have the original values in other cells.

After this if you need the values without formulas, you can copy them all and paste values, discarding the formulas completely.

Answer 3

Since you are manually identifying which columns will need to be multiplied by 1.02, and you just need to then quickly translate the formulas across, perhaps this is the fastest approach:

Row 1    (formula)
Row 2    1               1              1
Row 3    ColA    ColB    ColC    ...    ColZ

Insert two rows above the row of columns that you want to multiply 1.02 by, so relatively speaking, the row containing the numbers becomes the 3rd row.

Since you said you were manually identifying the columns, just quickly go across on Row 2 and type 1 (i.e., right arrow, 1, enter, right arrow, 1 enter, repeat).

In Row 1, you'll enter the following formula:

=if(Row2ColA=1,(Row2ColA)*1.02,"")

Now you can paste this formula across the entire row. If you don't want these two additional rows, you can now just copy the entire of Row 1 (post-calculation), and Paste Values into Row 3. Then you can safely delete Rows 1 and 2.

For about 600 columns this should really only take a few minutes.

Hope that helps.

Update: ISAAC's answer is even faster. Try it out! (Just be careful not to accidentally deselect your selection before you get a chance to even multiply).