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.
0
Romany (2)
6/1/2005 12:51:06 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
442 Views

Similar Articles

[PageSpeed] 22

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.
0
6/1/2005 1:22:04 PM
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.
0
Romany (2)
6/1/2005 1:36:03 PM
On Wed, 1 Jun 2005 05:51:06 -0700, Romany <Romany@discussions.microsoft.com>
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
0
ronrosenfeld (3122)
6/1/2005 6:02:02 PM
Reply:

Similar Artilces:

Help with Excel
I have windows xp on my new computer and it don't support excel, i have file on my old computer with excel how can i get excel to work with my xp version You will need to put Excel on your new machine - it is NOT part of Windows. If you have the Excel or MS Office disk from the old machine you can use that to install the applications on the new PC. Of course, once you have it running you must uninstall the apps for the old PC. You can install Excel 97, Excel 2000, Excel 2002 or Excel 2003 on the new PC. If you do not have the disks then you are in for a shopping spree. Best wishes Berna...

If I want to buid a simple database for customer details, should I use Excel or Access?
I want to buid up a database for store detailed information of customers. The key words would be contained Customer's name, contact, visited time, email address links ( That can be easily used for sending emails). I wonder which program is better, Excel or Access? I am thinking that Excel is more common to use. And If I set up Excel, its data can be imported to Access. There are two clues here. The first is "database" - Access is the database application. The second is that, in spite of that, you fancy Excel, probably because you are more comfortable with Excel. It is yo...

Excel and Windows Live Mail
Hi all I update my Mail add-in and VBA code examples to avoid the error if you use Windows Live Mail as your default mail program. More information in the blog post : http://www.dailydoseofexcel.com/archives/2010/04/25/windows-live-mail-wlm-and-excel-problem-part-two/ If you have problems let me know so I can update the code/add-in if needed Thanks -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm ...

ODBC server changed
Hi I have some Excel 97 files that used to pull in data from a Unix box using an ODBC driver (U/SQL from Transoft). We have upgraded to a new server which has a different IP address and now the embedded queries have stopped working. Is there any way to change the data source in the Excel 97 file? I noticed by opening the file with a text editor that the old IP address is buried in there but I can't change it as it corrupts the file. The query was created with Data > Get External Data > Create New Query. It has not been saved as a separate MS Query file. If I do manage to edi...

Linking to Another Excel File (File Name Changes)
Hi, I have 2 separate Excel file worksheets. Basically, one file is updated each month and will have a different name (example: File052007 for May, File062007 for June, File072007 for July, etc.). Currently, I'm trying to link cells in my other file to the updated file, but I don't know how to set the link so that it can change each month. I was trying the concatenate or (A1&A2&A3) options to try to link cells together, but haven't had much luck. Here is what the formula should look like: ='C:\FOLDER\[File052007.xls]Sheet1'!$A$1 I made 2 cells in this worksheet ...

Excel charts won't appear or preview. Help!
Hello, In Excell 2003 I cannot get my charts or graphs to appear or preview. I was able too at first. But I accidentally hit right click and then clear several times after having created template line chart for the first time. This subsequently one at a time removed different aspects of my chart until my chart was gone. And now i cannot get the charts to appear again. Not even a preview. Thank you in advance foe your help and advice. This problim has been solved. Thank you. ...

Save copy of active sheet
I use the code below to save a copy of my Workbook in a specific file path (testing to see if the folders already exist along the way) with a specific, data dependent file name. It works, but I'd like to tweak it a bit and I'm not sure how to write the code. I'd like to save only the active sheet instead of the whole workbook. I'd also like to save only the values and formats to the new file, not the formula and macros. Anyone have any advice on how to accomplish this? Here's my current code: Sub SaveName() If Not Len(Dir("g:\users\one\" & Range("h6&...

Adding and subtracting hours in Excel 2000 worksheet
I'm having some success with the addition part but not with the subtracting part! I have an Excel spreadsheet that is used to keep track of time worked by staff on a weekly basis and I need to keep track of the overtime worked. For example, one staff member has worked 40 hours, 54 minutes and 25 seconds in a week (displayed in a cell as 40:54:25 with the cell formatted as Category: --> Time and Type:--> 37:30:55). I then went to subtract 40 hours from the total hours worked to get the overtime displayed and thats where I'm having the problem. I tried the format of "=F9 -...

