Forum Discussion

NClarke3's avatar
NClarke3
Copper Contributor
Sep 11, 2023

Change values on data sheet

Hi I need to change values from 1,2,3,4 to 0,1,2,3 on an entire data sheet.  What is the best way to do this please? Thanks in advance.

Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Sep 11, 2023

    NClarke3 

    If that's something like this

    you may enter -1 in any empty cell, copy it, select your range and Paste->Paste special->Add->Ok

    Delete -1 from the helper cell. Result is

    If something like this

    when one by one. Select nothing, Ctrl+H, match entire cell content, Replace All.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Sep 11, 2023

    NClarke3 

    To apply the transformation to all the data in the sheet, you can use the following approach:

    1. Insert a Helper Column: Start by inserting a new column next to the column where your original data is located. For example, if your data is in column A, insert a new column (let's say column B) next to it.
    2. Use the Formula in the Helper Column: In the first cell of the helper column (cell B1), enter the formula =A1-1. This formula subtracts 1 from the value in cell A1.
    3. Copy the Formula Down: Copy the formula in cell B1 down to cover all the data in your sheet. You can do this by selecting cell B1, then dragging the fill handle (the small square at the bottom-right corner of the selected cell) down to fill the formula for all rows containing data in column A. Alternatively, you can select cell B1, copy it (Ctrl+C), then select the entire column B and paste (Ctrl+V).
    4. Copy and Replace Values: Now, select all the cells in column B, right-click, and choose "Copy."
    5. Paste as Values: Right-click again in the same selection and choose "Paste Special" and then "Values." This will replace the formulas in column B with the calculated values, effectively transforming your data.
    6. Remove the Helper Column (optional): If you no longer need the helper column, you can delete it by right-clicking the column header (e.g., column B) and choosing "Delete."

    This approach will transform all the data in your sheet without manually changing each value. Just make sure you have a backup of your original data before proceeding in case you need to revert to the original values.The text and steps were created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark them as helpful and like it!

    This will help all forum participants.

Resources