Formatting Columns of Percent Data

[TK]

Is there an easy way to align percentage data that has a
percentage symbol visible with percentage data that does
not show a percent symbol?

The proper way to display a column of numerical data in
formal reports is for the top row on each page or section
to show currency or percentage symbols, and for the
remaining rows to be aligned beneath this, but without
showing symbols.

This can be done easily enough with currency data by using
number formats with or without currency symbols, but I
have always wrangled with getting the percent format in
Excel to work this way. I usually multiply the percentages
by 100 and use custom formats to either show a literal "%"
or to reserve space for one, but this is getting to be a
nuisance, and I'm feeling particularly testy this
morning...

Does anyone have a better approach? Is it possible to
format data as a 'percent' without displaying a '%' symbol
using custom formats?

Any suggestions appreciated.

TK

 

[jeff]

Hi,

You could use a macro to format via:

Private Sub CommandButton1_Click()
Dim r As Range
'Set r = Range("O1:O5") ' or the next line
Set r = Selection ' this assumes you've selected cells
For Each c In r
If c.Row = 1 Then
c.Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.00%"
Else
c.Select
Selection = Selection * 100
Selection.NumberFormat = "0.00 "
End If
Next c
End Sub

jeff

 

[TK]

Jeff,

Thanks for the post!

That approach will work well in most cases, and I think I
will put it in a button on my toolbar and put the code to
work! However, it wont work for all fonts since the
percentage symbol is not always 3 space characters wide...
but a little tweaking to the format code in the rare cases
that it is needed will be a lot easier than what I have
been doing.

Thanks again,

TK