Converting data throughout sheet/workbooks

  • Thread starter Thread starter Keith Maynard
  • Start date Start date
Keith, to modify all cells in a worksheet, press Ctrl-A to select the entire
worksheet. Then Format > Cells > Currency, etc.

To modify all cells in the workbook, right-click any worksheet tab and
select Select All Sheets. All worksheet tabs will turn white and the word
[Group] will appear on the application title bar. Proceed according to the
directions in the paragraph above. Then right-click a worksheet tab and
Ungroup Sheets.
 
Thanks for the response. I don't think that takes care of doing a
numeric conversion. E.g. if the cell has 1 USD I would like it to read
0.60 GBP. The action I am looking for would multiply each selected cell
by the factor of 0.60, but I can't figure out how to do that.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
Format a cell to whatever monetary style you wish.
Then, enter 0.60 in the cell.
Right click in the cell and choose "Copy".
Now, select all the cells in question.
Right click in this selection and choose "PasteSpecial".
Click on "Multiply", then <OK>.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thanks for the response. I don't think that takes care of doing a
numeric conversion. E.g. if the cell has 1 USD I would like it to read
0.60 GBP. The action I am looking for would multiply each selected cell
by the factor of 0.60, but I can't figure out how to do that.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
Find an empty column somewhere. If possible, you can insert a column
temporarily right next to the one with the values in it. If your
values to be converted are in column A, starting right at the top, and
you are able to create a temporary column B, enter the following
formula in cell B1:
=A1*.6
Then copy that formula and paste it all the way down column B as far
as you need.

Now, highlight the entire column B, or even just the cells with the
new values in them. Highlight the same area of column A (either the
whole column, or the same number of cells you copied from B) and then
click the Edit menu, choose Paste Special, choose the "Values" option
in the "Paste" section, and then press Okay.

Now you have converted your formulas to values and replaced those new
values over the top of your old ones. Immediately delete column B,
because, as you see, it's now calculating .6 of your new values, which
means that column B is no longer valid.

By the way, it is not the best practice to "replace" values like that.
It's a pain to get the original values back if you ever need them -
especially if you make a mistake with your formulas or
copy/pastes/etc. If you must replace, be sure to save aside a copy of
your original file. Alternatively, you could just create column B
with it's .6 forumla and then hide column A and keep using the
spreadsheet as if column A never existed.
 
Back
Top