Percentage formatting / dec places

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
Guys,

Simple one... I have a cell formatted to a percentage. When the users enter .92 it displays 92%

How can I format this cell so it will not actually convert the entry to a &age, just display it. If the user enters 0.85, it needs to display 0.85%, not 85% as it does at the moment.

Cheers
Bob
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Have them enter it as 0.85% ( i.e. physically type the % with the 0.85 ) ... otherwise 0.85 is 85% percent, and can understand why Excel does the automatic conversion. I'll have a think about alternatives just in case typing the % sign is a no go.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,523
Office Version
  1. 365
Platform
  1. Windows
Mathematically speaking, .85 = 85%. It sounds like you want to actually enter in the percentage, not the value. We can do that with a macro that will divide all entries by 100.

Right click on the sheet tab name, click View Code, and paste this code in the following window. This code is written so that any entry in column F will automatically be divided by 100. Adjust the range accordingly. This macro will be triggered automatically upon entry.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
'   Enter range to apply this to
    Set myRange = Columns("F:F")
    
'   Divide numeric entries in defined range by 100
    If Not Intersect(Target, myRange) Is Nothing Then
        Application.EnableEvents = False
        If IsNumeric(Target) Then Target = Target / 100
        Application.EnableEvents = True
    End If
    
End Sub
Also, make sure that you have your column already formatted as a percentage with the correct number of decimals (2?).
 

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
Thanks JM,

I hadn't contemplated a macro as I thought there maybe a formatting solution. I know that 0.85 is mathematically 85%, but trying to explain that to builders, and then tell them they need to enter the % sign is a no go !

I'll code it !

Thanks
Bob
 

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079

ADVERTISEMENT

Actually, if they enter .92, it displays 92%, if they enter 0.92 it will display 0.92%. How can the leading zero make that much difference ! LOL

Thanks
Bob
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,523
Office Version
  1. 365
Platform
  1. Windows
Actually, if they enter .92, it displays 92%, if they enter 0.92 it will display 0.92%. How can the leading zero make that much difference ! LOL
Strange, my computer doesn't exhibit that behavior. Wonder if it is one of those subtle differences between the US and European versions of Excel.
 

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079

ADVERTISEMENT

Whatever it is, I call it VERY ANNOYING !
 

Watch MrExcel Video

Forum statistics

Threads
1,119,272
Messages
5,577,133
Members
412,769
Latest member
VK12345
Top