Microsoft Excel #2
Why can't I close my Excel application by clicking the x button in the upper right hand corner of the application. This is Excel 2002. Is there a way to do this? --- Message posted from http://www.ExcelForum.com/ Seum Is the "x" button grayed out and unavailable? Are using using Groupwise? If that's the problem. See this MSKB article http://support.microsoft.com/default.aspx?scid=kb;en-us;260199 Article is specific for 2000 but I think it is valid for other versions. It gives a workaround (use the file menu) and instructions as to how to remove the Groupwise add-i...

table of contents in excel
Does anybody know how to create a dynamic table of contents in Excel (similar to Words TOC). Or alternatively how to insert the current page number into a cell so that it can be cross-referenced. Thanks Nick Hi Nick, See David McRitchies TOC page: http://www.mvps.org/dmcritchie/excel/buildtoc.htm --- Regards, Norman "nickH" <nickH@discussions.microsoft.com> wrote in message news:28FEEDC1-6091-4FC4-BD5C-5CE2A6116A5E@microsoft.com... > Does anybody know how to create a dynamic table of contents in Excel > (similar > to Words TOC). Or alternatively ho...

excell formula needed
Hi, In Excell, I'm trying to match a cell (eg H21,with a num value (eg 2530) to a column of figures (M34:M99) that has one match in it and produce a grading A, B, C, D from a column (N34:N99). Anyone have any ideas? Rob. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ =Vlookup(H21,$M$34:$N$99,2,false) Bernard "terrificRob" <terrificRob.uixfy@excelforum-nospam.com> wrote in message news:terrificRob.uixfy@excelforum-nospam.com... > Hi, > In Ex...

opening excel document with shell as read only
Hello, I am presently using access to open an excel file by using the shell command. Is it possible by some command e.g. \readonly to open the excel file as read only? Cheers If you're automating excel from another program, you may be able to just create an instance of excel and open your workbook readonly: This worked for me from MSWord: Option Explicit Sub testme() Dim myXL As Object On Error Resume Next Set myXL = GetObject(, "Excel.Application") If Err.Number = 429 Then Set myXL = CreateObject("Excel.Application") End If On Error GoTo 0 myXL.Visible =...

Scroll Bars Do Not Appear in Excel XP
One of our Office XP for Small Business users (running Win XP Pro SP 2) is no longer able to see the horizontal scroll bar in any of his Excel files -- files that used to display correctly a few days ago. He also can no longer see the three colored buttons in the top right of the screen (for Minimize, Zoom, Close). The scroll bars are set to display in the Prefs and we haven't changed any settings on our monitor -- any idea what we should do? Are the windows just not maximized? Try double clicking on the title bar at the top of the file and see if it snaps back into place. Lee &g...

Label a Percentage Bar with Value
This has to be simple... With Excel 2003, I have a cluster column chart with bars comparing the percentage of total projects assigned (red bar) with the percentage of projects completed (green bar) for various employees. So the bars represent percentages, but I'd like to label the "Total" bar with the actual number of projects. I'm sure this can be done; I remember doing it myself long ago. Many thanks If I understand correctly: right click a Total bar on the chart on the shortcut menu use Format Data Series open the Data Label tab and check the Value box best wishes...

Macro Not Loading XLSART excel
I have created a macro and placed into the xlstart folder and the macro will load at first. At some point, the macro stops loading, yet the macro is still in the xlstart folder. Any thoughts? S Macros don't load from the XLSTART folder. Workbooks load from the XLSTART folder. The macro must be in workbook. Is your macro stored in a file named Personal.xls or in another workbook? Is the macro in a general module or Thisworkbook module or Worksheet module. Show us your macro code. More information please. Gord Dibben Excel MVP On Mon, 10 May 2004 15:05:49 -0700, "S Crowel...

