Aligning percentages with currency

[HE]

My boss would like me to create a template in Powerpoint. She wants me
to create blank columns that she can enter text on the left and
currency (positive & negative) and percentages (positive & negative) on
the right. I am using excel to set up the columns and will paste into
Powerpoint. The problem I am having is getting the percentages to line
up on the right side with the currency. I have tried to use a custom
format but have been unsuccessful. Here is an example of the alignment
I want. Please advise as to how to make this happen.


Example $10.00
Example ($10.00)
Example 10%

(the 0 in 10% should be aligned under the last zero of the one item
above it)

Thank you,
HE

 

[Pete_UK]

You can achieve this using a custom format on the currency cells -
highlight the cells and click Format | Cells | Number (tab) then Custom
format, and enter:

$#,##0.00<sp><sp><sp>;($#,##0.00)<sp><sp>

where <sp> is a single space. Using Arial 10pt, I found that you needed
3 spaces on the positive currency to line it up exactly, but only 2
spaces (plus the close-bracket) for negative values.

Hope this helps.

Pete

 

[HE]

Pete,

I did exactly what you said and I am getting a message that says
"Microsoft cannot use the number format you typed." I tried replacing
the "<sp> with "_" and that didn't work either. Also, don't I need a
semicolon to close the format? Some other things I need in the format
are making the negative a red and showing percentages as "%" instead of
converting to a decimal. PLEASE HELP!

HE

 

[Pete_UK]

The characters <sp> in the above represent a single space (i.e. press
the space bar once). If I showed exactly what you need to type, i.e.

$#,##0.00 ;($#,##0.00)

how could you tell that there are 3 spaces in the middle and two at the
end? You don't need a second semicolon here. Where you have
percentages, you just use the normal Format | cells | Number (tab) then
Percent with 0 decimal places - or select this from the "%" icon on the
toolbar if you have this visible.

Hope this helps.

Pete

 

[HE]

THANK YOU!!!!!! IT WORKED!!!! I have two more questions:

#1 How can I retain that alignment after I paste the excel sheet into
powerpoint. When I did it the percentages were misaligned in
Powerpoint.

#2 Would it be easier to just do the table in powerpoint? If so, can
you tell me how to align percentages & currency in a powerpoint table.

Thanks a million,

HE

 

[HE]

Ok, just talked to my boss. Apparently I cannot even use a pasted
excel sheet in powerpoint. It must be only powerpoint! So I guess
that only leaves question #2 to be answered.

 

[Pete_UK]

Well, I very rarely use Powerpoint, so I can't help you there - I
suppose you could manually type in the spaces to adjust the alignment
how you want it.

Anyway, I'm glad you got the Excel bit working.

Pete

 

[Harlan Grove]

You can achieve this using a custom format on the currency cells -
highlight the cells and click Format | Cells | Number (tab) then Custom
format, and enter:

$#,##0.00<sp><sp><sp>;($#,##0.00)<sp><sp>

where <sp> is a single space. Using Arial 10pt, I found that you needed

....

This isn't a general solution. It may work for some typefaces, but
there's an alternative that works for *ALL* typefaces. Format the
dollar cells as

#,##0.00_)_%;(#,##0.00)_%

and format the percentage cells as

0.00%_)

 

[DCSwearingen]

Have you looked into custom number formats?

A good primer for beginners is at Jon Peltier's

http://peltiertech.com/Excel/NumberFormats.html