Is there a way to only format numbers that have digitsafter decima

S

Sam

Hello,

I have a table where each column heading is a year. The numbers in the
table display 5 places after the decimal point. When I select all and format
the cells as a Number with only 3 decimal places, the years in the column
headings are also displayed with the three decimal places, like this:

1990.000 1991.000
13.478 13.902

How can I only format the numbers that already have places after the decimal
point - i.e.
Before
1990 <- doesn't have decimal places
13.47895 <- has places after the decimal point

Thanks you!
 
M

Max

Either don't select the col headers in the first instance, or, if entire
sheet already formatted to 3 dp, just re-select the col headers row and
re-format to zero dp.
 
D

Dave Peterson

If you can live with "1990.", you could use a number format of:

0000.###

It'll show:

1990.
1900.1
1900.23
1900.345

depending on the number of decimal places used.

If you absolutely can't have a decimal point for the whole number, you can do it
manually or run a macro:

If those values don't change (since they're headers), I gotta believe selecting
all those cells and changing them once would be the easiest way.
 
S

Sam

Max said:
Either don't select the col headers in the first instance, or, if entire
sheet already formatted to 3 dp, just re-select the col headers row and
re-format to zero dp.
Click to expand...

I have 72 of these tables on 36 pages...
Is there any other way to do this? Thanks!
 
M

Max

Sam said:
I have 72 of these tables on 36 pages...
Is there any other way to do this? Thanks!
Click to expand...

Think it's a one-off, but manual task. But if the tables are all identical
in structure with col headers in say, row1 with another in say, row50 in
each sheet, we could try grouping the sheets and do it all at one go. Try
something along these lines on a spare copy of your file

Select the leftmost sheet, say Sheet1

While holding down SHIFT, scroll to / select the rightmost sheet, say
Sheet36. This will group all the 36 sheets (all sheet tabs will appear
highlighted)

Select row1 (click on the row header)
Hold down CTRL and select row50
(this will select both rows 1 and 50)
Click to format to zero dp as per normal

Finally, do a right-click on any sheet tab > choose "Ungroup Sheets"
 
R

Robert_Steel

the cells as a Number with only 3 decimal places, the years in the
column
headings are also displayed with the three decimal places, like this:

1990.000 1991.000
13.478 13.902

How can I only format the numbers that already have places after the
Click to expand...
decimal

Do the numbers have a maximum lower than the years?
eg years start at 1990 and largest number 1989.999

if so try this custom number format
[<1990]0.000;General

I hope this is the case otherwise it's a lot of work.

hth RES
 
Top