Getting data from Access to Excel?
How would you go about pulling data from Access database into Excel? (Like weekly reports) Troy, Data|Get External Data|New Database Query Select MS Access Database The Wizard should take you through the remaining steps of selecting the data you want to import and the criteria (if any) that you wish to use. PC "Troy" <corptkm@yahoo.com> wrote in message news:nLDXa.3726$Jk5.3138269@feed2.centurytel.net... > How would you go about pulling data from Access database into Excel? (Like > weekly reports) > > You can also push-it-out of Access to Excel by (While...

still can't use Entourage, PowerPoint, Excel
I posted here several weeks ago and this problem couldn't be resolved then -- not entirely, anyway. I'm trying again because I haven't been able to find anything more anywhere else, and there WAS one thing I did right that I would like to duplicate -- plus, I'm pretty sure I can pinpoint when this problem started. When it started, I'm fairly certain, was when I downloaded the last Software Update of Office for Mac. This had to have been right around April 18, because I remember having downloaded the update late at night while I was working on something else, then rather th...

format numbers #2
I have a list of audio books in an excel 2003 spreadsheet. One column should show the duration of the book in this format: 8 hrs 23 mins. But what shows in the column is a 5 digit number, such as 54463 as an example. I cannot figure out how to format this column to translate this number into the hrs and mins of the actual book. Could someone please tell me how to do this? Thanks for your help Joanne Joanne Where does this number come from? It does not seem to relate to 8 hours 23 minutes at all (eg it isn't the total minutes for example or seconds). What is the source of this informa...

replacement for favorites command in Excel 2007
In excel 2003 there was available web toolbar with favorites command. That allowed quickly to find files. For some reason web toolbar is not available in excel 2007. Is there any alternative for the favorites command or is this possibility of saving files locations just disappeared? Hi Kv, > In excel 2003 there was available web toolbar with favorites command. That > allowed quickly to find files. For some reason web toolbar is not available > in excel 2007. Is there any alternative for the favorites command or is this > possibility of saving files locations just d...

Arabic/Farsi in Office 2004 (Word and Excel)
How do you setup Word 2004 and Excel 2004 to type right to left in arabic or farsi. I have enabled Farsi and arabic in the international pane of the System Preferences and I am able to type perfectly ok in TextEdit. However when I type Arabic or Farsi in Word 2004 the characters appear but they do not connect to one another as they do in TextEdit. Any ideas on how to fix this?? You can not type right to left in Office:Mac yet. This is certainly something to expect from the next version of Office:Mac... With Office:2004 dating from mid 2004, I can expect that the next Office might come out ...

How to open Excel docs default with Excel 2007?
I had Office 2003 installed, then installed Office 2007 as well. The system was automatically configured then to open all Excel files (2003 or 2007) in Excel 2007. However, here at work they just did some Office 2003 update, and now XLS files are opening with Office 2003. If I go into Windows explorer and to file types and find XLS, when I browse to the office 12 directory and select EXCEL.EXE as the file used to open, it simply selects the "Microsoft Excel" that's already in the "Recommended Programs" section (at the top) of the "Which program to open the file wi...

Outlook 2007 Edit Business Card Format
I've just started using Outlook 2007 with the Business Contact Manager. I'd like to edit the format of the Business Card in both Outlook and Business Contact Manager. I have discovered how to edit an individual card but not how to edit all the existing cards and future cards. Can this be done and if so how? Thanks, Bob It can't be done; that functionality just isn't built into Outlook. I = expect, though, that some enterprising programmer will produce an = inexpensive tool or sample application to apply a particular format to = all EBCs.=20 --=20 Sue Mosher, Outlook ...

Custom Format text XX:XXXX:XX
I would like to have text custom formatted so that if a 8 character words is typed, it is automatically broken up like this XX:XXXX:XX. This works just fine with numbers with a ##\:####\:## custom format, but it will not work for text. Any suggestions? -- mustard ------------------------------------------------------------------------ mustard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20017 View this thread: http://www.excelforum.com/showthread.php?threadid=346023 Number formatting only works on numbers. But you could use a little macro waiting for y...

Excel VBA Q
I have 2 columns in a file - 1. Project numbers 2. Plan id If there is no project no. then I want to take the plan id and assign it to the project no. I have done this with formula: IF(C12="",A12&1) - The last number "1" - I want to increment it for all the lines for a particular group that do not have project nos. So for Group 1 that has 10 items in Plan and of them 6 have Project ids - I would like to have the Planids but with 1 at the end and then 2 at the end and so forth till 4 at end. SBUBBD1, SBUBBD2, SBUBBD3, SBUBBD4 I would like to do it through ...

How does Excel 2003 calculate trendlines with respect to axes?
I'm using Excel 2003. Excel is giving me the wrong equation for the trendline it produced. The curve resembles a parabola pointed downward with a positive offset on the y-axis. The x-axis has points -80, -70, -60, ..., 60, 70, 80. I changed the y-axis so that it crosses the x-axis at point zero. I do not believe that zero on my x-axis is zero according to the way it comes up with the trendline equation. I wish to figure out how to set up my x-axis with positive and negative numbers and still have the equation for the trendline be accurate. Steve - Make sure you're using ...