If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formatting Columns of Percent Data
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 |
#2
|
|||
|
|||
Formatting Columns of Percent Data
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 -----Original Message----- 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 . |
#3
|
|||
|
|||
Formatting Columns of Percent Data
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 -----Original Message----- 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 -----Original Message----- 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 . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
formatting individual columns for different languages, including double-byte characters | Sandra Rosenzweig | General Discussion | 1 | June 20th, 2004 01:08 PM |
Data Table Numeric Formatting | Tom J H | Charts and Charting | 1 | June 8th, 2004 05:23 PM |
formatting columns | sue | Worksheet Functions | 6 | May 10th, 2004 05:18 PM |
conditional formatting comparing columns | SGM | Worksheet Functions | 2 | April 14th, 2004 04:32 PM |
Keep pivotchart formatting when data is refreshed? | Pmxgs | Worksheet Functions | 1 | December 7th, 2003 02:09 PM |