percentage trick

[Jigneshbharati]

Good evening,

I am learning about data cleaning. For example in Column A, values are given as 63.8, 62.4, 37.5 instead of 63.80 %, 62.40 %, 37.50 %, etc . I created another column and managed to get the desired out 63.80 %, 62.40 %, 37.50%. Is there a way I can achieve the following result without creating another column?
Desired output in column A: 63.80 % (two decimal places and % sign), 62.40 %, 37.50%, 48.70% and so on.
Thank you so much for your help!

[6StringJazzer]

Type 100 into any cell
Copy that cell
Select all of your data
Paste Special > Divide
Change format to %
Delete the cell with 100

[alansidman]

Please Login or Register  to view this content.

How to install your new code

• Copy the Excel VBA code
• Select the workbook in which you want to store the Excel VBA code
• Press Alt+F11 to open the Visual Basic Editor
• Choose Insert > Module
• Edit > Paste the macro into the module that appeared
• Close the VBEditor
• Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

• Press Alt-F8 to open the macro list
• Select a macro in the list
• Click the Run button

[Jigneshbharati]

Type 100 into any cell
Copy that cell
Select all of your data
Paste Special > Divide
Change format to %
Delete the cell with 100

Thank you so much

[Jigneshbharati]

Please Login or Register  to view this content.

How to install your new code

• Copy the Excel VBA code
• Select the workbook in which you want to store the Excel VBA code
• Press Alt+F11 to open the Visual Basic Editor
• Choose Insert > Module
• Edit > Paste the macro into the module that appeared
• Close the VBEditor
• Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

• Press Alt-F8 to open the macro list
• Select a macro in the list
• Click the Run button

Thank you. I don't know much about VBA. Can you apply this logic to multiple columns in the same worksheets? How can we do that?

[alansidman]

Yes. This concept can be applied to multiple columns. Supply a sample worksheet and I will demonstrate.