Formatting Percentages

[Guest]

How can I format percentages so that if the cell contains .022 it reads 2.2% and if the cell contains .02 it reads 2% (with no trailing decimal point)? Currently if I use the format 0.0%, then 2% comes out as 2.% Thanks.

 

[Dave Peterson]

Can you use a helper cell for just showing the information?

=A1*100&"%"

And keep the original cell for calculations.

If no, how about some VBA?

This looks for typing in column A. When it finds a number, it looks to see how
it should be formatted.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

With Target
If IsNumeric(.Value) Then
If .Value * 100 = Int(.Value * 100) Then
.NumberFormat = "0%"
Else
.NumberFormat = "0.0%"
End If
End If
End With

End Sub

rightclick on the worksheet tab that should have this behavior and select view
code. Paste this in the code window. (Change "a:a" to the range where you type
in your percentages.)

And a couple of links to describe events and macros:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
and
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm