Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Romany
 
Posts: n/a
Default Formatting percentage signs in Excel

Is it possible to change the size of the percentage sign that Excel inserts
in cells when the number in the cell is a percentage? If there are a lot of
percentages in a table, it is very difficult to read the numbers because the
percentage signs detract from the numbers. It would be good if there were a
way to get Excel to automatically insert the percentage sign in a smaller
font.
  #2   Report Post  
Peter Jausovec
 
Posts: n/a
Default

Hi,

If the cell format is set to Percentage you can't change the font sizes
within the cell or have two different font sizes. But, you can change the
cell formatting to Text and then change the percentage sign size - change the
font size.

--
http://blog.jausovec.net


"Romany" je napisal:

Is it possible to change the size of the percentage sign that Excel inserts
in cells when the number in the cell is a percentage? If there are a lot of
percentages in a table, it is very difficult to read the numbers because the
percentage signs detract from the numbers. It would be good if there were a
way to get Excel to automatically insert the percentage sign in a smaller
font.

  #3   Report Post  
Romany
 
Posts: n/a
Default

Thanks for this. Unfortunately, there are thousands of percentage signs in
the worksheet, so this isn't really an option. Never mind. I knew it was a
long shot.

"Peter Jausovec" wrote:

Hi,

If the cell format is set to Percentage you can't change the font sizes
within the cell or have two different font sizes. But, you can change the
cell formatting to Text and then change the percentage sign size - change the
font size.

--
http://blog.jausovec.net


"Romany" je napisal:

Is it possible to change the size of the percentage sign that Excel inserts
in cells when the number in the cell is a percentage? If there are a lot of
percentages in a table, it is very difficult to read the numbers because the
percentage signs detract from the numbers. It would be good if there were a
way to get Excel to automatically insert the percentage sign in a smaller
font.

  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Wed, 1 Jun 2005 05:51:06 -0700, Romany
wrote:

Is it possible to change the size of the percentage sign that Excel inserts
in cells when the number in the cell is a percentage? If there are a lot of
percentages in a table, it is very difficult to read the numbers because the
percentage signs detract from the numbers. It would be good if there were a
way to get Excel to automatically insert the percentage sign in a smaller
font.


Not directly.

As far as I know, you can only change the size of particular characters in a
cell if the entry is a text string.

So if the original entry is the result of a formula giving 0.6523879 and
formatted as %, you would have to turn that into a text string with defined
formatting; for example 65.24% and then format the % sign into a smaller font.

You could still use this value in *certain* math equations, but it would not be
as precise as the original.

The above could be automated with a macro. For example, if your % data is
A1:A100, the following macro

reads the value;
formats it as a percent with two decimal places;
outputs the value as a text string in the adjacent column;
and makes the % sign in a small font.

You could do this on your original data, changing it in place, but you would
permanently lose precision and might have to change some of your formulas.

But this should give you some idea of what is possible.

To enter this, <alt-F11 opens the VB Editor.
Ensure your project is highlighted in the project explorer window, then
Insert/Module and copy/paste the code below into the window that opens.

To use this macro, <alt-F8 brings up the macro dialog box. Select
SmallPercent and Run.

Obviously, you may need to change the "src" range and you may want to change
the Offset parameters, as well as the "fmt" code.

===================================
Option Explicit

Sub SmallPerCent()
Dim c As Range, src As Range
Const fmt As String = "0.00%"
Dim out As String

Set src = Range("A1:A100")

For Each c In src
out = Format(c.Value, fmt)
With c.Offset(0, 1)
.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Formula = CStr(out)
.Characters(Start:=Len(out), Length:=1).Font.Size = 6
End With
Next c
End Sub
==============================


--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Wrap text limits in Excel 2003 cell formatting Adelrose Excel Discussion (Misc queries) 1 April 19th 05 06:32 PM
excel formatting Lexicon Excel Discussion (Misc queries) 2 April 19th 05 06:23 PM
Excel could not save all the data and formatting you recently ad.. Houndstooth Excel Worksheet Functions 0 April 18th 05 03:05 PM
Despite formatting a column in Excel 2002 worksheet as Short Date. Pete Whalley Excel Discussion (Misc queries) 2 February 7th 05 07:17 PM
Can Excel add a set percentage to a cell automatically when you type in a number Chris B. Excel Worksheet Functions 3 October 29th 04 07:08 PM


All times are GMT +1. The time now is 04:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"