# Percentage formatting / dec places

#### staticbob

##### Well-known Member
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

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.

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?).

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

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

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.

Whatever it is, I call it VERY ANNOYING !

I get that behaviour too ... UK, Excel 97.

I'm on Excel 2003, UK. But it also the same on 2000.

Replies
3
Views
278
Replies
3
Views
150
Replies
9
Views
234
Replies
19
Views
2K
Replies
5
Views
611

Threads
1,203,047
Messages
6,053,197
Members
444,645
Latest member
mee siam

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back