Formatting with decimal alignment

JChrisHardy

New Member
Joined
May 8, 2002
Messages
7
I have a column of numbers displayed to, say, 2 decimal places. I would like to "centre" the numbers in the column but at the same time maintain the alignment of the decimal points. This is useful if the column heading is significantly wider than the largest number displayed. One way might be to add a column to the right of the target column, centre the heading across the two columns and then adjust the two column widths to taste. Would it be possible to set up a macro to adjust the format. I'd be interested in your comments.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Why not just reduce the width of your heading so that it is split over more than one line.

Pressing Alt+Enter creates a new line.
 
Upvote 0
Thanks for your reply.

Following your solution I would have to split some of the longer words in the heading which I don't want to do.

To me there are three obvious solutions. One is to do as I suggested in my original posting and another is to create a custom format for the column which contains enough spacing characters to "centre" all the numbers in the column or do as you suggest.

I suppose what I am asking is, is there some facility in Excel that I'm not aware of that allows one to specify where in a column the decimal point should be placed (ie similar to the decimal tab in Word)or do I have to write a macro to do it?
 
Upvote 0
Another option would be to change the alignment format to "wrap text" for the cell with the column heading to allow and then adjust the height of that row. You may also want to change the vertical alignment to "bottom".
 
Upvote 0
On 2002-05-09 03:10, JChrisHardy wrote:
I have a column of numbers displayed to, say, 2 decimal places. I would like to "centre" the numbers in the column but at the same time maintain the alignment of the decimal points. This is useful if the column heading is significantly wider than the largest number displayed. One way might be to add a column to the right of the target column, centre the heading across the two columns and then adjust the two column widths to taste. Would it be possible to set up a macro to adjust the format. I'd be interested in your comments.

Hi JChrisHardy:
If I understand you correctly, what you are really looking for is empty spaces padded to the right of the numbers. So this is what you might want to try

Custom format the numbers as 0.00~~~
where ~ represents the space character (use the space bar to get the space character -- don't put the ~ there). If this works for you adjust the number of space characters you want to the right to your satisfaction.

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top