# Modify value of a cell using the old value as part of the new one?

I have a table in which row 2 has tons of columns. Each of those columns in row 2 has a varying value in the range of 1-1000000 represented by X in the example below.

How can I add modify the cells in row 2 to look like this:

``````=X/1000
``````

keeping the value of X, but adding the division of 1000 to each cell?

-
Welcome to SuperUser. I edited your question, feel free to revert the changes or edit it again. – Baarn Sep 8 '12 at 18:14

The good news is you are just looking to divide by 1000, so we just need to shift the decimal place. You could use a custom format on the cells. Highlight the entire row. Right-click a cell, and select "Format Cells".

Under "Custom", enter the format:

``````#0"."000
``````

You'll only end up with 3 decimal places, but as long as you're starting with integers, you should be fine.

-
Instead of forcing the location of the decimal point you can use commas after the number format to display the number divided by multiples of 1000. For example, `#,##0.000,` displays a value divided by one thousand and `#,##0.000000,,` displays the number divided by one million. (Depending on your locale you may need to switch . and ,) – Mike Fitzpatrick Sep 11 '12 at 5:42
i want a % sign added so i did '#,##0.000,%' and it's not giving the correct results – kittensatplay Oct 4 '12 at 21:30
@kittensatplay, put double quotes around the percentage sign: `"%"` and that should stop Excel trying to use its regular percentage formatting rules. – Mike Fitzpatrick Oct 5 '12 at 0:31
#,##0.000,"%" did not work, i also tried #,##0.0,"%" and others, i also read up on the custom formmating codes and not sure how to combine them into what is needed -- superuser.com/questions/483630/… – kittensatplay Oct 6 '12 at 5:53
I tired both `#0.000,"%"` (@MikeFitzpatrick style) and `#0"."000"%"`. Both seem to format in the percent sign. Make sure you have double-quotes around the percent sign. – dangowans Oct 6 '12 at 22:26

Presumably, this is something you only want to do once, precisely because it makes no sense to have a circular loop in your formulas causing the values to be divided by 1000 every time you recalc the sheet (even supposing Excel would let you do that.)

So here's what I would do: Create a new row and copy a formula dividing the original value above by 1000 into each cell. Copy the new row and then "Paste special ... values" into the original row. Then delete the new row.

-